Solved

VBScript and reading from MS Excel file

Posted on 2006-11-02
9
10,803 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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