How to sum up CSV column without excel

[JRL]

UncleBen,

There might be a way to set up a schema.ini file to deal with your semicolon delimited file structure via SQL. There is also probably a regex solution out there somewhere. However, my skills are limited so here is a simple parsing routine. It works with the sample text you provided. Assuming the sample accurately represents the file structure possibilities it should give you accurate results. Speed might become an issue on large files.

Be sure to change the first line to reflect your file name and location.

Hope this is helpful,
Dick

Code: Select all

Let>vFileName=Path and name of your file


ReadFile>vFileName,var
Separate>var,crlf,vLine

Let>comma=,
Let>Col7Sum=0
Let>kk=0
Repeat>kk
  Add>kk,1
  Let>Value=vLine_%kk%
  Separate>Value,;,vItem
  
  //The following attempts to parse within the conditions you have specified for your file structure
  Let>kkk=0
  Let>kkkk=0
  Repeat>kkk
    add>kkk,1
    add>kkkk,1
    Let>Valuee=vItem_%kkk%
    Length>Valuee,Len
    MidStr>Valuee,len,1,testChar
    If>TestChar<>"
      Midstr>Valuee,1,1,testchar
      If>testchar="
        Add>kkk,1
        Let>Valueee=vItem_%kkk%
        Midstr>Valueee,1,1,testchar
        If>testchar<>"
          Length>Valueee,Len
          MidStr>Valueee,len,1,testChar
          If>testchar="
            Let>valuee=%valuee%;%valueee%
          Else
            sub>kkk,1
          EndIf
        EndIf
      EndIF
    EndIf
    //////////////////////////////////////  End of parsing  /////////////////////////////////////////
    
    //kkkk value is the column to sum
    If>kkkk=7
      StringReplace>valuee,",,valuee
      StringReplace>valuee,comma,.,valuee
      Add>Col7Sum,valuee
    EndIf
  Until>kkk=vItem_Count
  
Until>kk=vLine_count

Timer>stop

StringReplace>Col7Sum,.,comma,Col7Sum
MDL>Col7Sum

[rblack]

Regex is much faster and more stable. Here is a better solution with easypatterns:

Code: Select all

//RegEx to extract each CSV field from a single line to an array

Let>pattern=[CSVField]
RegEx>pattern,text,1,field,matchnumers,0

// and Regex to filter text to numbers from column 7

let>pattern=[not digit or comma]
RegEx>pattern,%field_7%,1,matches,num,1,,number
MDL>%number%

Now you just need to sum it for each line

[UncleBen]

Hello everyone!

Thanks for your replies. rblack - your solution is working pretty well but after few examples I've got a little problem.

There is my example:

Code: Select all

"column1";"column2";2012-08-06;2012-08-06;;320,00;"column7;"column8";column9;"column10"

I need to get the sixth column - "320,00" but the RegEx is breaking there and returns me a columns ";2012-08-06;2012-08-06;;320" and ",00;". I tried to replace string ;; to ; but it doesn't work.

Any ideas? Thanks in advance!