Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBScript and reading from MS Excel file

Posted on 2006-11-02
9
Medium Priority
?
10,810 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 2000 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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

Independent Software Vendors: 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!

Question has a verified solution.

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

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…
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Loops Section Overview

610 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