How to read CSV files line by line in VBScript

问题:

I am using an ASP page where I have to read a CSV file and insert it into DB table "Employee". I am creating an object of TestReader. How can I write a loop to execute up to the number of rows/records of the CSV file which is being read?

回答1:

Do not try to parse the file yourself, you'll just give yourself a headache. There's quite a bit more to it than splitting on newline and commas.

You can use OLEDB to open up the file in a recordset and read it just as you would a db table. Something like this:

Dim strConn, conn, rs

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("path to folder") & ";Extended Properties='text;HDR=Yes;FMT-Delimited';"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn

Set rs = Server.CreateObject("ADODB.recordset")
rs.open "SELECT * FROM myfile.csv", conn

while not rs.eof
    ...
    rs.movenext
wend

My vbscript is rusty, so verify the syntax.

edit: harpo's comment brings up a good point about field definitions. Defining a schema.ini file allows you to define the number and datatypes of the expected fields. See: You can handle this by defining a schema.ini file. see: http://msdn.microsoft.com/en-us/library/ms709353.aspx

回答2:

Why not just insert the CSV? For example:

SELECT * INTO MyTable FROM OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 
'Data Source=F:\MyDirectory;Extended Properties="text;HDR=No"')...
[MyCsvFile#csv]

From: http://coding.derkeiler.com/Archive/Delphi/borland.public.delphi.database.ado/2007-05/msg00057.html