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

Hi,

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.

asked Dec 13, 2010 at 08:42 AM in Default

David 2 1
992 ● 55 ● 60 ● 67

(comments are locked)

4 answers:

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`

answered Dec 13, 2010 at 09:14 AM

Kev Riley ♦♦
65.7k ● 48 ● 63 ● 81

Thanks all. Got it loading.

Dec 15, 2010 at 08:43 AM David 2 1

(comments are locked)

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'

(Or)

Set up as the schedule job - Microsoft DTS Link

CSV File Import

answered Dec 13, 2010 at 02:16 PM

Leo
1.6k ● 55 ● 59 ● 62

(comments are locked)

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

answered Dec 13, 2010 at 08:50 AM

Leo
1.6k ● 55 ● 59 ● 62

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, 2010 at 09:09 AM David 2 1

(comments are locked)

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

answered Dec 13, 2010 at 01:37 PM