>Hi All,
>
>Is there an article or examples of how to extract data from an excel spreadsheet into VFP table? or/and SQL Server table? TIA
Mathew,
Excel data to VFP and SQL server, I think there wouldn't be a single article covering all of those. However there are many posts here on UT how to do that under different scenarios.
Doing that mostly dependant how your data is laid in excel.
-Import command. Simpliest one to import a sheet into VFP. Disadvantage if data has a header row datatypes get screwed.
-Automation. This one is one of the most powerfull ways as you control where to get data and how to get it. You could directly save the spreadsheet as a dBaseIII (fox2x) type table.
-OLEDB, ODBC. Yet another way to get the data if your spreadsheet have an Excel 'database' (header row followed by data). This one is powerfull as using an OLEDB or ODBC connection you could directly get Excel database into VFP cursor and/or directly to SQL server (and even it's an updatable remote cursor).
Below sample shows the last option roughly. What it does is :
-Use customer table
-Copy to XLS
-Open XLS from SQL server and get back to a VFP cursor
USE customer
Copy To customer.xls type xl5
use
lnHandle=SQLStringConnect('DRIVER=SQL Server;'+;
'SERVER=servername;Trusted_connection=Yes')
Text to lcSQL textmerge noshow
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="<<Sys(5)+Curdir()+[customer.xls]>>";
User ID=Admin;Password=;Extended properties=Excel 5.0')...customer$
EndText
SQLExec(lnHandle, lcSQL,'myExcel')
SQLDisconnect(lnHandle)
browse
Cetin