9

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?

AnonJr
  • 2,759
  • 1
  • 26
  • 39

2 Answers2

15

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

joshmax
  • 378
  • 5
  • 20
jeff.willis
  • 346
  • 1
  • 5
  • Beware: the field count is based on the first line. So if the number of fields increases at any point in the file, you can't capture the additional fields. This mattered in my case, so I rolled my own (though not in VBScript). – harpo Mar 26 '09 at 13:59
  • This is true. You can avoid this limitation by defining a schema.ini file (http://msdn.microsoft.com/en-us/library/ms709353.aspx). This also allows you to qualify the data types of the fields (by default, '0001' will be read as '1') – jeff.willis Mar 28 '09 at 00:12
  • You need a semi-colon after the path to folder - Server.MapPath("path to folder";) - but this has just saved me hours of trying to parse my CSV file - thanks! – whostolemyhat Jan 24 '11 at 16:11
  • Beware that the JET CSV reader uses the default locale of the machine for parsing, even if your ASP application has set a different locale e.g. for date formats. So when parsing dates with this method, you may get 1-Dec-2014 becoming 12-Jan-2014! – James McCormack Dec 01 '14 at 13:08
  • Awesome solution, but i had to apply another format on `Extended Properties` otherwise it throws an `Format of the initialization string does not conform to the OLE DB specification.` exception. Use `Extended Properties=""text;HDR=Yes;FMT=Delimited""` instead to avoid this exception. – ViRuSTriNiTy Sep 14 '16 at 13:49
6

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

Fionnuala
  • 90,370
  • 7
  • 114
  • 152