Importing a CSV file to a multidimensional Array

Hi,

 I am trying to import a csv file into an array - ultimately I will be inserting it into a database but I first want to process it. The csv file is an EDI message broken up into sections. 1 header line, x amount of detail lines and 1 footer line all this is delimited by a $. So:

H$Company Name$5010251000009$$$$$$$$
X$ADDITIONAL INFORMATION:$*** PLEASE NOTE DELIVERY ADDRESS ***$IN CASE OF ORDER QUERY$PLEASE TELEPHONE 0845 6115999$$$$$$
X$IN CASE OF DELIVERY QUERY$PLEASE TELEPHONE 01924 999999$ATTN: ORDER DEPARTMENT. -- DELIVERY CONF$IRMED FOR 11.30 ON  7/02/08 --$$$$$$
X$PLEASE NOTE THAT THIS BOOKING IN TIME MU$ST BE STRICTLY ADHERED TO.$VEHICLES ARRIVING EITHER EARLY OR LATE W$ILL BE ASKED TO WAIT FOR THE$$$$$$
X$NEXT AVAILABLE SLOT.$SUBJECT TO Company Name TERMS AND CONDITION$$$$$$$$
D$9744005$080204$080207$5010251999744$1$5010251015574$0000000012$0000001260$$
T$0001$5010251000006$$$$$$$$
H$Company Name$5010251000009$$$$$$$$
X$ADDITIONAL INFORMATION:$*** PLEASE NOTE DELIVERY ADDRESS ***$IN CASE OF ORDER QUERY$PLEASE TELEPHONE 0845 61159999$$$$$$
X$IN CASE OF DELIVERY QUERY$PLEASE TELEPHONE 01924 999999$ATTN: ORDER DEPARTMENT. -- DELIVERY CONF$IRMED FOR 09.00 ON  7/02/08 --$$$$$$
X$PLEASE NOTE THAT THIS BOOKING IN TIME MU$ST BE STRICTLY ADHERED TO.$VEHICLES ARRIVING EITHER EARLY OR LATE W$ILL BE ASKED TO WAIT FOR THE$$$$$$
X$NEXT AVAILABLE SLOT.$SUBJECT TO Company Name TERMS AND CONDITION$$$$$$$$
D$9744006$080204$080207$5010251999744$1$5010669950030$0000000024$0000000280$$
T$0001$5010251000006$$$$$$$$

this comprises 2 seperate orders - the H - denotes the header - the X is the details and the D is the footer

I've managed to import the file into a single array using the code below, but I need to be able to loop through each element and perform a function depending which column it is (all those numbers are dates, order references, delivery times, product codes etc in a fixed order)

this is the code

InputCSV = "import.csv"
Delimiter = "$"

Dim objFSO
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(file) then
 Dim objFile
 Set objFile = objFSO.GetFile(file)
 Dim iFileSize
 iFileSize = objFile.Size

 If iFileSize > 0 Then
   Set objTextStream = objFSO.OpenTextFile(file  ,1)
   ReadFile = objTextStream.ReadAll
   objTextStream.Close
   Set objTextStream = Nothing    
 Else
   ReadFile = ""

 End if
else
   ReadFile = "Error Reading File " & file
   Response.write "Error Reading File" & InputCSV & vbCrLf & vbCrLf
   errflag = 1
end if

End Function


Response.write "Import started for: " & InputCSV & vbCrLf & vbCrLf& "<br>"

CSVData2 = ReadFile(HTMLRoot & "\uploads\" & InputCSV )


CSVData3 = replace(CSVData2,chr(34),"")
CSVData = replace(CSVData3,chr(39),"")
CSVData_Lines = Split(CSVData,VbCrLF)

For x = 0 To UBound(CSVData_Lines)-1

CSVData_Lines2(x) = Split (CSVData_Lines(x), Delimiter) //' this is me trying to create multi array

//'InsertData = Replace(CSVData_Lines(x) , Delimiter, " - ")  //' this works to display the single array
//'Response.Write InsertData
//'Response.Write "<BR>"

Next

//'testtxt = Replace(CSVData_Lines2(1,1) , Delimiter, " - ")
//'Response.Write CSVData_Lines2(0,0)


thanks

Seb
Start Free Trial