Bulk insert problem

[hkung]

hi all,

I'm using bulk insert to insert records from a data file into a table in SQL Server. Initially, It's working fine. The field delimiter is known as ',' and the row delimiter is '/n'. However, when we tried to run the bulk insert statement,with data file consist of 1 extra column value (not defined in the table), the value is loaded together into the final column. To clearly illustrate it, i have below :

table1 :
col1 varchar(30)
col2 varchar(30)
col3 varchar(30)

Sample Data file :
val11,val12,val13
val21,val22,val23,val24
val31,val32,val33


when i select * from table1, the results shown as :
col1 col2 col3
-----------------------------------
val11 val12 val13 (fine)
val21 val22 val23,val24 (problem!)
val31 val32 val33 (fine)

the col3's value for row2 is wrong. Does anyone know how should i overcome this? Is there any thing that i've missed out besides specifying fielddelimiter and rowdelimeter in BulkInsert? Thanks in advance!

 

[pankajv]

Send the code which is used to parse the file based on row and coulmn delimeter.

 

[tlbroadbent]

pankajv,

Bulk Insert is a SQL command . The code is built into SQL Server.

hkung,

You should use a format file. Read the topic "Using Format Files," in SQL Books Online (BOL) or at the following link.

http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_9yat.asp

Syntax for using format file in the Bulk Insert statmetn can also be found in BOL or at ...

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ba-bz_4fec.asp

You may want to consider using Data Transformation Servies (DTS) to import the data.

Another option is to clean up the input file before importing. For example, you could write a program to strip the 4th column from rows where it exists. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[hkung]

Thank you for the tips. Am i right to say that Bulk Insert will only work fine by 'assuming' the data files are in the correct format? Does it mean that i'll have to create a separate program to check the data consistency before running bulk insert? If that is the case, then i guess i'll have no choice but to code another program.. Anyway, thanks a lot!