We help IT Professionals succeed at work.

Input CSV file into array using ASP

levinho
levinho asked
on
1,330 Views
Last Modified: 2008-03-17
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
Comment
Watch Question

Commented:
Is there fifteen columns in each row?

Author

Commented:
Correct 700 rows with 15 columns per row
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

Author

Commented:
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Here's an article that deals with CSV into an ASP array, it may provide a solution:

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

Author

Commented:
Point well taken.

Thanks.

HL

Commented:
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

Author

Commented:
SquareHead,

Thanks but I already accepted before your comment.

Sorry.

HL
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)

Author

Commented:
Peter,

Thanks also.

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

Commented:
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.
@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)
%>

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.