question

justice avatar image
justice asked

Using BCP to import Data to SQL2005

Is it possible to select certain columns from a .CSV file to SQL table using BCP? If so how? I need to import only 5 columns from a flat file that I get with more than 10 columns, how do i choose the only 5 column I need from such csv file. Please help.

sql-server-2005import-databcp
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Yes, take a look at the format file. That allows you to map the data in the file to columns in the database.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered

Why don't you use SSIS? That will be lots easier then BCP (Bulk Copy Program).

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

Grant has the right answer to the question directly asked, but I think Leo probably is suggesting the best answer (+1 to both). BCP is probably the easiest and best way to handle this task (barring some reason to prefer BCP not listed in the question.)

Just for the sake of completeness, let me point out you can do this using pure T-SQL using OpenDatasource. There is some information that might be useful in doing that at: http://www.sqlservercentral.com/articles/OpenDataSource/61552/

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

Can't let this question go by without mentioning LogParser, it would let you specify the columns that you want by name, change the data types if necessary, rename the columns in pass it in to SQL in one query. It would be my preference.

Leo, Grant and Timothy have valid, workable solution though. +1's all round :)

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.