Solved

VBScript and reading from MS Excel file

Posted on 2006-11-02
9
10,808 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

690 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