problem importing CSV file to SQL 2k

[kleinma]

I was given a CSV file that I need to import into a SQL2K DB.

The table is already created in the DB with the correct structure.

The problem is the text fields in the CSV aren't closed in quotes or anything, so when I import the data, any textfield that has a cr/lf in it, screws up the import, because it thinks its the next record.

Anyone have any ideas how I might be able to get this imported without a bunch of manual legwork?

[RobDog888]

Are you using DTS to import it? I dont have access to SQL anymore but there was something about text qualifiers or field transformations.

[kleinma]

yeah I am, but the problem is that I guess when it hits a CR/LF, it doesn't know if its the next line, or a textfield that contains a CR/LF.

The problem wouldn't exist if the text fields were enclosed in single our double ticks, however I was just given this CSV file, I had no control over its creation.

[RobDog888]

How about trying bcp? Maybe it has a different option of will perform better.

[RobDog888]

Ah, just thought about this. What if you read the textfile into an array or such using VB and then write out to a corrected file or import into SQL directly using ADO.NET? Basically preping the file or just directly import it.

[kleinma]

I thought about that actually, but what logic can I employ myself to determine if the CRLF is the end of the record line, or just part of one of the textfields?

This can be done by manually going through each record, because its human readable to figure it out, however what do you think I should do in code to determine this?

[RobDog888]

Read it line by line and if there is less then the expected number of commas or fields then read the next line as part of the same line. Its just a matter of translating the cognitive task of when you visually determine if the rows are continuing on or if they are the next record, into code.

[si_the_geek]

I would personally load the file in Excel and write a quick macro to add quotes to all cells, before re-saving as CSV and doing the import with delimiters... untested macro example:

Dim CurrCell as Range
  For Each CurrCell In ThisWorkBook.WorkSheets("[u]Sheet1[/u]").UsedRange
    CurrCell.Text = """" & CurrCell.Text & """"
  Next CurrCell

[RobDog888]

Being a csv file doesnt mean it has to use Excel especially if its already part of an app. If its part of an app then its easier to just add some code to add quotes or some other workaround.

[techgnome]

I would personally load the file in Excel and write a quick macro to add quotes to all cells, before re-saving as CSV and doing the import with delimiters... untested macro example:

Dim CurrCell as Range
  For Each CurrCell In ThisWorkBook.WorkSheets("[u]Sheet1[/u]").UsedRange
    CurrCell.Text = """" & CurrCell.Text & """"
  Next CurrCell

Is that necessary? I mean, isn't there an option when saving as CSV to add text quote identifiers? It's been a while since I've had to make a csv through Excel, but I thought I remembered something to that effect in there.

-tg

[kleinma]

well again I didn't make the CSV.

I think I can likely get it working using one of the mentioned methods above, but about 3 hours ago I got yanked to work on another project, so I will have to put this on hold for a few.

[si_the_geek]

Is that necessary? I mean, isn't there an option when saving as CSV to add text quote identifiers? It's been a while since I've had to make a csv through Excel, but I thought I remembered something to that effect in there.

I did a quick test and didn't get any options (even in the Save As dialog), but it probably is an option somewhere!

[RobDog888]

Yes,it is in there

You can use the Workbooks.OpenText method to open a delimited file and specify the textqualifier, format for a particular column or special delimiter etc.

[szlamany]

If the data elements in the CSV have CR/LF's embedded in them and they are not quoted then it's a poor CSV file. EXCEL isn't going to like it any better.

You are going to have to code a silly little app to open the file and read data in line by line. You will know that you haven't processes x-amount of columns yet - so when the CR/LF appears you know that the next line read from the file is simply a continuation of the prior line.

I hate poorly produced import files - I get them all the time.

[kleinma]

it was a CSV created automatically from a SQL Server DB that is hosted by a go daddy website account.

They don't allow remote connections to the DB, they only had a feature to "export to CSV". Its not my site, and I didn't have control to write my own ASP or ASP.NET page to create a file how I wanted it. I just got the CSV and was informed to get it into a DB.

[RobDog888]

Easiest way is just to write a procedure like I suggested earlier to prep the file for DTS importing. By the time all this is hashed out to use some other method or program, you could have been done.


Ps, godaddy.com lol

[kleinma]

yeah I know godaddy.com is budget... but they needed a *cheap* host.

I personally use discountasp for my hosting. They are a great host, even if they do cost a little more...

short of registering activeX controls on their servers, there is little you can't do with their hosting package.

anyway I think I am going to use that method Rob and szlamany mentioned which is to just write a dinky app to make it a better importable file.