Help with .csv

[WSUPhys]

Hello,

I am currently working on a project where I need to read the data from a .csv file into an array and extract the values for multiple operations. I am wondering how do I open this and format it in Fortran? I do not have any experience with .csv files and when I try to copy it into a text array it does not work well. The file can be found here:Link The actual data starts on line 11 and I am trying to extract the first 4 columns. Thank you in advance.

 

[mikrom]

WSUPhys,
CSV is simply a text file. Your CSV looks like this:
DATA-002.CSV

;Title, [URL unfurl="true"]http://www.gcdataconcepts.com,[/URL] X6-1b
;Version, 1000, Build num, 0x97B, Build date, 20101014 12:47:12,  SN:CCDC22010050493
;Start_time, 2013-03-19, 11:03:51.003
;Switch, Unknown
;Temperature, 24.75, deg C,  Vbat, 1438, mv
;Gain, high
;SampleRate, 80,Hz
;Deadband, 0, counts
;DeadbandTimeout, 5,sec
;Headers, time,Ax,Ay,Az
1.789220,1012,-15,-176
1.801521,998,-27,-173
1.813878,986,-42,-164
1.826212,958,-52,-155
1.838513,958,-60,-156
1.850870,966,-63,-147
1.863171,952,-51,-155
1.875504,978,-28,-160
1.887861,1014,-13,-175
1.900162,1051,-16,-190
...
...

So, simply delete the first 10 header lines and from the rest of lines sweep out the commas (I done it with sed).
Then you will get the file with 4 columns of numbers, which you could easily process with fortran.

Example: WSUPhys_csv.f95

[COLOR=#a020f0]program[/color] WSUPhys_csv
  [COLOR=#2e8b57][b]implicit[/b][/color] [COLOR=#2e8b57][b]none[/b][/color]

  [COLOR=#2e8b57][b]integer[/b][/color] :: stat, cmd_rc, num_lines, ivar1, ivar2, ivar3
[COLOR=#2e8b57][b]  real[/b][/color] :: rvar
  [COLOR=#0000ff]! command string - using sed:[/color]
  [COLOR=#0000ff]! delete lines 1-10 and replace "," with " "[/color]
  [COLOR=#2e8b57][b]character[/b][/color]([COLOR=#804040][b]*[/b][/color]), [COLOR=#2e8b57][b]parameter[/b][/color] :: cmd_string [COLOR=#804040][b]=[/b][/color] [COLOR=#804040][b]&[/b][/color]
     [COLOR=#ff00ff]'sed -e "1,10d; s/,/ /g" DATA-002.CSV > DATA-002.txt'[/color]

  
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]"Preprocessing file..."[/color]
  cmd_rc [COLOR=#804040][b]=[/b][/color] system (cmd_string)
  [COLOR=#0000ff]! if command not succesfull then stop the program[/color]
  [COLOR=#804040][b]if[/b][/color] ( cmd_rc [COLOR=#804040][b].ne.[/b][/color] [COLOR=#ff00ff]0[/color] ) [COLOR=#804040][b]then[/b][/color] 
    [COLOR=#804040][b]stop[/b][/color] [COLOR=#ff00ff]'system: Error - command failed !!!'[/color]
  [COLOR=#804040][b]else[/b][/color]
    [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'...done.'[/color]
  [COLOR=#804040][b]end if[/b][/color]

  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]"Reading file..."[/color]

  [COLOR=#0000ff]! open input file[/color]
  [COLOR=#804040][b]open[/b][/color]([COLOR=#ff00ff]10[/color],[COLOR=#804040][b]file[/b][/color][COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]'DATA-002.txt'[/color],[COLOR=#804040][b]status[/b][/color][COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]'old'[/color],[COLOR=#804040][b]iostat[/b][/color][COLOR=#804040][b]=[/b][/color]stat)
  [COLOR=#804040][b]if[/b][/color] (stat [COLOR=#804040][b].ne.[/b][/color] [COLOR=#ff00ff]0[/color]) [COLOR=#804040][b]then[/b][/color]
    [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'File cannot be opened !'[/color]
    [COLOR=#804040][b]go to[/b][/color] [COLOR=#ff00ff]99[/color]   
  [COLOR=#804040][b]end if[/b][/color]

  num_lines [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color]
  [COLOR=#804040][b]do[/b][/color]
    [COLOR=#804040][b]read[/b][/color]([COLOR=#ff00ff]10[/color],[COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]end[/b][/color][COLOR=#804040][b]=[/b][/color][COLOR=#804040][b]99[/b][/color], [COLOR=#804040][b]iostat[/b][/color][COLOR=#804040][b]=[/b][/color]stat) rvar, ivar1, ivar2, ivar3
    [COLOR=#804040][b]if[/b][/color] (stat [COLOR=#804040][b].ne.[/b][/color] [COLOR=#ff00ff]0[/color]) [COLOR=#804040][b]then[/b][/color]
      [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'Error reading data !'[/color]
      [COLOR=#804040][b]go to[/b][/color] [COLOR=#ff00ff]99[/color]
    [COLOR=#804040][b]end if[/b][/color]
    num_lines [COLOR=#804040][b]=[/b][/color] num_lines [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]1[/color]
    [COLOR=#804040][b]if[/b][/color] (num_lines [COLOR=#804040][b]<=[/b][/color]  [COLOR=#ff00ff]10[/color]) [COLOR=#804040][b]then[/b][/color]
      [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) num_lines, [COLOR=#ff00ff]':'[/color], rvar, ivar1, ivar2, ivar3
    [COLOR=#804040][b]end if[/b][/color]
  [COLOR=#804040][b]end do[/b][/color]

  [COLOR=#0000ff]! close file[/color]
[COLOR=#804040][b]  99 continue[/b][/color]
  [COLOR=#804040][b]close[/b][/color] ([COLOR=#ff00ff]10[/color])
  [COLOR=#0000ff]![/color]
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'...done.'[/color]
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'Lines processed: '[/color], num_lines
[COLOR=#a020f0]end program[/color] WSUPhys_csv

Output:

$ gfortran WSUPhys_csv.f95 -o WSU_Phys_csv

$ WSU_Phys_csv
 Preprocessing file...
 ...done.
 Reading file...
           1 :   1.7892200            1012         -15        -176
           2 :   1.8015209             998         -27        -173
           3 :   1.8138781             986         -42        -164
           4 :   1.8262120             958         -52        -155
           5 :   1.8385130             958         -60        -156
           6 :   1.8508700             966         -63        -147
           7 :   1.8631710             952         -51        -155
           8 :   1.8755040             978         -28        -160
           9 :   1.8878610            1014         -13        -175
          10 :   1.9001620            1051         -16        -190
 ...done.
 Lines processed:        72000

 

[WSUPhys]

Wow, you are awesome. This is a very elegant solution that will help with the fact that I have multiple data sets to process. I am still fairly new to Fortran coding so I would've never thought of something close to this. Thanks and have a nice day.

 

[salgerman]

Well, the call to sed is totally unnecessary; let alone that makes the program non-portable. Also, commas work just fine as a separator, no need to replace them with spaces. All together, there simply isn't any need for pre-processing.

So, taking mikron's program, removing the system call to sed and forgetting about hard coded file names, here is the next version of the program still under the assumption that you know for a fact that you need to skip the first 10 lines of the csv file:

program rd
    implicit none
    integer stat, cmd_rc, num_lines, ivar1, ivar2, ivar3
    real :: rvar
    integer i
    character*100 line
    
    do i=1,10
        read(*,*) line
    end do
    
    num_lines = 0
    do
      read(*,*,end=99, iostat=stat) rvar, ivar1, ivar2, ivar3
      if (stat .ne. 0) then
        write(*,*) 'Error reading data !'
        exit
      end if
      num_lines = num_lines + 1
      if (num_lines <=  10) then
        write(*,*) num_lines, ':', rvar, ivar1, ivar2, ivar3
      end if
    end do
    99 continue
    
    write(*,*) '...done.'
    write(*,*) 'Lines processed: ', num_lines    
end program rd

Usage of this program is done with re-direction:

rd < DATA-002.CSV

The next version of the program could be one that can handle skipping any number of lines that start with semicolon by reading lines into a string, first, and checking for it; if not there, then it reads the values from the line.

Germán

 

[xwb]

Another alternative is to read the line in as a string. Check if the first character is a ;. If it is, skip the line, otherwise read the string into the variables. That way, if lines of data can be commented out with a ; or the header can increase in size and you don't have to change the program.

 

[WSUPhys]

thank you for the advice on generalizing it.

 

[mikrom]

I automatically assumed that Fortran could not simply read comma separated numbers ... I was stupid, sorry ;-)

 

[mikrom]

I modified it like xwb suggested:

[COLOR=#a020f0]program[/color] WSUPhys_csv
  [COLOR=#2e8b57][b]implicit[/b][/color] [COLOR=#2e8b57][b]none[/b][/color]

  [COLOR=#2e8b57][b]integer[/b][/color] :: stat, num_lines, ivar1, ivar2, ivar3
[COLOR=#2e8b57][b]  real[/b][/color] :: rvar
  [COLOR=#2e8b57][b]character[/b][/color][COLOR=#804040][b]*[/b][/color][COLOR=#ff00ff]80[/color] :: line
  
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]"Reading file..."[/color]

  [COLOR=#0000ff]! open input file[/color]
  [COLOR=#804040][b]open[/b][/color]([COLOR=#ff00ff]10[/color],[COLOR=#804040][b]file[/b][/color][COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]'DATA-002.csv'[/color],[COLOR=#804040][b]status[/b][/color][COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]'old'[/color],[COLOR=#804040][b]iostat[/b][/color][COLOR=#804040][b]=[/b][/color]stat)
  [COLOR=#804040][b]if[/b][/color] (stat [COLOR=#804040][b].ne.[/b][/color] [COLOR=#ff00ff]0[/color]) [COLOR=#804040][b]then[/b][/color]
    [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'File cannot be opened !'[/color]
    [COLOR=#804040][b]go to[/b][/color] [COLOR=#ff00ff]99[/color]   
  [COLOR=#804040][b]end if[/b][/color]

  num_lines [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color]
  [COLOR=#804040][b]do[/b][/color]
    [COLOR=#804040][b]read[/b][/color]([COLOR=#ff00ff]10[/color],[COLOR=#ff00ff]'(A)'[/color],[COLOR=#804040][b]end[/b][/color][COLOR=#804040][b]=[/b][/color][COLOR=#804040][b]99[/b][/color], [COLOR=#804040][b]iostat[/b][/color][COLOR=#804040][b]=[/b][/color]stat) line 
    [COLOR=#804040][b]if[/b][/color] (stat [COLOR=#804040][b].ne.[/b][/color] [COLOR=#ff00ff]0[/color]) [COLOR=#804040][b]then[/b][/color]
      [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'Error reading data !'[/color]
      [COLOR=#804040][b]go to[/b][/color] [COLOR=#ff00ff]99[/color]
    [COLOR=#804040][b]end if[/b][/color]
    [COLOR=#0000ff]! skip comment/header lines beginning with ";"[/color]
    [COLOR=#804040][b]if[/b][/color] ([COLOR=#008080]adjustl[/color]([COLOR=#008080]trim[/color](line([COLOR=#ff00ff]1[/color]:[COLOR=#ff00ff]1[/color]))) [COLOR=#804040][b].eq.[/b][/color] [COLOR=#ff00ff]';'[/color]) [COLOR=#804040][b]then[/b][/color]
      [COLOR=#804040][b]cycle[/b][/color]
    [COLOR=#804040][b]end if[/b][/color]
    [COLOR=#0000ff]! read string line into numeric variables[/color]
    [COLOR=#804040][b]read[/b][/color](line,[COLOR=#804040][b]*[/b][/color]) rvar, ivar1, ivar2, ivar3
    num_lines [COLOR=#804040][b]=[/b][/color] num_lines [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]1[/color]    
    [COLOR=#804040][b]if[/b][/color] (num_lines [COLOR=#804040][b]<=[/b][/color]  [COLOR=#ff00ff]10[/color]) [COLOR=#804040][b]then[/b][/color]
      [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) num_lines, [COLOR=#ff00ff]':'[/color], rvar, ivar1, ivar2, ivar3
    [COLOR=#804040][b]end if[/b][/color]
  [COLOR=#804040][b]end do[/b][/color]

  [COLOR=#0000ff]! close file[/color]
  [COLOR=#6a5acd]99[/color] [COLOR=#804040][b]continue[/b][/color]
  [COLOR=#804040][b]close[/b][/color] ([COLOR=#ff00ff]10[/color])
  [COLOR=#0000ff]![/color]
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'...done.'[/color]
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'Lines processed: '[/color], num_lines
[COLOR=#a020f0]end program[/color] WSUPhys_csv

 

[WSUPhys]

thank you!