0

I am having a text file with millions of records and have to extract and update to some staging table.

Now the problem is we don't have any text qualifier to the separate columns from others. My column delimeter is "," and I am having some fields like address which may have some ",". I am using 2008 version and know that there should be 20 columns.

I have to deal with this before going for any transformation i.e. while extracting data from the text file. Please let me know some of the best way to deal with this kind of data. I am stumped here and need some help.

Thanks in advance. :)

Sarat
  • 176
  • 7
  • 2
    Perhaps you could reject any records that have more than 20 columns, and redirect them to an error file. Then you could maybe process the remaining records that are okay. – DeanOC Jun 08 '15 at 10:34
  • Thanks DeanOC, That seems a good idea to impliment. :) – Sarat Jun 09 '15 at 11:47
  • I am having something in my mind as well. Say Ican request client to put the address field at last and then for last field I will not consider any Column delimeter. Can I do that? If yes then plz suggest me how? – Sarat Jun 09 '15 at 11:53
  • That might be possible, but you won't be able to read the text file as comma-delimited. You would have to read each line entirely and then use a script component to parse the line. If the client can change the file, it would be a lot easier for you if they can change their delimiter to a pipe character (or something else that doesn't appear in a value) – DeanOC Jun 09 '15 at 21:28

0 Answers0