Solved

VBScript and reading from MS Excel file

Posted on 2006-11-02
9
10,805 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
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!

 
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

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

Suggested Solutions

Title # Comments Views Activity
Microsoft SQL ADO Conn Issue 6 59
Call a function within the ASP code 4 26
PHP encrypted string and passing to a ASP Page 12 49
ASP Classic - Read XML File inner content 10 49
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…
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/…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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