| View previous topic :: View next topic |
| Author |
Message |
colt1954
Joined: 21 Dec 2010
Posts: 81
|
 Posted: Mon Jan 10, 2011 3:26 pm Post subject: 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?? |
|
| Back to top |
|
 |
JohnCampbell
Joined: 16 Feb 2006
Posts: 2554
Location: Sydney
|
| Posted: Tue Jan 11, 2011 2:32 am Post subject: |
|
|
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 |
|
| Back to top |
|
|
colt1954
Joined: 21 Dec 2010
Posts: 81
|
| Posted: Tue Jan 11, 2011 12:46 pm Post subject: Yes |
|
|
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 !!! |
|
| Back to top |
|
|
DrTip
Joined: 01 Aug 2006
Posts: 74
Location: Manchester
|
| Posted: Tue Jan 11, 2011 10:39 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
colt1954
Joined: 21 Dec 2010
Posts: 81
|
| Posted: Wed Jan 12, 2011 5:24 pm Post subject: Hi |
|
|
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. |
|
| Back to top |
|
|
colt1954
Joined: 21 Dec 2010
Posts: 81
|
| Posted: Sat Jan 15, 2011 2:51 pm Post subject: Hi Dr Tip |
|
|
| Have you come up with any ideas for the fortran/excel data handshake yet? |
|
| Back to top |
|
|
IanLambley
Joined: 17 Dec 2006
Posts: 490
Location: Sunderland
|
| Posted: Sat Jan 15, 2011 5:33 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
DrTip
Joined: 01 Aug 2006
Posts: 74
Location: Manchester
|
| Posted: Sun Jan 16, 2011 3:01 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006
Posts: 2554
Location: Sydney
|
| Posted: Mon Jan 17, 2011 12:17 am Post subject: |
|
|
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 |
|
| Back to top |
|
|
colt1954
Joined: 21 Dec 2010
Posts: 81
|
| Posted: Mon Jan 17, 2011 5:07 pm Post subject: Join the club 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 !!! |
|
| Back to top |
|
|
colt1954
Joined: 21 Dec 2010
Posts: 81
|
| Posted: Mon Jan 17, 2011 5:08 pm Post subject: Hi Ian |
|
|
You wrote:
Once you have written and closed your csv file, call "use_url@(...)"
Where do you suggest i put this call command??? |
|
| Back to top |
|
|
IanLambley
Joined: 17 Dec 2006
Posts: 490
Location: Sunderland
|
| Posted: Tue Jan 18, 2011 4:03 am Post subject: |
|
|
Try this type of thing:
| Code: |
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
|
|
|
| Back to top |
|
|
colt1954
Joined: 21 Dec 2010
Posts: 81
|
| Posted: Tue Jan 18, 2011 2:47 pm Post subject: Not working Ian |
|
|
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....' |
|
| Back to top |
|
|
IanLambley
Joined: 17 Dec 2006
Posts: 490
Location: Sunderland
|
| Posted: Wed Jan 19, 2011 11:40 pm Post subject: |
|
|
sorry, missed "file=". it should have been:
| Code: |
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
|
|
|
| Back to top |
|
|
colt1954
Joined: 21 Dec 2010
Posts: 81
|
| Posted: Thu Jan 20, 2011 11:08 am Post subject: OK |
|
|
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? |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
