Reading CSV Values

[robertdavies]

Hi all ive been struggling with this problem for hours now.

What I am doing is querying a CSV file, to return a recordset. it works ok for the most part. The thing is the first column contains numbers such as "78789" and some contain letters and the value may be "HP5454". The code is assuming that everything is a numeric value. All alphanumeric values are returned as null.

The CSV looks like this

Unique #, Another
89890, kjadskfjksdf
98909, adsfddddd
HP80, 00000000

The problem is im looking to get the csv from a dump from a application. So cant really afford to mess around with that output I would like to leave it as it is.

Is there anything I can do in sql to fix this. As that seems to be the root of it. The actual sql results are null. Dont think I can fix that in asp.

Hope that makes scence

Thanks

[Bullschmidt]

Well since no one else has answered yet I'll post something even though it may not be much and might not be what you're looking for.

Nevertheless it COULD BE a POSSIBLE solution.

And that is if all else false create your own new CSV file based on going through the old one line by line perhaps with FSO.

And make it so that each text field is surrounded by double quotes so that this:

Unique #, Another
89890, kjadskfjksdf
98909, adsfddddd
HP80, 00000000

Now looks like this:

"Unique #", "Another"
"89890", "kjadskfjksdf"
"98909", "adsfddddd"
"HP80", "00000000"

And maybe you already know this but for help connecting to a text file using the Jet OLE DB provider:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJetText

And based on the above link realize that the actual filename does NOT go in the connection string - rather it goes in the SQL statement (definitely a little tricky).

[robertdavies]

Hi Thanks Bullschmidt :-)

Yeah I did work out that double quotes around values work. However the problem is users will be using a software package to export the csv, and this is how the data is exported. So minimum fiddling by users would be great to get it working. The users are not very techincal so as easy as possible would be great.

Ive tried going in excel and saving it as different formats and the same thing happens over and over again.

Perhaps I could manually go in modifying the text file via code to insert the quotes?

Rob

[Bullschmidt]

The only other thing I can think of to try is to use a union SQL statement with hardcoded numbers as the first part of it (and then not use the first record of the resulting recordset - i.e. the hardcoded numbers).