How can i read a Excell file in Fortran 1

[danielinhu]

Hello everyone,

I want to make a program that is basicly a mineral database, so I have a list of minerals and after that i have some options like organize them etc.
But i have a problem, i have the Excel list and I want export it to read it in Fortran.
How can I do that??
Do i have to put the list in ".txt" format?

Greetings to everybody

 

[mikrom]

Save your Excel as CSV-file (Comma Separated Values), which is basically text file and process it with fortran.

 

[danielinhu]

And how can i export it to fortran?

Im using :

open (1, file='listaminerais.csv', status='old', iostat=stat)! Open file
if (stat .ne. 0) then
write(*,*) 'File cannot be opened
end if

But it isnt working!! :S

 

[mikrom]

Here is an example:
Given is a CSV file
csv_file.csv

CHARCOL;NUMCOL
foo;123
bar;456
baz;789

and here is the program
csv_read.f95

[COLOR=#a020f0]module[/color] csv_mod
  [COLOR=#2e8b57][b]type[/b][/color] csv_header_line
    [COLOR=#2e8b57][b]character[/b][/color]([COLOR=#ff00ff]10[/color]) :: c01 [COLOR=#0000ff]! column 01[/color]
    [COLOR=#2e8b57][b]character[/b][/color]([COLOR=#ff00ff]10[/color]) :: c02 [COLOR=#0000ff]! column 02[/color]
  [COLOR=#2e8b57][b]end type[/b][/color] csv_header_line

  [COLOR=#2e8b57][b]type[/b][/color] csv_data_line
    [COLOR=#2e8b57][b]character[/b][/color]([COLOR=#ff00ff]10[/color]):: c01 [COLOR=#0000ff]! column 01[/color]
    [COLOR=#2e8b57][b]integer[/b][/color]      :: c02 [COLOR=#0000ff]! column 02[/color]
  [COLOR=#2e8b57][b]end type[/b][/color] csv_data_line

[COLOR=#a020f0]contains[/color]
  [COLOR=#a020f0]subroutine[/color] parse_header(separator, line, header) 
    [COLOR=#2e8b57][b]character[/b][/color]([COLOR=#804040][b]*[/b][/color]), [COLOR=#2e8b57][b]intent[/b][/color]([COLOR=#2e8b57][b]in[/b][/color]) :: separator, line
    [COLOR=#2e8b57][b]type[/b][/color](csv_header_line), [COLOR=#2e8b57][b]intent[/b][/color]([COLOR=#2e8b57][b]out[/b][/color]) :: header
    
    [COLOR=#2e8b57][b]integer[/b][/color] :: separator_pos

    [COLOR=#0000ff]!separator position[/color]
    separator_pos [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]index[/color](line, separator)
    header%c01 [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]adjustl[/color]([COLOR=#008080]trim[/color](line(:separator_pos[COLOR=#804040][b]-[/b][/color][COLOR=#ff00ff]1[/color])))
    header%c02 [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]adjustl[/color]([COLOR=#008080]trim[/color](line(separator_pos[COLOR=#804040][b]+[/b][/color][COLOR=#ff00ff]1[/color]:)))
  [COLOR=#a020f0]end subroutine[/color] parse_header

  [COLOR=#a020f0]subroutine[/color] parse_data(separator, line, dta, nr)
    [COLOR=#2e8b57][b]character[/b][/color]([COLOR=#804040][b]*[/b][/color]), [COLOR=#2e8b57][b]intent[/b][/color]([COLOR=#2e8b57][b]in[/b][/color]) :: separator, line
    [COLOR=#2e8b57][b]integer[/b][/color], [COLOR=#2e8b57][b]intent[/b][/color]([COLOR=#2e8b57][b]in[/b][/color]) :: nr
    [COLOR=#2e8b57][b]type[/b][/color](csv_data_line), [COLOR=#2e8b57][b]dimension[/b][/color](:), [COLOR=#2e8b57][b]intent[/b][/color]([COLOR=#2e8b57][b]inout[/b][/color]) :: dta

    [COLOR=#2e8b57][b]integer[/b][/color] :: separator_pos
    [COLOR=#0000ff]!separator position[/color]
    separator_pos [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]index[/color](line, separator)  
    dta(nr)%c01 [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]adjustl[/color]([COLOR=#008080]trim[/color](line(:separator_pos[COLOR=#804040][b]-[/b][/color][COLOR=#ff00ff]1[/color])))
    [COLOR=#0000ff]! convert substring into integer number[/color]
    [COLOR=#804040][b]read[/b][/color] (line(separator_pos[COLOR=#804040][b]+[/b][/color][COLOR=#ff00ff]1[/color]:),[COLOR=#ff00ff]'(I3)'[/color]) dta(nr)%c02
  [COLOR=#a020f0]end subroutine[/color] parse_data
[COLOR=#a020f0]end module[/color] csv_mod


[COLOR=#a020f0]program[/color] csv_read
  [COLOR=#a020f0]use[/color] csv_mod
  [COLOR=#2e8b57][b]implicit[/b][/color] [COLOR=#2e8b57][b]none[/b][/color]

  [COLOR=#2e8b57][b]integer[/b][/color], [COLOR=#2e8b57][b]parameter[/b][/color] :: max_csv_lines [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]100[/color] [COLOR=#0000ff]! max number of lines in CSV[/color]
  [COLOR=#2e8b57][b]integer[/b][/color], [COLOR=#2e8b57][b]parameter[/b][/color] :: csv_columns   [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]2[/color]   [COLOR=#0000ff]! number of columns in CSV[/color]
  [COLOR=#2e8b57][b]integer[/b][/color] stat, line_nr, nr, j, computed_number
  [COLOR=#2e8b57][b]character[/b][/color] :: separator [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]';'[/color]
  [COLOR=#2e8b57][b]character[/b][/color]([COLOR=#ff00ff]80[/color]) :: line
  [COLOR=#2e8b57][b]type[/b][/color](csv_header_line) :: csv_header
  [COLOR=#2e8b57][b]type[/b][/color](csv_data_line), [COLOR=#2e8b57][b]dimension[/b][/color](max_csv_lines) :: csv_data  

  [COLOR=#0000ff]! open file[/color]
  [COLOR=#804040][b]open[/b][/color] ([COLOR=#ff00ff]1[/color], [COLOR=#804040][b]file[/b][/color][COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]'csv_file.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]

  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color]) [COLOR=#ff00ff]'Reading CSV-file...'[/color] 
  [COLOR=#0000ff]! process file[/color]
  line_nr [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color]
  [COLOR=#804040][b]do[/b][/color] [COLOR=#804040][b]while[/b][/color] ([COLOR=#ff00ff].true.[/color]) 
    [COLOR=#804040][b]read[/b][/color]([COLOR=#ff00ff]1[/color], [COLOR=#ff00ff]'(A)'[/color], [COLOR=#804040][b]end[/b][/color][COLOR=#804040][b]=[/b][/color][COLOR=#804040][b]99[/b][/color]) line
    line_nr [COLOR=#804040][b]=[/b][/color] line_nr [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]1[/color]
    [COLOR=#804040][b]if[/b][/color] (line_nr [COLOR=#804040][b].eq.[/b][/color] [COLOR=#ff00ff]1[/color]) [COLOR=#804040][b]then[/b][/color]
      [COLOR=#a020f0]call[/color] parse_header(separator, [COLOR=#008080]adjustl[/color]([COLOR=#008080]trim[/color](line)), csv_header)
    [COLOR=#804040][b]else[/b][/color] 
      [COLOR=#0000ff]! if line_nr > 1 then parse data line[/color]
      nr [COLOR=#804040][b]=[/b][/color] line_nr [COLOR=#804040][b]-[/b][/color] [COLOR=#ff00ff]1[/color]
      [COLOR=#a020f0]call[/color] parse_data(separator, [COLOR=#008080]adjustl[/color]([COLOR=#008080]trim[/color](line)), csv_data, nr)
    [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]1[/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]'Number of all lines found in CSV  = '[/color], line_nr
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#804040][b]*[/b][/color])  [COLOR=#ff00ff]'Number of data lines found in CSV = '[/color], nr

  [COLOR=#0000ff]! write the data[/color]
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color], [COLOR=#ff00ff]'(A)'[/color]) [COLOR=#ff00ff]'****************************************'[/color]
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color],[COLOR=#ff00ff]'(A10, A10, A10)'[/color]) csv_header%c01, [highlight #ffff00][COLOR=#0000ff]&[/color][/highlight]
                             [COLOR=#008080]adjustr[/color](csv_header%c02), [highlight #ffff00][COLOR=#0000ff]&[/color][/highlight] 
                             [COLOR=#ff00ff]'3*NUMCOL'[/color]
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color], [COLOR=#ff00ff]'(A)'[/color]) [COLOR=#ff00ff]'****************************************'[/color]
  [COLOR=#804040][b]do[/b][/color] j [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]1[/color], nr
     computed_number [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]3[/color] [COLOR=#804040][b]*[/b][/color] csv_data(j)%c02
     [COLOR=#804040][b]write[/b][/color] ([COLOR=#804040][b]*[/b][/color],[COLOR=#ff00ff]'(A10, I10, I10)'[/color]) csv_data(j)%c01, [highlight #ffff00][COLOR=#0000ff]&[/color][/highlight]
                                csv_data(j)%c02, [highlight #ffff00][COLOR=#0000ff]&[/color][/highlight]
                                computed_number

  [COLOR=#804040][b]end do[/b][/color]
  [COLOR=#804040][b]write[/b][/color]([COLOR=#804040][b]*[/b][/color], [COLOR=#ff00ff]'(A)'[/color]) [COLOR=#ff00ff]'****************************************'[/color]  
[COLOR=#a020f0]end program[/color] csv_read

Output:

$ g95  csv_read.f95 -o csv_read

$ csv_read
 Reading CSV-file...
 Done.
 Number of all lines found in CSV  =  4
 Number of data lines found in CSV =  3
****************************************
CHARCOL       NUMCOL  3*NUMCOL
****************************************
foo              123       369
bar              456      1368
baz              789      2367
****************************************

 

[SkipVought]

Can you use ActiveX Data Objects to QUERY the Excel Sheet as a table?

I know it can be done in VB. Don't know if you can manipulate objects in FORTRAN.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mikrom]

Gi SkipVought,
I'm using on the windows g95 and gfortran and I cannot imagine how to use ActiveX Objects with these compilers. But, maybe another commercial fortran compilers for windows support that.