1

I need to parse a flat file which is containing backspace (^H) character delimiter between fields. I need to parse this file and insert into sql server 2005 tables.I tried to use bcp utility along with the format file but I wasn't able to specify the delimiter as backspace. The default one is tab (\t). There are several other delimiters as well but none to specify backspace. Anyone has any ideas, please do help me.

Also I need to export data from sql server table to fixed length flat file.I tried to use non-xml format file, but always it asks for a delimiter.How can I create a flat file using bcp without any delimiter between the fields? All above are character files.

Thunderhashy
  • 5,291
  • 13
  • 43
  • 47
  • Why is it using backspaces as field delimiters? – Coding With Style Dec 28 '09 at 20:31
  • The point is that the file being fed to our system is actually originating from a legacy application which sends flat files having backspace as field delimiters.We need to parse these files and store the data in our sql server tables. – Thunderhashy Dec 29 '09 at 00:49
  • Ok I solved the first part about exporting a fixed length flat file using bcp export utility. But I am still waiting for ideas on how to parse backspace delimited files using bcp import facility. I can only use non-xml format files.. Any ideas are most welcome. – Thunderhashy Dec 29 '09 at 19:17

2 Answers2

0

This is an ugly workaround, but you could always find something that's not in the flat file, and replace everything in the flat file with that, then use that as the column terminator (using bcp -t that).

Kev
  • 15,899
  • 15
  • 79
  • 112
  • This requires one more preprocessing step. Anything better? – Thunderhashy Dec 29 '09 at 19:34
  • I wish. I'm waiting for an answer to a similar question that I posted that I hadn't realized might be approximately the same question as yours in essense. – Kev Dec 29 '09 at 19:56
  • Can you send me the link to that question? I was wondering if we could somehow specify ascii characters as delimiters in bcp -t option. But couldnt find any way to do that. – Thunderhashy Dec 29 '09 at 20:08
  • Sure, sorry: http://stackoverflow.com/questions/1976086/getting-bcp-exe-to-escape-terminators . Although, you should see the note under the first table at http://msdn.microsoft.com/en-us/library/aa196735%28SQL.80%29.aspx – Kev Dec 29 '09 at 20:37
  • I am still trying to parse backspace delimited files from bcp. I am looking for answers.. any ideas most welcome.. – Thunderhashy Dec 30 '09 at 22:06
0

Sorry that I'm almost 11 years late on this, hopefully you've already solved your problem but you can use the hexadecimal representation of the backspace character 0x08 to parse your input file and properly delimit your fields which are separated with a backspace character.

Logan
  • 126
  • 1
  • 6