Using bcp Or Bulk Insert To Insert Data From A CSV File With 2 Identical Columns?


I'm new to SQLServer and wondering how the below may be possible:

I have a csv file that contains the following columns: CLIENT, NOTES, NOTES. As you can see there are 2 NOTES columns both of which need to be imported automatically using bcp or Bulk Insert into the table DATA_LOAD which has the following columns CLIENTNO, NOTES_FIELD1, NOTES_FIELD2.

How can I use bcp or Bulk Insert to successfully load all 3 columns into their appropriate places in the table?

Thanks in advance.
more ▼

asked Dec 13 '10 at 08:42 AM in Default

David 2 1 gravatar image

David 2 1
412 42 49 51

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

4 answers: sort voted first

There are too many options/questions to give a complete answer here, so I would suggest looking at the BOL entries for both BULK INSERT and bcp. As long as the import file data types match the table data types, and are in the same order you shouldn't have too many issues.

However some guidance:

  • Using BULK INSERT will be something along the lines of BULK INSERT DATALOAD FROM 'c:\dataloadfile.csv'
  • It sounds like you have column headers in your data - the FIRSTROW option might sound good, but as stated in BOL

The FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows.

  • using bcp will be like bcp DATALOAD in dataloadfile.csv -T -c - where -T is used to specify Windows authentication, and -c specifies character data

Another option is [OPENROWSET][3]

[3]: http://msdn.microsoft.com/en-us/library/ms175915.aspx
more ▼

answered Dec 13 '10 at 09:14 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Thanks all. Got it loading.
Dec 15 '10 at 08:43 AM David 2 1
(comments are locked)
10|1200 characters needed characters left

You can done it by using following way ......

You can remove Dynamics Task (Pass Parameter) if you are using the same file name. Otherwise use the Global Variable and pass the parameters by using Dynamics Task.

You can set the Column Name in Text File (Source) and Transfer to Database.

After you set those up, you can run the Job by calling something like 'DTSRun /S "(local)" /N "Test Package" /G "{9EA5A37A-40F6-4F35-B901-68A2E627C6B0}" /A "Global_var1":"22"="24" /A "Global_var2":"8"="TEST" /W "0" /E'


Set up as the schedule job - Microsoft DTS Link

![CSV File Import][2]

[2]: /upfiles/DTS_2000_Package_Designer.jpg
more ▼

answered Dec 13 '10 at 02:16 PM

Leo gravatar image

1.6k 51 56 58

(comments are locked)
10|1200 characters needed characters left
You can use SSIS if you are using SQL 2005 and above. Or DTS in SQL 2000. You might need to change in SSIS Columns for one of NOTES to NOTES as NOTES1 or something.....
more ▼

answered Dec 13 '10 at 08:50 AM

Leo gravatar image

1.6k 51 56 58

I am using SQLServer 2000 and am setting up a job in DTS using EXEC MASTER.DBO.xp_cmdshell 'bcp....

However I need to script the bcp or bulk insert part to accept more than 1 NOTES column (with I suppose a format file). This is what I really want to know. Thanks
Dec 13 '10 at 09:09 AM David 2 1
(comments are locked)
10|1200 characters needed characters left

You could use LogParser, it will automatically rename the second Notes column to Notes2 or you could concatenate it to the 1st one as part of the import process. Some details here [http://www.simple-talk.com/community/blogs/jonathanallen/archive/category/1043.aspx][1]

[1]: http://www.simple-talk.com/community/blogs/jonathanallen/archive/category/1043.aspx
more ▼

answered Dec 13 '10 at 01:37 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 73 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



Answers and Comments

SQL Server Central

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



asked: Dec 13 '10 at 08:42 AM

Seen: 3985 times

Last Updated: Dec 13 '10 at 08:42 AM