Solved

VBScript and reading from MS Excel file

Posted on 2006-11-02
9
10,802 Views
Last Modified: 2012-05-05
I have an excel file in the following format:

ColumnName
_____________
TEST
TEST05
11TEST02
123455
98666
VALUE09


I need to use VBScript to read the data from the file into an array. What's the best way to do that?

Another problem is that the file might also be in CSV format or TXT format. How to I take care of a number of different formats?
0
Comment
Question by:YZlat
  • 6
  • 3
9 Comments
 
LVL 19

Accepted Solution

by:
peh803 earned 500 total points
ID: 17860480
I have done this many times using the filesystem object.  Have you used this before?  

Here's a quick example:

Dim objFSO : objFSO = server.createobject("scripting.filesystemobject")
Dim sFileNameAndPath : sFileNameAndPath = server.mappath("file.txt")
Dim sContents : sContents = getFileContents(sFileNameAndPath, objFSO)
Dim vRows : vRows = split(sContents, vbCrLf)

'' now, you've got your entire file into a 1D array of lines, where ubound(vRows)+1 = total lines in your file

Dim i
for i=0 to ubound(vRows)
   response.write "vRows(" & i & "): " & vRows(i) & "<BR>"
next

Function getFileContents(sFileNameAndPath, objFSO)
   Const ForReading = 1, ForWriting = 2
   'msgbox(sFileNameAndPath)
   Dim objFile: Set objFile = objFSO.OpenTextFile(sFileNameAndPath, ForReading)
   getFileContents = objFile.ReadAll
   set objFile = nothing
End Function

Hope that helps!
Phil
0
 
LVL 19

Assisted Solution

by:peh803
peh803 earned 500 total points
ID: 17860516
By the way, the above method will work well with *.txt or *.csv files.

If you're using an excel spreadsheet, you can use ADO to query the spreadsheet similar to how you might query other data sources...

Something like this:
Dim adoCn
Set adoCn = CreateObject("ADODB.Connection")
With adoCn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = _
            "Data Source=" & server.mappath("your_excel_file.xls") & ";" & _
            "Extended Properties=Excel 8.0;"
      .Open
End With
Dim sSQL
Dim rs
Dim myArr
set rs = server.CreateObject("Adodb.recordset")
sSQL = "SELECT * FROM [transactions$]"

rs.open sSQL, adoCn

response.write "<table>"
response.write "<tr>"
for each field in rs.fields
   response.write "<td>" & field.name & "</td>"
next
response.write "</tr>"

do while not rs.eof
   response.write "<tr>"
   for each field in rs.fields
      response.write "<td>" & field.value & "</td>"
   next
   response.write "</tr>"
   rs.moveNext()
loop

response.write "</table>"
rs.close
set rs = nothing
adoCn.close
set adoCn = nothing

HTH!
peh803
0
 
LVL 19

Expert Comment

by:peh803
ID: 17860538
>>  I need to use VBScript to read the data from the file into an array.

If this is the case, then of course you can just use the "getRows()" method of the recordset once you've fetched the excel records into the recordset...

So, in the example above, after you do this:
rs.open sSQL, adoCn

You'd do something like this:
Dim vMyArray
if not rs.eof then
   vMyArray = rs.getRows()
end if

Now, you'll have a 2D array of all the elements from your excel spreadsheet, where ubound(vMyArray,1)+1 is the number of columns from your SS, and ubound(vMyArray,2)+1 is the number of rows.

HTH,
Phil
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 35

Author Comment

by:YZlat
ID: 17861215
peh803, thank you for quick response.

I Modified your code slightly:

Function ReadDataFromFile(sFileNameAndPath)
      Dim objFSO,i,sContents,vRows
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      sContents = getFileContents(sFileNameAndPath, objFSO)
      vRows = split(sContents, vbCrLf)
      ReadDataFromFile=vRows
End Function

Function getFileContents(sFileNameAndPath, objFSO)
      Const ForReading = 1, ForWriting = 2
      'msgbox(sFileNameAndPath)
      Dim objFile
      Set objFile = objFSO.OpenTextFile(sFileNameAndPath, ForReading)
      getFileContents = objFile.ReadAll
      Set objFile = nothing
End Function

The above works well for a text file but doesn't work for Excel. When I try to split Excel file, I get values consisting of weird symbols like "&#9633;&#9643;^º&#9643;&#9633;"
0
 
LVL 19

Expert Comment

by:peh803
ID: 17861388
Yeah -- for excel, you'll need to use the second method that I posted -- see my 2nd comment, please.  If you're using this and you're still having issues, let me know and I'll see what I can do to continue to help.

Thanks!

Regards,
Phil
0
 
LVL 35

Author Comment

by:YZlat
ID: 17861419
Thanks, I'll try the second one.

What if my Excel file has multiple columns and I only need to read one of them?
0
 
LVL 19

Assisted Solution

by:peh803
peh803 earned 500 total points
ID: 17861502
If you're using multiple columns in Excel, I would recommend using my sample code to write out what ado thinks the column names are for excel -- the code I gave you should print out the recordset field names in the first row.  So, once you've done this, you should be able to refer to excel columns in similar ways that you can refer to columns in other data sources...
for example,
"SELECT [column1], [column2] from [$sheet1]"

This is just an example, of course.  Excel + ado is a bit touchy, and you may have to play with it a little to get it to behave.

The other option is to just suck everything in, and once you get the array in memory using rs.getRows(), you can then only use the array elements that you're interested in.

HTH,
Phil
0
 
LVL 35

Author Comment

by:YZlat
ID: 17861607
Thanks!
0
 
LVL 19

Expert Comment

by:peh803
ID: 17861651
No problem, glad to help!

Regards,
Phil / peh803
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Response.Flush in Classic ASP causing TIME_WAIT ports 9 100
Add server behaviors to Dreamweaver CC 2015 2 159
ASP Classic - Load test 2 49
ASP CDO - Limiting Connections 2 52
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question