Classic ASP - How to preserve commas while parsing

Question

The Scenario

4 columns, ID, ParentID, Category, OrderNo and Category can have commas in it e.g "Sales, Manager" or "HR, Recruitment" so I would have to handle that without being able to know that the words have quotes around them as the files they use don't so I would need to handle rows with odd number of commas and then treat those rows as ones with categories with commas inside so it's a little more complicated.

Answer 1

I'd personally use the Microsoft Text Driver to parse CSV files, makes dealing with the data alot easier.

First create a text.dsn file and save it somewhere in your web app (in the example i'll assume its where the CSV file is located)

[ODBC]
DRIVER=Microsoft Text Driver (*.txt; *.csv)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=25
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=text
Extensions=txt,csv,tab,asc
DriverId=27

Then treat it as a normal db connection eg:

strPath = server.mappath("/csv/")
sDSNFile = "text.dsn"
strCSVFile = "test.csv"

sDSN = "FileDSN=" & strPath & sDSNFile & ";DefaultDir=" & strPath & ";DBQ=" & strPath & ";"
Set Conn = CreateObject("ADODB.Connection")
Conn.Open sDSN
sql = "SELECT * FROM [" & strCSVFile & "]"

set rs = conn.execute(sql)

do until rs.eof
    id = rs("ID")
    ParentID = rs("ParentID")
    Category = rs("Category")
    OrderNo = rs("orderno")
    ' do something cool here
loop

This way you could pull say all developers out using standard sql

sql = "SELECT * FROM [" & strCSVFile & "] where Category='Developer'"

Hope this helps.

ps. If you don't have it installed, I think the text driver is included as part of Microsoft Access Database Engine redistributable, but it has been a while so may be wrong :)