By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Read data from csv file using VBScript

P: 8
I would like to read a column of data in csv file on each rows and get the data to perform calculation as shown below:

"Sample Number","POD1","POD5","Time"
803,E7,03,
1803,E7,03,3.3330 us
2803,E7,03,3.3330 us
3803,E7,03,3.3330 us
4803,E7,03,3.3345 us
5803,E7,03,3.3330 us
6803,E7,03,3.3330 us
7803,E7,03,3.3330 us
8803,E7,03,3.3330 us
9803,E7,03,3.3345 us

I will need to read the values in the last column 3.3330 u and use this value to convert it to frequency. Note the 3.3330us is in second which means it reads as 3.3330micro second. I would also like to repeat it for every rows until end, how should i perform that. Please help me to come out with simple vbscript command on that.Thank you
Jul 10 '08 #1
Share this Question
Share on Google+
8 Replies


gpraghuram
Expert 100+
P: 1,275
If its in C or C++ i can help u.
Since its in VB why cant u try posting this in .NET forum

Raghu
Jul 10 '08 #2

Banfa
Expert Mod 5K+
P: 8,916
Since its in VB why cant u try posting this in .NET forum

Raghu
Better still try posting in the VB forum :D

I am moving this post to a more relevant forum anyway.
Jul 10 '08 #3

P: 8
If its in C or C++ i can help u.
Since its in VB why cant u try posting this in .NET forum

Raghu
Raghu, thanks for your proposal.

Can you show to me if it is running using C++? I need to learn as well.

meiliong
Jul 11 '08 #4

gpraghuram
Expert 100+
P: 1,275
Raghu, thanks for your proposal.

Can you show to me if it is running using C++? I need to learn as well.

meiliong
I think this is moved to new forum...why cant u post a new thread for this...in C++ forum

raghu
Jul 11 '08 #5

!NoItAll
100+
P: 296
"Sample Number","POD1","POD5","Time"
803,E7,03,
1803,E7,03,3.3330 us
2803,E7,03,3.3330 us
3803,E7,03,3.3330 us
4803,E7,03,3.3345 us
5803,E7,03,3.3330 us
6803,E7,03,3.3330 us
7803,E7,03,3.3330 us
8803,E7,03,3.3330 us
9803,E7,03,3.3345 us


Assuming you know how to put the data into a variable - we'll call it sCSVData

Expand|Select|Wrap|Line Numbers
  1. Dim sCSVLine() as String
  2. Dim sCSVElements() as String
  3. Dim I as String
  4.  
  5. sCSVLine = Split(sCSVData, vbcrlf)
  6.  
  7. For I = 0 to Ubound(sCSVLine)
  8.  
  9. sCSVElements = Split(sCSVLine(I), ",")
  10.  
  11. 'at this point you will have each element of one line of the csv to process as you need.
  12.  
  13.  
  14. Next I

Danger: CSV files can be delimited with just commas, or commas and quotes. This routine does NOT work with commas and quotes - just the data you provided.
Jul 12 '08 #6

P: 8
"Sample Number","POD1","POD5","Time"
803,E7,03,
1803,E7,03,3.3330 us
2803,E7,03,3.3330 us
3803,E7,03,3.3330 us
4803,E7,03,3.3345 us
5803,E7,03,3.3330 us
6803,E7,03,3.3330 us
7803,E7,03,3.3330 us
8803,E7,03,3.3330 us
9803,E7,03,3.3345 us


Assuming you know how to put the data into a variable - we'll call it sCSVData

Expand|Select|Wrap|Line Numbers
  1. Dim sCSVLine() as String
  2. Dim sCSVElements() as String
  3. Dim I as String
  4.  
  5. sCSVLine = Split(sCSVData, vbcrlf)
  6.  
  7. For I = 0 to Ubound(sCSVLine)
  8.  
  9. sCSVElements = Split(sCSVLine(I), ",")
  10.  
  11. 'at this point you will have each element of one line of the csv to process as you need.
  12.  
  13.  
  14. Next I

Danger: CSV files can be delimited with just commas, or commas and quotes. This routine does NOT work with commas and quotes - just the data you provided.
Thank you, but how about if i would like to grab the data and perform calculation for example 3.3330+3.3330+3.3330+3.3330+3.3330+3.3330+....=xxx x?How should i proceed from here?
Jul 13 '08 #7

!NoItAll
100+
P: 296
Thank you, but how about if i would like to grab the data and perform calculation for example 3.3330+3.3330+3.3330+3.3330+3.3330+3.3330+....=xxx x?How should i proceed from here?
Expand|Select|Wrap|Line Numbers
  1.   Dim sCSVLine() as String
  2.   Dim sCSVElements() as String
  3.   Dim I as String
  4.   Dim lTime() as Long
  5.   Dim lCSVCount as Long
  6.   Dim sTime as String
  7.  
  8. sCSVLine = Split(sCSVData, vbcrlf)
  9. lCSVCount = Ubound(sCSVLine)
  10. Redim lTime(lCSVCount)
  11.  
  12.    'start at 1 because the first set of values is just the header
  13.   For I = 1 to lCSVCount
  14.          sCSVElements = Split(sCSVLine(I), ",")
  15.          'i believe element 3 contains the value you want
  16.          sTime = Trim$(Replace(sCSVElements(3), "us"))
  17.          if IsNumeric(sTime) = True then
  18.               lTime(I) = cLng(sTime)  
  19.          else
  20.               lTime(I) = -1
  21.          end if
  22.          DoEvents   'this is here to give the OS  a break from this rather tight loop
  23.    Next I
  24.  
'when the loop is complete you will have (lCSVCount -1) (skip element 0) of the microsecond (us) values in the one dimensional lTime array. Now you can do something simple like ltime(1) + ltime(2) + ltime(3) etc...
Any invalid times will be -1 so you will want to skip those so it's best to do your math in a loop.
Jul 13 '08 #8

P: 8
Expand|Select|Wrap|Line Numbers
  1.   Dim sCSVLine() as String
  2.   Dim sCSVElements() as String
  3.   Dim I as String
  4.   Dim lTime() as Long
  5.   Dim lCSVCount as Long
  6.   Dim sTime as String
  7.  
  8. sCSVLine = Split(sCSVData, vbcrlf)
  9. lCSVCount = Ubound(sCSVLine)
  10. Redim lTime(lCSVCount)
  11.  
  12.    'start at 1 because the first set of values is just the header
  13.   For I = 1 to lCSVCount
  14.          sCSVElements = Split(sCSVLine(I), ",")
  15.          'i believe element 3 contains the value you want
  16.          sTime = Trim$(Replace(sCSVElements(3), "us"))
  17.          if IsNumeric(sTime) = True then
  18.               lTime(I) = cLng(sTime)  
  19.          else
  20.               lTime(I) = -1
  21.          end if
  22.          DoEvents   'this is here to give the OS  a break from this rather tight loop
  23.    Next I
  24.  
'when the loop is complete you will have (lCSVCount -1) (skip element 0) of the microsecond (us) values in the one dimensional lTime array. Now you can do something simple like ltime(1) + ltime(2) + ltime(3) etc...
Any invalid times will be -1 so you will want to skip those so it's best to do your math in a loop.
Thanks, i think it did actually gave me some idea. previously i did come out with my own code, is that mean if i wanted to add all the values on element 3 i just perform as above or how should i continue from my own code rather use the one you recommended?Thank you for your comment,i really appreciate it!

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("c:\test2\test.txt", ForReading)

Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.Readline
arrServiceList = Split(strNextLine , ",")
For i = 3 to Ubound(arrServiceList)
Wscript.Echo "Service: " & arrServiceList(i)
Next
Loop
Jul 13 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.