SQL writing to Excel

Hi,

I have successfully used F90SQL s/w to write x/y data to a Excel spreadsheet using their example code, trouble is when you re-run even after deleting the previous x/y columns it keeps incrementing down the page even if I close the file it still somehow keeps a pointer row record to where it last wrote...any ideas how I can get it to overwrite previous data??

You've done better than me if you have something working. My research of available info indicated that you can only write new data, so what you are seeing, as not being able to overwrite previous cells, may be the case.

Have you been able to read information from an existing spreadsheet ?

John

I got the Excel read example to work too...

I did also get the Excel Update example to work which did overwrite everytime but it would only allow limited 23/24 rows of data as peer the the example program then it threw up error reading records etc... weird..

I guess if I could find the key difference in the Excel write and update I could modify Excel write and be home and dry !!!

this isn't really an answer to your question but...

I have found that using ODBC/OLEDB solutions with Excel to be a bit flaky to say the least.

This is independent of the programming languages I have used a lot in C#, Fortran, SQL Server. The Issues mostly stem from the fact that Excel is a spread sheet and not a relational database. If one wants to overwrite in SQL ones uses an update query not an insert query.

What I have found generally to be the case is that Excel based solutions can be much more efficient, and easy to code. In your problem write an excel VBA subroutine that fills the spreadsheet with data passed as a parameter.

Then have this subroutine in a template excel file that get copied by the fortran code to the output location from some fixed location then call the the function using excel automation.

There is a bit of faff with the arguments etc (and closing excel when you are finished). but generally there is a much finer degree of control using this type of solution, and your not bound to pretending that excel is a database, the syntax will be a lot simpler also.

If your like I will see if I can bodge something together to demonstrate. What version of excel are your fighting with?

Carl

Hi Dr Tip,

Excel 2003 - I have decided to write out my Fortran to a Excel derived CSV file, after all I am reading data in from CSV files, a colleague is going to supply a macro that will couple this CSV output data to a prepared *.XLS graphics template worksheet I have developed...simple really I guess.

Interested in you idea though.

Have you come up with any ideas for the fortran/excel data handshake yet?

Once you have written and closed your csv file, call 'use_url@(...)' with either the filename of the csv file or of the Excel file which contains a macro to process the csv. Ian

BEEFHEART

I am struggling on the include files one would need. To do my suggestion, I have done what I suggested in C# lots of example on the net how to do this. but I have to get upto speed with my ne job so I will put this on the back burner for now.

your way sounds good, also opens up the possibility of calling your fortran dll from excel, which is easier than the other way round.

Carl

I've also struggled with going the next step and using excel for input and output from my fortran programs.

At present: I develop my data in excel, then save the data to a fixed space .prn file I then run my fortran program, using the .prn file and then write output to a .csv file. (It is relatively easy to write a tab or comma delimited output.) I can then open the .csv file with excel and further process the output. This achieves a lot of what is wanted.

The main problem I have with this approach is there are two data definition files, a .xls and a .prn. If I come back to a project after some time, then I'm never always sure if the .prn file is the same as the .xls definition. If I could read direct from the .xls then there would not be this duplicate data definition. Skiping the .csv and sending output direct to charts would also be a time saver.

I find the interface with .xls files is so difficult in fortran. .xlsx files are also very useful for larger data sets.

John

Well john as I indicated earlier I have had some success writing direct to an xls file from fortran using f90SQL (codes supplied by them, in their examples directory).

I managed to write x,y1,y2,y3 arrays to a named xls file, trouble is it keeps counting down the file evertime you do it even if you delete and/or close it etc.

The update example does overwrite but seems to have limited records 23/34 rows...tried to extend the rows and also looked at combining both codes but failed...frustrating, sql is a weird code !!!

You wrote: Once you have written and closed your csv file, call 'use_url@(...)'

Where do you suggest i put this call command???

Try this type of thing:

      character*256 csvfile
      csvfile='mycsv.csv'
      open(unit=10,csvfile,status='unknown')
      write(10,1000)
1000 format(''Hello there',1.0,2.5,3.1415926')
      close(unit=10)
      call use_url@(csvfile)
      end

Is the code as listed above compilable??? Because I get this error...

'.....IN LINE 3....in the open file line it states error 529 non key word specifiers must appear before keyword specifiers....'

sorry, missed 'file='. it should have been:

      character*256 csvfile
      csvfile='mycsv.csv'
      open(unit=10,file=csvfile,status='unknown')
      write(10,1000)
1000 format(''Hello there',1.0,2.5,3.1415926')
      close(unit=10)
      call use_url@(csvfile)
      end 

Ok but can I be clear what the call command is doing are you suggesting the'url' should be a filepath etc...if so can you be more specific?

Also you are opening a file to which you assign a dummy name you write to it and then call it....sorry am I being dumb here can you explain further?

I expect the use_url@ is saying 'Open this file with the default process for a .csv file'. The same result would occur if you typed the name in a dos box or double clicked the name in windows explorer. If, in explorer, you change the default process to open a .csv file, say from excel to notepad, then you would get a different result with the use_url@ call. You can set up the default response for other file name extensions, including for .f95 to be ftn95.exe, rather than plato.

John

Absolutely spot on John. And if you specify a file with a .txt extension, then Notpad will open it, .doc for Word and .pdf for acrobat. This is an easy way of providing a help file, simply use Word, print to a pdf and then use_url@ with the pdf name. It probably opens your default web browser for a .htm or .html etc.

I quite often write a .csv files and then 'use_url@' an Excel file with a macro that executes on opening, to load the csv and process the data to new Excel files.

Regards Ian

The code you supplied if run precisely as you wrote it gives the following:

Runtime error from program:c:\documents and settings\ctroth1\my documents\fortran program\eva\test3.exe Run-time Error *** Error 29, Call to missing routine : _USE_URL# at 0x004010e3.

main - in file test3.f95 at line 7 [+00e8] [recur= 1]

You need the interface to the subprogram, which is in Clearwin.ins (or the corresponding module). Other posters have assumed, perhaps, that you would use the line:

      INCLUDE <WINDOWS.INS>

(or CLEARWIN.INS on its own; WINDOWS.INS incorporates CLEARWIN.INS) to make sure that you have all the interfaces and definitions included.

My personal test is to look for any subprogram that ends in @, which makes it a 'Silverfrost special'. If there is one of those in the code, there is a good chance you need one or other of the INCLUDE files, and WINDOWS.INS is the one that hits most spots. There are several such 'Incantations' that conjure up the real Silverfrost magic ...

Eddie

You got me again! - insert as the top line:

      include <windows.ins>

Regards Ian