Read data from csv file using VBScript

[meiliong]

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

[gpraghuram]

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

Raghu

[Banfa]

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.

[meiliong]

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

[gpraghuram]

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

[!NoItAll]

"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

Dim sCSVLine() as String
Dim sCSVElements() as String
Dim I as String
 
sCSVLine = Split(sCSVData, vbcrlf)
 
For I = 0 to Ubound(sCSVLine)
 
sCSVElements = Split(sCSVLine(I), ",")
 
'at this point you will have each element of one line of the csv to process as you need.
 
 
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.

[meiliong]

"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

Dim sCSVLine() as String
Dim sCSVElements() as String
Dim I as String
 
sCSVLine = Split(sCSVData, vbcrlf)
 
For I = 0 to Ubound(sCSVLine)
 
sCSVElements = Split(sCSVLine(I), ",")
 
'at this point you will have each element of one line of the csv to process as you need.
 
 
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?

[!NoItAll]

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?

  Dim sCSVLine() as String
  Dim sCSVElements() as String
  Dim I as String
  Dim lTime() as Long
  Dim lCSVCount as Long
  Dim sTime as String
 
sCSVLine = Split(sCSVData, vbcrlf)
lCSVCount = Ubound(sCSVLine)
Redim lTime(lCSVCount)
 
   'start at 1 because the first set of values is just the header
  For I = 1 to lCSVCount
         sCSVElements = Split(sCSVLine(I), ",")
         'i believe element 3 contains the value you want
         sTime = Trim$(Replace(sCSVElements(3), "us"))
         if IsNumeric(sTime) = True then
              lTime(I) = cLng(sTime)  
         else
              lTime(I) = -1
         end if
         DoEvents   'this is here to give the OS  a break from this rather tight loop
   Next I
 

'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.

[meiliong]

  Dim sCSVLine() as String
  Dim sCSVElements() as String
  Dim I as String
  Dim lTime() as Long
  Dim lCSVCount as Long
  Dim sTime as String
 
sCSVLine = Split(sCSVData, vbcrlf)
lCSVCount = Ubound(sCSVLine)
Redim lTime(lCSVCount)
 
   'start at 1 because the first set of values is just the header
  For I = 1 to lCSVCount
         sCSVElements = Split(sCSVLine(I), ",")
         'i believe element 3 contains the value you want
         sTime = Trim$(Replace(sCSVElements(3), "us"))
         if IsNumeric(sTime) = True then
              lTime(I) = cLng(sTime)  
         else
              lTime(I) = -1
         end if
         DoEvents   'this is here to give the OS  a break from this rather tight loop
   Next I
 

'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