May 6, 2021 at 8:13 am
Hi,
I have a BulkInsert setup to import files daily. It uses a format file to specift the end of row:
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR='"' (single quotes round a double)
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR='"\r\n' (Double Quote \r \n)
</RECORD>
<ROW>
<COLUMN SOURCE="2" NAME="DATA" xsi:type="SQLVARYCHAR"/>
</ROW>
Now for the most, this works totally fine..
It looks for a double quote and then a [CR][LF].
The issue i have is that there is a free hold text field, where people can enter whatever they want (yay.. comments fields...)
If they start the string with a carriage return.. it looks exactly like the end of line.. A double Quote and then a [CR][LF].
I cant get them to change their ways.. Is there any ideas of how to get round this?
Thanks
Rob
May 6, 2021 at 2:07 pm
I had something similar with a data flow task in a SSIS package importing a CSV file.
In the end I changed the delimiter from , (comma) to "," (double quote comma double quote). There was a bit a fiddling around for the first double quote (I think I created a dummy column of 1 character and then ignored the column) and for the last column (it was delimited double quote CRLF) but it over came the problem of embedded commas in the text file.
If you have a delimiter between the two columns you might be able to do something similar.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply