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, 2010 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, 2010 at 10:48 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 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, 2010 at 10:31 AM

Leo gravatar image

Leo
1.6k 54 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, 2010 at 01:33 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 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, 2010 at 05:25 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(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.

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:

x1951
x67
x42

asked: May 24, 2010 at 10:27 AM

Seen: 2209 times

Last Updated: May 24, 2010 at 11:16 AM