July 26, 2011 at 5:14 am
Hello all
I've been asked to set up a new loader for our system which already has around 20 other different loaders for different types of files. These all use a bulk insert task in DTS to load from a flat file to a SQL Server 2000 table and I'm wanting to set up the new loader so it works the same way as the others.
I've been sent a file to load which is comma separated and most columns, but not all, have a double-quote text qualifier. However, on some of the columns with the double-quote text qualifier if the text is blank there are no double quotes, e.g. the file looks similar to this...
[font="Courier New"]"Joe","Colin","Bloggs","Male","Data1","Data2"
"Joan",,"Bloggs","Female",,"Data3"[/font]
Note that the 2nd and 5th columns of row 2 are empty and have no double-quotes.
The format file I set up looked like this...
[font="Courier New"]7.0
6
1 SQLCHAR 0 50 "\",\"" 1 FirstName
2 SQLCHAR 0 50 "\",\"" 2 MiddleName
3 SQLCHAR 0 50 "\",\"" 3 LastName
4 SQLCHAR 0 10 "\",\"" 4 Gender
5 SQLCHAR 0 20 "\",\"" 5 ExtraColumn1
6 SQLCHAR 0 20 "\"\r" 6 ExtraColumn2[/font]
However, this fails to load due to the second row not containing the double-quote text qualifiers on the blank columns (I'm pretty sure this is the reason as loading the first row on it's own is fine). I would expect the following to load successfully...
[font="Courier New"]"Joe","Colin","Bloggs","Male","Data1","Data2"
"Joan","","Bloggs","Female","","Data3"[/font]
I have initially gone back to the business and told them the file is invalid and that if a column has a text qualifier then it needs to appear on all data within that column. Before they go back to the suppliers of the file I wanted to double check that there is no change I can make to the format file to handle the existing file. I don't want to load the double-quotes into the table and then have to remove them afterwards.
Any help would be much appreciated.
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply