[ASP] Find and Parse a specific line in CSV?

[Pheddy]

Hi All!

Could anybody provide me with the fastest and best way to search for a specific value in a (VERY LARGE) CSV file and parse that exact line?

The CSV file has about 90.000 to 120.000 Lines and are build like this:

"84016","Bags","Belkin iPod Nano Remix Acrylcase black","19","Yes","0.1","F8Z381EA"

I would like to be able to finde "84016" and from there on parse the other values from that line.

Regards and thanks.
Frederik

[azrar]

use instr

Try something like this:

start_pos = Instr(1, csv_data, vbCrLf & Chr(34) & "84016")
If start_pos > 0 Then
 '-- find the end of the line
 end_pos = Instr(start_pos + 2, csv_data, vbCrLf)

 '-- extract that one line based on start and end positions
 onerow = Mid(csv_data, start_pos, end_pos - start_pos)
Else
 '-- the searched data does not exist
End IF

The trick is to include the line break in your instr search, so you know you are at the beginning of the line.

Note: for best results, make sure there is a line break at the very beginning and the very end of the csv data.

[Pheddy]

Thank you azrar!

The code works fine. But now I have one line and would like to extract elements from this, can you help me with that?

[azrar]

split()

Given the line:
"84016","Bags", "Belkin iPod Nano Remix Acrylcase black","19","Ye s","0.1","F8Z38 1EA"

You can make array out of the data like this:

myArray = Split(csv_data,Chr(34) & "," & Chr(34))

The first and last element will have an extra quote in it. You can avoid that by tweaking the code I gave you earlier (add 1 to the start position, subtract 1 from the end position).

[jhardman]

I disagree, I would use a db engine and query the csv

"select * from file where field1 = 84016"

The microsoft JET ODBC driver has that capability.

On the other hand, with 120 thousand rows, why haven't you converted to a db yet?

Jared

[Pheddy]

Thank you very much for that reply, I am trying to parse this file down to put the fields into a database. So i am looking for the exact thing you asked of..

I would be very thankfull if you could guide me further.

Regards
Frederik

[jhardman]

http://www.w3schools.com/asp/asp_ado.asp