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","Tim e"
803,E7,03,
1803,E7,03,3.33 30 us
2803,E7,03,3.33 30 us
3803,E7,03,3.33 30 us
4803,E7,03,3.33 45 us
5803,E7,03,3.33 30 us
6803,E7,03,3.33 30 us
7803,E7,03,3.33 30 us
8803,E7,03,3.33 30 us
9803,E7,03,3.33 45 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","Tim e"
803,E7,03,
1803,E7,03,3.33 30 us
2803,E7,03,3.33 30 us
3803,E7,03,3.33 30 us
4803,E7,03,3.33 45 us
5803,E7,03,3.33 30 us
6803,E7,03,3.33 30 us
7803,E7,03,3.33 30 us
8803,E7,03,3.33 30 us
9803,E7,03,3.33 45 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","Tim e"
803,E7,03,
1803,E7,03,3.33 30 us
2803,E7,03,3.33 30 us
3803,E7,03,3.33 30 us
4803,E7,03,3.33 45 us
5803,E7,03,3.33 30 us
6803,E7,03,3.33 30 us
7803,E7,03,3.33 30 us
8803,E7,03,3.33 30 us
9803,E7,03,3.33 45 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+... .=xxxx?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+... .=xxxx?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?Tha nk you for your comment,i really appreciate it!

Const ForReading = 1
Set objFSO = CreateObject("S cripting.FileSy stemObject")
Set objTextFile = objFSO.OpenText File _
("c:\test2\test .txt", ForReading)

Do Until objTextFile.AtE ndOfStream
strNextLine = objTextFile.Rea dline
arrServiceList = Split(strNextLi ne , ",")
For i = 3 to Ubound(arrServi ceList)
Wscript.Echo "Service: " & arrServiceList( i)
Next
Loop