I have a data source file which I need to load into my SQL table using DTS package. | is used as delimited to separate each field. Recently, i discovered some of the fields came with | has part of the field content. This has caused some problem with the loading process because the field has been split into 2 because | is recognised as a new field.
What can I do now? I can't change the delimited to something else or neither can I stop my data source from using | as this is beyond my control.
You are screwed unless you can find some pattern to the usage of the pipe delimiter in your data values. If you can find a reliable pattern then you can handle them by loading each record into a staging table as a single string, and then removing or replacing the offending character prior to populating your production tables.
Can you give a few examples where a pipe delimiter exists in your data? Include both the field with the pipe character, as well as the field immediately prior to it, and the pipe delimiter between the fields as well.
If it's not practically useful, then it's practically useless.