Reading Data from an Excel Spreadsheet?

[GWats1]

I did read over the Sample F90 Autodice and it is way more complicated than my simple Fortran IV mind can get around. I don't need to output into Excel, I just want to read a few cells of real numbers to populate my data arrays.

I have an old Fortran program that I put in some static data and do some calculations. I thought it would be a little easier to read the data from a file that is editable by a user and I can do that but then I thought a simple Excel spreadsheet might be easier for a novice user to edit. 

So, my thought was to open a simple Dialog window and have the user select the BOLTS.XLSX file and read the data into my arrays for each column. I used to have 17 elements in each 1 dimension array for bolt diameter, area, etc. and the user might only want a few diameters to process.

If it is all to complicated to get Fortran to read data, I guess I could have the user edit the Excel rows and run a Macro to print out the data to a file and read that into my program, but I thought I would try to understand how Fortran could do it.

TIA 

[andrew_4619]

Well my first thought was to say that  reading and writing to Excel is easy but in reality that is only after quite a  bit of work. There is quite a lot of house keeping before you can get to the basic point of reading or writing a cell. You need a COM interface layer and then some utility functions that sit in a layer between your application and the COM interface.   I would suggest it is easier to have a macro in excel to make a formatted text file or do that manually or even save the whole sheet as a .csv file and parse from that. 

[GWats1]

I suspect you are correct. When I wrote the program (it reads an XML output file from another program that analyzes steel poles), it designs several base plates but uses the anchor bolts that I use with set diameters and number of threads per inch (TPI). Other people can use my post processor but may want to use different TPI for the diameter and a different steel material.  I wanted to make it a little easier for the user to supply their bolt diameters and TPI's, so I thought about Excel.

I have an axiom. Never write a program for others to use. I was happy with my program and used it myself.  If I wanted to add something, I did it. Then someone asked to use it and I had to write a user's manual but first handed them the Fortran code and told them to look at the Read Commands and see what it was asking.    This did not work well and got comments back, "this program is nice, but it does not output what I wanted, so can you add that?"  When you write a program for yourself, you are happy when it compiles and runs and gives results. If you want to make additions, you do that and you are happy again. When you give it to other Engineers, that is when the criticism starts.       

[JohnNichols]

Try CSV format, you can read it EXCEL and also as a TEXT file in Fortran.  

The other way is ODBC connection to EXCEL but that requires another program such as FORDBC. 

[jimdempseyatthecove]

Assuming you want a quick and simple method that requires a tiny bit of operator competence:

program ReadExcel
    implicit none
    character(len=1024) :: line
    integer :: j
    real :: value
    do
        read(*,'(A)') line
        j = len(trim(line))
        if(j==0) exit
        read(line(1:j),*) value
        print *,value
    end do
end program ReadExcel

Procedure (with Excel open and Fortran program running at input:

1. In Excel
2. select a column (or subsection of column) of your data (doesn't work with rows or blocks)
3. Right-Click | Copy
4. Click on Fortran console window
5. Right click to paste column
6. Press Enter to end input

Notes:

rows do not work as the internal code for read strips out the tab character.

Columns work, however my short code for termination is to exit the input loop on a blank line but Excel won't add a blank line thus the above would not see a blank line a column of data immediately followed by a blank cell (unless the selection contained a blank cell followed by a non-blank cell).

1
2
3
4
5

xxx

You could also terminate your column with a non numeric value.

Jim Dempsey

[jimdempseyatthecove]

Update....

If you select Custom formatting, and use

"0.00E+00 ," or "0.000000E+00 ,"

Note the space preceding the comma, you can get rows too

Jim Dempsey

[Steve_Lionel]

There is a second, much less complex example called, wait for it, Excel. It is in the MixedLanguage folder of the samples bundle. This shows how to call a Fortran DLL from an Excel macro.

[GWats1]

Hi Dr Fortran;

I think it needs to be the other way where my F90 program opens Excel (or some other file). My compiled EXE is a Post-Processor to a commercial program. It runs after the program finishes a pole analysis. At one point I ask the User in a Console window what is the gap between the concrete and the baseplate in numbers of Diameters (like 2.0 or 3.5).  When the user hits Enter, my post-processor does a lot of number crunching and designs several base plates.

Currently, several variables are hard coded so the user cannot change them, so I wanted to allow the user to input the data and I thought maybe Excel might work. It looks too difficult so I'm thinking now that I can write an Excel spreadsheet where the User can input bolt data and a Macro would write out the data as a text file that I can read in my F90 program. 

I attached a sample output that I write out using Fortran_WinPrint to an Adobe PDF. The XML file that I read and parse is around 23Mb. I got lots of help with the XML parse from Arjen Markus.

The following DATA statements from my F90 hard code some real numbers into one dimension arrays but I really only need DIAM, ALLOWBL, FU, and TPI to calculate the other properties of the bolts.

DATA DIAM / 0.5, 0.625, 0.75, 0.875, 1.000, 1.125, 1.250, 1.5, 1.75 , 2.00, 2.25, 2.25, 2.5, 2.75 , 3.00, 3.5, 4.0 /
DATA APBLT / .142 , .226 , .334 , .462 , .606, .763 , .969 , 1.41 ,1.90 ,2.50 ,3.25, 3.25, 4.00, 4.93, 5.97, 8.33, 11.1 /
DATA IBLTS/ .0013 , .0034 , .0076 , .0146 , .0253, .040 , .066, .138 , .25 , .44 ,0.75, .75 , 1.14 , 1.75 , 2.58, 5.10 , 9.13 /
DATA ALLOWBL / 7*60.0, 105.0, 105.0, 105.0, 75.0, 4*105.0, 2*55 /
DATA FU / 7*90.0 , 3*125.0, 100.0, 4*125.0, 2*75 /
DATA TPI / 13.0, 11.0, 10.0, 9.0, 13*8.0 /
DATA BLTWT / 2.5 , 4.1 , 6.4 , 10.1 , 13.5 , 20.8 , 31.2 , 58.3 , 141.3 , 137. ,178., 178. , 225. , 279. , 341. , -10.,-10. /
DATA BSPACE /2.25, 2.25, 2.5, 2.5, 3.0, 3.25, 3.50, 4.25, 5.75, 5.75, 5.75, 5.75, 6.25, 7.0, 7.5, 8.75, 10.0 /

[GWats1]

Hi Dr Fortran;

I thought I typed a long reply with an attached file but do not see it here. My F90 program is a Post-Processor for another commercial program that analyzes steel poles. I looked over the Excel project you reference and it looks like it goes the wrong way for me.  I would need to supply some data to my program so it can design several base plates for the steel poles. Currently, I ask the User for a Gap in a Console window and after the number is input, my program crunches a lot of numbers and spits out several base plates with different bolt diameters.

[mecej4]

It seems to me that you have attempted to use Excel as a text editor. That is a task for which there are far better and simpler, freely available tools: any text editor, such as Notepad++, or even Notepad. No matter which editor you use, for input into Fortran programs it is best to have the editor save the input data into a text file (such as a CSV format file) rather than into a XLSX file.

Readers are likely to understand your descriptions of the data file more easily if you write in terms of rows and columns than bolts and plates.

[Arjen_Markus]

Since the data are organised in a table, an Excel file that has sufficient comments would not be too bad as a user-interface, but I would recommend using a macro (not my forte, I immediately admit!) to save it as a CSV file. You might even simply provide the user with a CSV file and let them chose their own tool to edit the input - as long as they save the result as a CSV file, you can handle it all easy enough.

[GWats1]

mecej4;

The general user of my post-processor would need some explanation of the inputs required (and I hate writing user manuals). Currently the only user input is the Gap between the concrete and baseplate and the program crunches many numbers on a fixed set of bolt properties (diameter, number of threads, yield strength (Fy), and ultimate strength (Fu)).  I wanted to broaden the possible choices, so I thought of Excel which everyone knows and I could have some hints for possible choices. My programming abilities are very minimal (I'm an old Engineer that writes Fortran) so I hoped there might be an easy way to have a spreadsheet where my program says read cell A:12 from Bolts.xlsx and store it as DIAM(1) but evidently that is very complicated (for me anyway). I can probably hard code a filename with a Gap and Number of bolts to read as the first line and a do loop to read the bolt diameters and 3 other fields needed. The User is likely an EE that knows little or nothing about bolts, so selecting values for Fy and Fu will need some hints. 

Arjen;

You helped me with this project 10 years ago to read and parse a huge XML output file and it would never have been possible for me to learn enough Fortran to do it alone.  The current program works well and spits out several base plate designs based on bolts that my company uses. The problem is I have given it to other companies that do not use our bolts with 8 threads per inch, so I wanted to allow the user to pick a different value and different bolt materials (the Fy and Fu). I have attached a screen shot above of possible diameters and materials and attached below is the output from my last Pole run for a very large Transmission Pole. 

Right now, the bolt properties are based on 8 threads per inch and are hard coded in a Data Statement for 17 possible diameters, so I wanted to allow the user to pick different standard sizes. The routine cycles thru the 17 bolt diameters to find those that work and print them out. On some small diameter bolts, the stress is too high so I go to the next size.

Ideally, I might try to use the Sample program GetOpenFileName.F90 to open the user created data file so I can read the data.

Sorry for the long and detailed post, and I'm sure 99% of readers don't want to read about T-Line pole base plates.

[JohnNichols]

You could write a menu to the screen with a list of the choices and they are numbered 1, 2, 3 etc.  

You let them make a choice and then you do the analysis -- you would reflect all the necessary data once they made a choice.  

I do this with my structural analysis program, and store all the properties in an .ini file. 

Rule no 1 keep it simple.  

[Arjen_Markus]

George, I remember :). I thought I recognised the topic!

[GWats1]

Hi Arjen;

It must be late at night in your part of the world. (unless you moved to the USA) 

Yes, I'm digging around in our old project.  I did have to stumble around and recompile when someone used more load cases than I had dimensioned the array for (I'm still stuck using fixed length arrays) and wanted to make the input more general for different bolts. 

I'm not sure what I am worried about. There are about 10 users of the post-processor at my company and they just use the bolts we use. I have given the EXE out a few times so there may be 4 users worldwide outside my company. So the user base is extremely small.

I guess I just like to tinker.      

I attached the main design routine below. It is very old .FOR code from my one Fortran IV course training in 1967. Actually I wrote a pole design program in the 1980's and this was a piece of it that designed a base plate.  I just adapted the routine to be a post-processor to take the pole geometry and loads and design several plates.

[mecej4]

I opened your Excel sheet, and selected the table between cells A-3 and K-20 with the mouse. I copied and pasted that information into a text editor and saved the file as 'bolt.tab'. Then, with the following program, I read that file to check that the fields were being read correctly.

As you can see from the output of this program, the variable values that your old program maintains in DATA statements are now in the tab-separated text file, and can be read in without any fuss. You can add your code to the end of this program to process the data as you did before.

The bolt.tab file is inside the attached Zip file.

program bolt
implicit none
integer, parameter :: NF = 11
character(len=132) :: line
real v(NF)
integer i, n
real, allocatable, dimension(:) :: diam, apblt, iblts, allowbl, fu, &
   tpi, bltwt, bspace, apbcalc, apbwtpi, unc
!
! read file and count lines of data
!
open(unit=11,file='bolt.tab',status='old')
read(11,*) !column titles
n = 0
do
   read(11,*,end=10)v
   n = n+1
   print '(11G10.3)',v
end do
10 print *,n,' data lines'
!
! allocate arrays
!
allocate(diam(n),apblt(n),iblts(n),allowbl(n),fu(n),tpi(n),bltwt(n), &
   bspace(n),apbcalc(n),apbwtpi(n),unc(n))
!
! rewind file and read data into arrays
!
rewind(11)
read(11,*)
read(11,*)(diam(i),apblt(i),iblts(i),allowbl(i),fu(i),tpi(i),bltwt(i), &
   bspace(i),apbcalc(i),apbwtpi(i),unc(i),i=1,n)
!   
close(11)
end program

[GWats1]

mecej4;

Wow, thanks for the work. That old version of my spreadsheet was a work in progress. I do like your idea of a bolts.tab file but I may just use bolts.txt.  My old hard coded numbers were off a bit so I plan to calculate the values that I can in my FOR program.  All I really need is the DIAM, TPI, Fy, and Fu from the User plus the number of bolts to read and the Gap. The BSPACE data was old and I need to rethink how far apart the bolts can be in the concrete to allow 1.5" rock to pass as it flows around the bolts with nuts on the bottom. 

I copied the range A22:D69 to my favorite text editor, Boxer and saved it as a TXT file. ( I used to program with the Brief editor many years ago, but Boxer came along and I've used it since).  I miss the editing part and having to learn the editor in VS 2017, but I rarely do any Fortran programming anymore.  

Anyway, I was going to allow the user to input the number of bolts and the Gap in bolt diameters, then several lines of diameters, TPI's, Fy's and Fu's.  I would read the first line and then skip the 2nd line and use a do loop to read the rest of the lines and go off and calculate the thread area, Moment of Inertia, and some bolt spacing.  I might have used a different Read routine somewhere but I can't remember right now.  Some users may have only a couple of bolt diameters and others may want to try several bolt TPI's and several material strengths.

I may have to add some error checking to see if the TPI matches the DIAM. 

[MWind2]

I found something in c from an old kb article that works with O365 64 bit Excel at https://github.com/jeffpar/kbarchive/blob/master/txt/207/Q207931.TXT.  The c dll works for passing a vba ad() as double where a result is written in the array using SAFEARRAY **. Maybe someone could make dc.txt a fortran dll as I will likely take a long while. 

[GWats1]

Many thanks to those that tried to help with this task of reading data. I gave up and went back to the brute force methods I learned way back. I was able to make the changes and get compiled this morning.  Since the bolt diameters have different spacings, I could not see how to assign the distance without all the IF statements. There is probably a more elegant way but I could not think of it.   

SUBROUTINE PLATE (MY,MX,VLOAD,VX,VY,AF,THK,NSIDES,orient)
C
C THIS SUBROUTINE DESIGNS THE BASE PLATE AND ANCHOR BOLTS
C
REAL IBLT, IXX, IYY, MX, MY, IBLTS, gapmax, TPI
INTEGER NUMBLT
character (len=1) :: orient
CHARACTER DUMMY
DIMENSION POS(2,400),SIGX(400),SIGY(400),BSTRS(400),BLOAD(400),
+ DIS(400),PLM(400),DIAM(50),APBLT(50),IBLTS(50),ALLOWBL(50),FU(50)
+ , TPI(50),REDIA(50),BSPACE(50),ANGLE(400),SPACE(17),
+ RESB(50,10), RESNB(50,10),plmom(50),thik48(50) ! ADDED THESE 2 ARRAYS TO HOLD RESULTS

DATA SPACE /3.75, 3.75, 4.00, 4.25, 4.50, 4.75, 5.00 ,5.25, 5.50, ! ADDED THIS VARIABLE FOR BOLT SPACING
+ 6.00, 6.50, 6.75, 7.25, 7.75, 8.25, 8.75, 9.00 /

RDIS(A,B)=SQRT(A*A + B*B)

PI=4.*ATAN(1.0)

open( 88, file = 'C:\PLS\TEMP\ABOLTS.TXT' )
READ(88,*) NUMBLT     
READ(88,*) DUMMY
DO I=1,NUMBLT
READ(88,*) DIAM(I), TPI(I), ALLOWBL(I), FU(I)

APBLT(I) = (PI/4.0)*(DIAM(I)-0.9743/TPI(I))**2 ! THE BOLT AREA IN THE THREADS
REDIA(I) = SQRT((4.0*APBLT(I))/PI) ! THIS IS THE REDUCED DIAMETER
IBLTS(I) = PI*((REDIA(I)**4)/64.0) ! MOMENT OF INERTIA IN THE THREADS
C SET THE BOLT SPACING TO ALLOW 1.5" ROCK TO PASS NUT IN CONCRETE
IF(DIAM(I).LE.1.001) BSPACE(I) = SPACE(1)
IF(DIAM(I).EQ.1.125) BSPACE(I) = SPACE(2)
IF(DIAM(I).EQ.1.25 ) BSPACE(I) = SPACE(3)
IF(DIAM(I).EQ.1.375) BSPACE(I) = SPACE(4)
IF(DIAM(I).EQ.1.5 ) BSPACE(I) = SPACE(5)
IF(DIAM(I).EQ.1.625) BSPACE(I) = SPACE(6)
IF(DIAM(I).EQ.1.75 ) BSPACE(I) = SPACE(7)
IF(DIAM(I).EQ.1.875) BSPACE(I) = SPACE(8)
IF(DIAM(I).EQ.2.0) BSPACE(I) = SPACE(9)
IF(DIAM(I).EQ.2.25) BSPACE(I) = SPACE(10)
IF(DIAM(I).EQ.2.5) BSPACE(I) = SPACE(11)
IF(DIAM(I).EQ.2.75) BSPACE(I) = SPACE(12)
IF(DIAM(I).EQ.3.00) BSPACE(I) = SPACE(13)
IF(DIAM(I).EQ.3.25) BSPACE(I) = SPACE(14)
IF(DIAM(I).EQ.3.50) BSPACE(I) = SPACE(15)
IF(DIAM(I).EQ.3.75) BSPACE(I) = SPACE(16)
IF(DIAM(I).EQ.4.00) BSPACE(I) = SPACE(17)

ENDDO

DO I=1,NUMBLT
IF(BSPACE(I) .LE. 0.01) THEN
BSPACE(I) = 12.0
WRITE(10,808) I, DIAM(I)
WRITE(*,808) I, DIAM(I)
ENDIF

ENDDO

808 FORMAT(2X,'BOLT NUMBER',I3,' DIAMETER ',F8.3,1X,
+ 'IS NOT RECOGNIZED, CHECK YOUR DATA INPUT'////)

[mecej4]

Instead of coding 17 IF statements with the same pattern, you can do the following:

real :: dia(17) = [1.001, 1.125,...,3.75,4.0]
...
do j = 1, 17
   if(diam(i) <= dia(j))then
      bspace(i) = space(j)
      exit
   endif
end do

(You need to type in all the diameters that I left out when I typed "..." in the initializer. )

Note that when the comparison succeeds, we can skip all subsequent comparisons, with the EXIT statement.

[GWats1]

Thanks for the tips but it has an error if I input a diameter that is not on my list. If they all match, it works fine. 

I had used the variable "dia" later on so I changed it to listdia(17)

 real :: listdia(17) = [ 1.000, 1.125, 1.250, 1.375, 1.500, 1.625, 
     + 1.750, 1.875, 2.000, 2.250, 2.500, 2.750, 3.000, 3.250, 3.500,
     +   3.750, 4.000 ]

In the code piece below in the do loop starting on line 45, I check to see if any of the bspace(i) values did not get assigned and were left as 0.0.  I note the bolt number and the offending diameter and write a note in the output and the input window so the user knows there was an input error. The bolt properties are correct for the oddball diameter, but the diameter is not available so I put in a very big spacing of 12 inches and continue the program.

When I run your code, the output window goes crazy with errors.  I played with it and changed your "<= " to .eq. and that seemed to work and give me the error trap that I wanted.

open( 88, file = 'C:\PLS\TEMP\ABOLTS.TXT' )
       READ(88,*) NUMBLT                ! ADD GAPMAX TO THE READ IF IT IS NEEDED , gapmax
       READ(88,*) DUMMY
       DO I=1,NUMBLT
           READ(88,*) DIAM(I), TPI(I), ALLOWBL(I), FU(I)
           
           APBLT(I) = (PI/4.0)*(DIAM(I)-0.9743/TPI(I))**2   ! THE BOLT AREA IN THE THREADS
           REDIA(I) = SQRT((4.0*APBLT(I))/PI)  ! THIS IS THE REDUCED DIAMETER
           IBLTS(I) = PI*((REDIA(I)**4)/64.0)   ! MOMENT OF INERTIA IN THE THREADS
           
           do j=1,17  ! try this code from mecej4
               if(diam(i) <= listdia(j)) then
                   bspace(i) = space(j)
                   exit
               endif
           end do
           
           
           goto 848 
C   SET THE BOLT SPACING TO ALLOW 1.5" ROCK TO PASS NUT IN CONCRETE
           IF(DIAM(I).LE.1.001) BSPACE(I) = SPACE(1)
           IF(DIAM(I).EQ.1.125) BSPACE(I) = SPACE(2)
           IF(DIAM(I).EQ.1.25 ) BSPACE(I) = SPACE(3)
           IF(DIAM(I).EQ.1.375) BSPACE(I) = SPACE(4)
           IF(DIAM(I).EQ.1.5  ) BSPACE(I) = SPACE(5)
           IF(DIAM(I).EQ.1.625) BSPACE(I) = SPACE(6)
           IF(DIAM(I).EQ.1.75 ) BSPACE(I) = SPACE(7)
           IF(DIAM(I).EQ.1.875) BSPACE(I) = SPACE(8)           
           IF(DIAM(I).EQ.2.0)   BSPACE(I) = SPACE(9)
           IF(DIAM(I).EQ.2.25)  BSPACE(I) = SPACE(10)
           IF(DIAM(I).EQ.2.5)   BSPACE(I) = SPACE(11)
           IF(DIAM(I).EQ.2.75)  BSPACE(I) = SPACE(12)
           IF(DIAM(I).EQ.3.00)  BSPACE(I) = SPACE(13)
           IF(DIAM(I).EQ.3.25)  BSPACE(I) = SPACE(14)
           IF(DIAM(I).EQ.3.50)  BSPACE(I) = SPACE(15)
           IF(DIAM(I).EQ.3.75)  BSPACE(I) = SPACE(16)
           IF(DIAM(I).EQ.4.00)  BSPACE(I) = SPACE(17)
 848      continue 
           
C           WRITE(*,*) DIAM(I), TPI(I), ALLOWBL(I), FU(I), APBLT(I),
C     +     REDIA(I) , IBLTS(I), BSPACE(I)
          
       ENDDO
       
       DO  I=1,NUMBLT
           IF(BSPACE(I) .LE. 0.01) THEN
               BSPACE(I) = 12.0
               WRITE(10,808) I, DIAM(I)
               WRITE(*,808) I, DIAM(I)
           ENDIF
           
      ENDDO
           
808         FORMAT(2X,'BOLT NUMBER',I3,' DIAMETER ',F8.3,1X,
     +     'IS NOT RECOGNIZED, CHECK YOUR DATA INPUT'////)
       

 Another silly VS2017 editor question:

When I have a very long line in my .FOR code like the real declaration with the 17 diameters, is there a way to hit a CR and get the editor to Format it correctly?  There is a gray box that shows up when I get close to the magic limit and I usually just manually put a + sign in column 6 like I was punching cards years ago.  So is there a continuation character I can put just before the gray box?

BTW, I did figure out how you insert code in this Forum.