How to read CSV files line by line in VBScript

Question

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?

Answer 1

Use the reader to read the content into a string. Call the split function of the string to split the string by the new line character ('\n'). This will split each row into a separate string that you can store in a string array. Call the length method on the string array to find the number of rows.

Edit: sorry, upon reading your question again I understood that you are not using .NET. I would imagine the functionality is about the same with VBscript though.

I could give you an example in C# with .NET. The concept is the same but the syntax is completely different. C# Example:

//text reader
StreamReader reader = new StreamReader(Server.MapPath(filePath));

//read file into string
string file = reader.ReadToEnd();

//create string array and split the file string
string[] rows = file.Split('\n');

//find the count
int rowCount = ads.Length;

Answer 2

Any ASP.VBSCritps ?

Answer 3

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

Answer 4

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

Answer 5

great technique and idea. Probably much easier and faster than dealing with newlines, commas, splitting..

Answer 6

I get the Could not find installable ISAM ERROR ?