CSV Import

I have scoured the web for everything related to CSV imports in ASP. I have everything working, but one issue remains.

My web application accepts a file upload of a Tab Delimited text file, parses through it to create a Comma Delimited file, creates that file on the server, and then goes to the CSV import page.

The CSV import page accesses the Comma Delimited file through ODBC text driver, and I generate SQL insert statements from each record. The problem is, one field is a "room number" field which generally contains numbers, but several entries also contain letters, an entry might be "A121" or "AUD" or "254", etc.

I'm guessing that ASP or the ODBC text driver "guesses" the datatype of the field, because whenever it hits a record with letters in the "room number" field, it uses the last numeric value for that field from any previous record. If there are multiple records with letters in this field, it will continually show the most recent numeric entry from that field, even if it was 5 or 10 records ago!

I am accessing these values using recordset("fieldname"), and all other fields display correctly, except recordset("roomnumber") when there are letters and numbers in the entry.

I've opened the CSV directly on the server and the fields are all fine, in the "room numbers" field, the entries which are all numbers are right-aligned, while entries with numbers and letters are left-aligned, but I assume that's just the way Excel displays them.

I am aware that adding quotation marks around this field would solve the problem, but there is no way I can have the data format changed. I might try to add quotations while creating the CSV file on the server, but this is such a strange problem that I'd like to figure it out anyways.