Tags: amt, contains, database, excel, fields, foxpro, grpcod, microsoft, mysql, oracle, prdcod, sapcode, sapname, sheet, sheets, sir, sql, visual

visual foxpro to excel

On Database » Microsoft FoxPro

5,943 words with 5 Comments; publish: Wed, 09 Jan 2008 01:58:00 GMT; (25062.50, « »)

Sir,

I have 2 excel sheets.

one sheet contains sapcode,sapname,grpcod,prdcod,amt

another sheet contains sapcode,so many other fields and amt

both sheet contains multiple sapcodes

now my job is to

1. read first sheet into a dbf (sum(amt) group by sapcode because multiple sapcodes)

2. read second sheet into a dbf (sum(amt) group by sapcode because multiple sapcodes)

3. match two tables based on sapcode and amt and put thoses sapcodes which do not match with the amt

in third sheet.

if i have 2 tables then it is easy

i will do a set relation

i..e.

sele a

use tableone

index on sapcode tag sapcode additive

sele b

use tabletwo

index on sapcode tag sapcode additive

set relation to sapcode into tableone

repl all tabletwo.tableoneamt with tableone.amt for tabletwo.sapcode = tableone.sapcode

copy to table3 for tableoneamt != tabletwoamt

and i will find which ever sapcode not exist in both the files and append it to table3

and say

export to table3.xls type xls

but how do i convert excel sheets into dbf automatically and how to output automatically in 3rd sheet.

if u can give me the code, i will be greatful to you.

I am uisng VFP 9.0 Service Pack 1 and Office 2007.

i can also use Office XP. (if foxpro does not support Office 2007).

Also another question is why in excel 2007 save as does not support dbf. i.e. u can't save a excel sheet as a dbf. you can save to access but not to foxpro. why not possible but u can open a fox table in excel 2007.

Thanks and Regards

All Comments

Leave a comment...

  • 5 Comments

    • Lakshmi,

      You can read Excel 2007 sheets using ODBC and OLEDB. Or you can read them using automation. You can create the 3rd sheet and put data in it using automation. ie:

      lcXLS = GETFILE('XLSX')

      TEXT TO m.lcConn NOSHOW PRETEXT 15 TEXTMERGE

      DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);

      UID=admin;

      FIL=excel 12.0;

      DriverId=1046;

      DefaultDir=<<JustPath(m.lcXLS)>>;

      DBQ=<<m.lcXLS>>

      ENDTEXT

      lnHandle = SQLStringConnect(m.lcConn)

      SQLTables(m.lnHandle,"", "SheetNames")

      Scan

      lcTableName = Trim(SheetNames.Table_Name)

      lcOutput = Chrtran(m.lcTableName,' $','_')

      SQLExec(lnHandle,'select * from ['+m.lcTableName+']',m.lcOutput )

      endscan

      SQLDisconnect(lnHandle)

      For OLEDB connectionstring is like:

      lcADoConn = 'Provider=Microsoft.ACE.OLEDB.12.0;'+;

      'Data Source='+m.lcMyExcelFile+';'+;

      'Extended Properties="Excel 12.0;HDR=YES";'
      #2; Tue, 02 Oct 2007 17:45:00 GMT

    • You may use excel file with 1 sheet per file, and just:

      APPEND FROM <FILENAME> XLS

      to export in xls you may use

      COPY TO <FILENAME> XLS.

      The last format recognized by vfp9 is, as I know, Excel 5 (Office 2003).

      #3; Tue, 02 Oct 2007 17:46:00 GMT

    • Remember that you could also use "import from" to create a new database from the xls spreadsheet, however, if the spreadsheet has column headings that are text every item imported in that column will be text. It uses the datatype of columns in the the first row to define the field types.

      #5; Tue, 02 Oct 2007 17:48:00 GMT