asked on

Input CSV file into array using ASP

You would think I could have figured this out from reading the old posts.

I did try however, and failed miserably. I would appreciate any help you could give.

The problem:

1. Data stored in CSV file that contains about 700 rows and 15 columns.
2. Columns can be text or number
3. Would like to read it into an array called StandingsTemp (x,y)

Once in the array, I will then manipulate it and append the data to an existing Access database.

Any suggestions on who to get it into the array in the first place?

Thanks.

HL

peter58

Is there fifteen columns in each row?

levinho

ASKER

Correct 700 rows with 15 columns per row

jitganguly

Is it one time job ? Then Go to access and use import feature and that way much easier.

If not, then why put it in array ?

1.Use a file dsn to read the .csv
2.Append it to access db

levinho

ASKER

The job is a registration database for a sports league

Every year, they get a monthly updated CSV file from an online registration provider. It has to be imported every month. In addition, there are fields that need to modified depending on the status of the league at the time (e.g., where these people get assigned, etc.)

Thus, it would be nice to be able to modify them as they were imported from the CSV file depending on what is need at the time.

I guess they could get directly imported into the access database but the format of the access database also can change without a corresponding timely change in the CSV format.

How do you use a DSN to read a CSV? Is it the same as opening an access database and reading into a recordset?

Thanks.

HL

ASKER CERTIFIED SOLUTION

jitganguly

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SquareHead

Here's an article that deals with CSV into an ASP array, it may provide a solution:

http://www.webmasterworld.com/forum47/759.htm

levinho

ASKER

Point well taken.

Thanks.

HL

peter58

I agree with jitganguly it would be better not to use asp but if you had to you can try the code below.

What you can do is open the file as below and iterate through each line.

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objTStream = objFSO.OpenAsTextStream(Your Files Path, Forreading)
Do While Not objTStream.AtEndOfStream
LineNumber = objTStream.Line
LineText = objTStream.ReadLine
MyArray = Split(LineText,",")
For I = 0 TO Ubound(MyArray)
StandingsTemp(LineNumber,I) = MyArray(I)
Next
Loop
objTStream.Close
Set objTStream = Nothing
Set objFSO = Nothing

levinho

ASKER

SquareHead,

Thanks but I already accepted before your comment.

Sorry.

HL

jitganguly

But, Peter thats one part. He(she) also needs it to the database and manipulate and things are complicated to me ( Sorry I hate arrays)

levinho

ASKER

Peter,

Thanks also.

HL
(a guy, mate, male, etc. and lover of soccer)

peter58

He did just ask on how to get it into an array, even if it would take forever and then it would probably take even longer to manipulate it.

SquareHead

@leninho: Well, this intrigued me and I went ahead and did it this way:

Take your CSV file and put it into a string (not sure if there's a limited number of characters that can go into a string) and split the string for each line, then split each line for each value, which is probably a half-assed way of doing what peter58 suggests... Anyway, here's the code and it works:

<%@ Language = VBScript %>
<% option explicit

dim strCSV, arCSV, i, arLine, j
dim mainArray()
redim mainArray(14,0)

strCSV = "abcd,1234,dsaq,3321,wwer,ffgb,vvcx,ssde,4r5t,6y7u,zxs2,8uyt,56r4,33wb,554e" & VBCRLF & _
            "66th,htgy,7u80,qwa4,3321,wwer,ffgb,vvcx,ssde,abcd,1234,dsaq,3321,wwer,ffgb" & VBCRLF & _
            "3321,wwer,ffgb,vvcx,ssde,4r5t,6y7u,zxs2,ffgb,vvcx,ssde,abcd,1234,ccxz,ddew" & VBCRLF & _
            "abcd,1234,dsaq,3321,wwer,ffgb,vvcx,ssde,4r5t,6y7u,zxs2,8uyt,56r4,33wb,554e" & VBCRLF & _
            "66th,htgy,7u80,qwa4,3321,wwer,ffgb,vvcx,ssde,abcd,1234,dsaq,3321,wwer,ffgb" & VBCRLF & _
            "3321,wwer,ffgb,vvcx,ssde,4r5t,6y7u,zxs2,ffgb,vvcx,ssde,abcd,1234,ccxz,ddew" & VBCRLF & _
            "abcd,1234,dsaq,3321,wwer,ffgb,vvcx,ssde,4r5t,6y7u,zxs2,8uyt,56r4,33wb,554e" & VBCRLF & _
            "66th,htgy,7u80,qwa4,3321,wwer,ffgb,vvcx,ssde,abcd,1234,dsaq,3321,wwer,ffgb" & VBCRLF & _
            "3321,wwer,ffgb,vvcx,ssde,4r5t,6y7u,zxs2,ffgb,vvcx,ssde,abcd,1234,ccxz,ddew"

arCSV = Split(strCSV,VBCRLF,-1,0)
for i = 0 to UBound(arCSV)
      arLine = Split(arCSV(i),",",-1,1)
      for j = 0 to UBound(arLine)
            mainArray(j,i) = arLine(j)
      next
      if i = UBound(arCSV) then exit for
      redim preserve mainArray(14,i+1)
next

response.write "<br><br>mainArray(0,1): " & mainArray(0,1) & "<br>IsArray: " & IsArray(mainArray)
%>