x

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.

more ▼

asked May 24 '10 at 10:27 AM in Default

justice gravatar image

justice
35 2 2 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

more ▼

answered May 24 '10 at 10:48 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.6k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered May 24 '10 at 10:31 AM

Leo gravatar image

Leo
1.6k 51 56 58

(comments are locked)
10|1200 characters needed characters left

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/

more ▼

answered May 24 '10 at 01:33 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left

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 :)

more ▼

answered May 25 '10 at 05:25 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x62
x38

asked: May 24 '10 at 10:27 AM

Seen: 1988 times

Last Updated: May 24 '10 at 11:16 AM