x

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.

more ▼

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

avatar image

David 2 1
732 54 57 65

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

more ▼

answered Dec 13, 2010 at 09:14 AM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

Thanks all. Got it loading.

Dec 15, 2010 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'

(Or)

Set up as the schedule job - Microsoft DTS Link

CSV File Import

more ▼

answered Dec 13, 2010 at 02:16 PM

avatar image

Leo
1.6k 55 59 62

(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, 2010 at 08:50 AM

avatar image

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

more ▼

answered Dec 13, 2010 at 01:37 PM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

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

x1066
x501
x49
x38

asked: Dec 13, 2010 at 08:42 AM

Seen: 5542 times

Last Updated: Dec 13, 2010 at 08:42 AM

Copyright 2016 Redgate Software. Privacy Policy