bulk insert rowterminator issue

[devquest]

I'm running a bulk insert like this:
Code:

BULK INSERT Descriptions
FROM '......\Descriptions.txt' 
WITH (FIELDTERMINATOR = '\t',
      ROWTERMINATOR ='\n')

The file is a tab delimited file with two columns, item name and description

One of the descriptions in the file goes over 2 lines (enclosed in quotes) and it's messing things up.How can I work with this?
ex:
item1 description1
item2 "description2.. .............
...continued description2... ..."
item3 description3... ....

Thanks.

[Rabbit]

You can't. The file is out of format. Bulk insert does not have an option to ignore everything within string delimiters. The file will have to be fixed by getting rid of that line break.

[devquest]

Thank you. Do you know how I can do that or how I can just disregard that line?

[Rabbit]

Open the file in notepad and then delete the line break.

[devquest]

Any workaround programmaticall y?
Thank you.

[Rabbit]

You could create a script that will delete any new line characters between sets of double quotes. You could use whatever scripting language is handy to you: javascript, vb script, perl, etc. Whatever you're comfortable with. Then you would just have to run the file through the script before running the bulk insert.

And if you wanted to, you could probably do it through an SSIS package, but I believe that limits to you VB.net for coding purposes.

[devquest]

Thank you for your help. I feel comfortable using ssis packages and will look into it.