August 25, 2008 at 12:33 am
I am using BCP to load a csv file into a table. All is working fine when the data in the file is good.
If the file has fields with bad data types (eg. value of field in the csv file is a set of characters but the column is an int data type) BCP spits out the error to the error file correctly.
But if the csv file has a line with 1 less column than is expected (ie. some lines have 7 columns of data separated by commas, but some have only 6) then BCP simply ignores these lines. They obviously do not end up in the table, but they also are not recorded in the error file. Why aren't they being logged to the error file?
Cheers
December 7, 2009 at 1:22 pm
Paul,
The reason that BCP does not log these rows into the error file is because they are not considered errors. BCP will do one of 2 things depending on the file you are importing:
1)If your file still contains the final tab delimiter, any missing columns will be imported as NULLS.
Ex: Column 1 Column2 Column3
1 Value 1.1, Value1.2, Value 1.3
2 Value 2.1, Value 2.2,
If you were to import the data above into a SQL table, Row 2 Column 3 will contain NULL
2)If your file does not contain the tab delimiter, BCP will keep searching until it finds another tab delimiter, even if this means that it skips the row terminator in the process.
Ex: Column1 Column2 Column3
1 Value 1.1, Value 1.2, Value 1.3
2 Value 2.1, Value 2.2, Value 2.3
3 Value 3.1, Value 3.2
4 Value 4.1, Value 4.2, Value 4.3
If you import the data above into SQL, your data will look as follows:
field1field2 field3
Data11Data12 Data13
Data21Data22 Data23
Data31Data32 Data41 Data42,Data43
As you can see from this example, Data32 and Data41 are concatenated because there was no comma after Data32. Since BCP did not find
the tab delimiter after Data32(the comma), it skipped over the row terminator(\ n which is the default) and went to the next line. On this line, it found a comma after Data41 so it imported "Data32 Data41". Since there is no row terminator after this value, BCP grabs the next value Data42 and contatenates this with Data43, in spite of the fact there is a comma. Since there is only 3 fields in the DB table, BCP will grab all data after "Data41" until it encounters the row terminator character.
The easiest way to resolve this issue is to ensure that the tab delimiters and row delimiters appear on each line regardless of whether the file contains any values. If this is not feasible, you may need to look into an SSIS solution.
Bob Pinella
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply