|
| Using xls files in dBase dbfs |
 |
Mon, 31 Mar 2008 21:55:03 -050 |
|
| Post Reply
|
| Re: Using xls files in dBase dbfs |
 |
Mon, 31 Mar 2008 22:43:54 -050 |
Excellent! I am going to apply it to two different spreadsheets with differing
header/field names to see how they come into two tables. Thank you much for
sharing this code and responding so quickly. I grew from III to 7.01 - and
dusting it off after a few years hiatus... for a work project... Will let you
know how this works out!
Jean-Pierre Martel Wrote:
> In article <E5h5IP6kIHA.1836@news-server>, donnyk@att.net says...
> > I would like to read Excel file data into a table via
> > a form application.
>
> Hereunder is the copy of a message written by Ken Mayer on that subject.
> Watch for word wrap.
>
> Jean-Pierre Martel, editor
> The dBASE Developers Bulletin
> Blue Star dBASE Plus Core Concepts Graduate
> ===========================================
> Subject: Re: Importing Excel into dBASE Plus
> From: "Ken Mayer [dBASE, Inc.]" <kmayer@dbase.com>
> Date: Mon, 24 Feb 2003 12:42:54 -0800
> Newsgroups: dbase.getting-started
>
> "Michael Nuwer" <nuwermj@econ.potsdam.edu> wrote:
> > I've done a little work on a more generic routine. I haven't added
any
> > error checks and the code works only with columns A through Z.
> > Nevertheless, if any of it is useful, you're welcome to it.
>
> Take a look at this -- not fully tested, but it does seem to be working.
> I haven't added UI or course, but I'm just trying to be sure my logic
> works ...:
>
> // Michael Nuwer's attempt at a generic routine
> // to import data from an Excel spreadsheet:
> clear
> try
> oExcel = new oleAutoclient("Excel.Application")
> cFileName = "c:\dbasetests\testforimport.xls"
> oExcel.workbooks.open(cFileName)
> catch( Exception E )
> ? e.code, e.message
> ? "Problem opening spreadsheet"
> return
> endtry
>
> if file("test.dbf")
> drop table test
> endif
> create table Test (Field1 char(20),;
> Field2 Numeric(4,0),;
> Field3 Numeric(4,0),;
> Field4 Numeric(4,0),;
> Field5 Numeric(4,0),;
> Field6 Numeric(4,0),;
> Field7 Numeric(4,0),;
> Field8 Numeric(4,0),;
> Field9 Numeric(4,0),;
> Field10 Numeric(4,0) )
>
> Q = new QUERY()
> with (Q)
> sql = 'select * from "test.DBF"'
> active = true
> endwith
>
> // The user defines the Excel range
> // they wish to import
> cRange = "A3:J5"
>
> // Parse the range into four coordinates
> cStartRange = left(cRange,at(":",cRange)-1)
> cStartCol = ""
> cStartRow = ""
> for i=1 to len(cStartRange)
> if isAlpha(substr(cStartRange,i,1))
> cStartCol += substr(cStartRange,i,1)
> else
> cStartRow += substr(cStartRange,i,1)
> endif
> next
>
>
> cEndRange = substr(cRange,at(":",cRange)+1)
> cEndCol = ""
> cEndRow = ""
> for i=1 to len(cEndRange)
> if isAlpha(substr(cEndRange,i,1))
> cEndCol += substr(cEndRange,i,1)
> else
> cEndRow += substr(cEndRange,i,1)
> endif
> next
>
>
> // build an array of the columns to
> // import. LIMITATION: Col "AA" etc
> // are not handled.
> aCols = new array()
> //cCols = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
> cCols = "A |B |C |D |E |F |G |H |I |J |K |L |M |N |O |P |Q |R |S |T
|U
> |V |W |X |Y |Z |"+;
>
>
"AA|AB|AC|AD|AE|AF|AG|AH|AI|AJ|AK|AL|AM|AN|AO|AP|AQ|AR|AS|AT|AU|AV|AW|AX
> |AY|AZ|"+;
>
>
"BA|BB|BC|BD|BE|BF|BG|BH|BI|BJ|BK|BL|BM|BN|BO|BP|BQ|BR|BS|BT|BU|BV|BW|BX
> |BY|BZ|"+;
>
>
"CA|CB|CC|CD|CE|CF|CG|CH|CI|CJ|CK|CL|CM|CN|CO|CP|CQ|CR|CS|CT|CU|CV|CW|CX
> |CY|CZ|"+;
>
>
"DA|DB|DC|DD|DE|DF|DG|DH|DI|DJ|DK|DL|DM|DN|DO|DP|DQ|DR|DS|DT|DU|DV|DW|DX
> |DY|DZ|"+;
>
>
"EA|EB|EC|ED|EE|EF|EG|EH|EI|EJ|EK|EL|EM|EN|EO|EP|EQ|ER|ES|ET|EU|EV|EW|EX
> |EY|EZ|"+;
>
>
"FA|FB|FC|FD|FE|FF|FG|FH|FI|FJ|FK|FL|FM|FN|FO|FP|FQ|FR|FS|FT|FU|FV|FW|FX
> |FY|FZ|"+;
>
>
"GA|GB|GC|GD|GE|GF|GG|GH|GI|GJ|GK|GL|GM|GN|GO|GP|GQ|GR|GS|GT|GU|GV|GW|GX
> |GY|GZ|"+;
>
>
"HA|HB|HC|HD|HE|HF|HG|HH|HI|HJ|HK|HL|HM|HN|HO|HP|HQ|HR|HS|HT|HU|HV|HW|HX
> |HY|HZ|"+;
>
>
"IA|IB|IC|ID|IE|IF|IG|IH|II|IJ|IK|IL|IM|IN|IO|IP|IQ|IR|IS|IT|IU|"
> nPos = 1
> do
> if substr(cCols,nPos,2) => cStartCol and ;
> substr(cCols,nPos,2) =< cEndCol
> aCols.add(substr(cCols,nPos,2))
> endif
> nPos+=3
> until substr(cCols,nPos,2) > cEndCol
>
> // Append the data from the spreadsheet
> // into the table.
> for nRow=val(cStartRow) to val(cEndRow)
> q.rowset.beginAppend()
> for nCol=1 to aCols.size
> cRange = trim(aCols[nCol])+ltrim(str(nRow))
> xValue = oExcel.Range( cRange ).value
> q.rowset.fields[nCol].value = xValue
> next
> next
> q.rowset.save()
> oExcel.workbooks.close()
> q.active = false
> oExcel = null
> q = null
>
> Ken Mayer [dBASE, Inc.]
|
| Post Reply
|
| Re: Using xls files in dBase dbfs |
 |
Mon, 31 Mar 2008 22:59:57 -040 |
In article <E5h5IP6kIHA.1836@news-server>, donnyk@att.net says...
> I would like to read Excel file data into a table via
> a form application.
Hereunder is the copy of a message written by Ken Mayer on that subject.
Watch for word wrap.
Jean-Pierre Martel, editor
The dBASE Developers Bulletin
Blue Star dBASE Plus Core Concepts Graduate
===========================================
Subject: Re: Importing Excel into dBASE Plus
From: "Ken Mayer [dBASE, Inc.]" <kmayer@dbase.com>
Date: Mon, 24 Feb 2003 12:42:54 -0800
Newsgroups: dbase.getting-started
"Michael Nuwer" <nuwermj@econ.potsdam.edu> wrote:
> I've done a little work on a more generic routine. I haven't added any
> error checks and the code works only with columns A through Z.
> Nevertheless, if any of it is useful, you're welcome to it.
Take a look at this -- not fully tested, but it does seem to be working.
I haven't added UI or course, but I'm just trying to be sure my logic
works ...:
// Michael Nuwer's attempt at a generic routine
// to import data from an Excel spreadsheet:
clear
try
oExcel = new oleAutoclient("Excel.Application")
cFileName = "c:\dbasetests\testforimport.xls"
oExcel.workbooks.open(cFileName)
catch( Exception E )
? e.code, e.message
? "Problem opening spreadsheet"
return
endtry
if file("test.dbf")
drop table test
endif
create table Test (Field1 char(20),;
Field2 Numeric(4,0),;
Field3 Numeric(4,0),;
Field4 Numeric(4,0),;
Field5 Numeric(4,0),;
Field6 Numeric(4,0),;
Field7 Numeric(4,0),;
Field8 Numeric(4,0),;
Field9 Numeric(4,0),;
Field10 Numeric(4,0) )
Q = new QUERY()
with (Q)
sql = 'select * from "test.DBF"'
active = true
endwith
// The user defines the Excel range
// they wish to import
cRange = "A3:J5"
// Parse the range into four coordinates
cStartRange = left(cRange,at(":",cRange)-1)
cStartCol = ""
cStartRow = ""
for i=1 to len(cStartRange)
if isAlpha(substr(cStartRange,i,1))
cStartCol += substr(cStartRange,i,1)
else
cStartRow += substr(cStartRange,i,1)
endif
next
cEndRange = substr(cRange,at(":",cRange)+1)
cEndCol = ""
cEndRow = ""
for i=1 to len(cEndRange)
if isAlpha(substr(cEndRange,i,1))
cEndCol += substr(cEndRange,i,1)
else
cEndRow += substr(cEndRange,i,1)
endif
next
// build an array of the columns to
// import. LIMITATION: Col "AA" etc
// are not handled.
aCols = new array()
//cCols = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
cCols = "A |B |C |D |E |F |G |H |I |J |K |L |M |N |O |P |Q |R |S |T |U
|V |W |X |Y |Z |"+;
"AA|AB|AC|AD|AE|AF|AG|AH|AI|AJ|AK|AL|AM|AN|AO|AP|AQ|AR|AS|AT|AU|AV|AW|AX
|AY|AZ|"+;
"BA|BB|BC|BD|BE|BF|BG|BH|BI|BJ|BK|BL|BM|BN|BO|BP|BQ|BR|BS|BT|BU|BV|BW|BX
|BY|BZ|"+;
"CA|CB|CC|CD|CE|CF|CG|CH|CI|CJ|CK|CL|CM|CN|CO|CP|CQ|CR|CS|CT|CU|CV|CW|CX
|CY|CZ|"+;
"DA|DB|DC|DD|DE|DF|DG|DH|DI|DJ|DK|DL|DM|DN|DO|DP|DQ|DR|DS|DT|DU|DV|DW|DX
|DY|DZ|"+;
"EA|EB|EC|ED|EE|EF|EG|EH|EI|EJ|EK|EL|EM|EN|EO|EP|EQ|ER|ES|ET|EU|EV|EW|EX
|EY|EZ|"+;
"FA|FB|FC|FD|FE|FF|FG|FH|FI|FJ|FK|FL|FM|FN|FO|FP|FQ|FR|FS|FT|FU|FV|FW|FX
|FY|FZ|"+;
"GA|GB|GC|GD|GE|GF|GG|GH|GI|GJ|GK|GL|GM|GN|GO|GP|GQ|GR|GS|GT|GU|GV|GW|GX
|GY|GZ|"+;
"HA|HB|HC|HD|HE|HF|HG|HH|HI|HJ|HK|HL|HM|HN|HO|HP|HQ|HR|HS|HT|HU|HV|HW|HX
|HY|HZ|"+;
"IA|IB|IC|ID|IE|IF|IG|IH|II|IJ|IK|IL|IM|IN|IO|IP|IQ|IR|IS|IT|IU|"
nPos = 1
do
if substr(cCols,nPos,2) => cStartCol and ;
substr(cCols,nPos,2) =< cEndCol
aCols.add(substr(cCols,nPos,2))
endif
nPos+=3
until substr(cCols,nPos,2) > cEndCol
// Append the data from the spreadsheet
// into the table.
for nRow=val(cStartRow) to val(cEndRow)
q.rowset.beginAppend()
for nCol=1 to aCols.size
cRange = trim(aCols[nCol])+ltrim(str(nRow))
xValue = oExcel.Range( cRange ).value
q.rowset.fields[nCol].value = xValue
next
next
q.rowset.save()
oExcel.workbooks.close()
q.active = false
oExcel = null
q = null
|
| Post Reply
|
| Re: Using xls files in dBase dbfs |
 |
Tue, 1 Apr 2008 07:42:50 -0400 |
In article <Wz53bq6kIHA.1688@news-server>, donnyk@att.net says...
> Thank you much for sharing this code and responding
> so quickly. I grew from III to 7.01 - and dusting it
> off after a few years hiatus...
You're welcome. Good luck.
Jean-Pierre Martel, editor
The dBASE Developers Bulletin
|
| Post Reply
|
| Re: Using xls files in dBase dbfs |
 |
Wed, 16 Apr 2008 00:11:15 -040 |
I modified the below code to the file names I have in the project folder to
include the table creation for the new 26 field names and types. I know the app
is hitting the .xls because on one run, it locked the spreadsheet...
I keep getting a 359 error: OLE dispatch exception: ARM5test.xls cannot be
found. Check the spelling of the... Problem opening the spreadsheet.
The xls is saved in Office 97-2003 format... columns are A-Z... and the dbf
creates just fine in the running app... I also use the Set Path To function at
the beginning of the code - to be sure...
Any ideas out there?
Jean-Pierre Martel Wrote:
> In article <E5h5IP6kIHA.1836@news-server>, donnyk@att.net says...
> > I would like to read Excel file data into a table via
> > a form application.
>
> Hereunder is the copy of a message written by Ken Mayer on that subject.
> Watch for word wrap.
>
> Jean-Pierre Martel, editor
> The dBASE Developers Bulletin
> Blue Star dBASE Plus Core Concepts Graduate
> ===========================================
> Subject: Re: Importing Excel into dBASE Plus
> From: "Ken Mayer [dBASE, Inc.]" <kmayer@dbase.com>
> Date: Mon, 24 Feb 2003 12:42:54 -0800
> Newsgroups: dbase.getting-started
>
> "Michael Nuwer" <nuwermj@econ.potsdam.edu> wrote:
> > I've done a little work on a more generic routine. I haven't added
any
> > error checks and the code works only with columns A through Z.
> > Nevertheless, if any of it is useful, you're welcome to it.
>
> Take a look at this -- not fully tested, but it does seem to be working.
> I haven't added UI or course, but I'm just trying to be sure my logic
> works ...:
>
> // Michael Nuwer's attempt at a generic routine
> // to import data from an Excel spreadsheet:
> clear
> try
> oExcel = new oleAutoclient("Excel.Application")
> cFileName = "c:\dbasetests\testforimport.xls"
> oExcel.workbooks.open(cFileName)
> catch( Exception E )
> ? e.code, e.message
> ? "Problem opening spreadsheet"
> return
> endtry
>
> if file("test.dbf")
> drop table test
> endif
> create table Test (Field1 char(20),;
> Field2 Numeric(4,0),;
> Field3 Numeric(4,0),;
> Field4 Numeric(4,0),;
> Field5 Numeric(4,0),;
> Field6 Numeric(4,0),;
> Field7 Numeric(4,0),;
> Field8 Numeric(4,0),;
> Field9 Numeric(4,0),;
> Field10 Numeric(4,0) )
>
> Q = new QUERY()
> with (Q)
> sql = 'select * from "test.DBF"'
> active = true
> endwith
>
> // The user defines the Excel range
> // they wish to import
> cRange = "A3:J5"
>
> // Parse the range into four coordinates
> cStartRange = left(cRange,at(":",cRange)-1)
> cStartCol = ""
> cStartRow = ""
> for i=1 to len(cStartRange)
> if isAlpha(substr(cStartRange,i,1))
> cStartCol += substr(cStartRange,i,1)
> else
> cStartRow += substr(cStartRange,i,1)
> endif
> next
>
>
> cEndRange = substr(cRange,at(":",cRange)+1)
> cEndCol = ""
> cEndRow = ""
> for i=1 to len(cEndRange)
> if isAlpha(substr(cEndRange,i,1))
> cEndCol += substr(cEndRange,i,1)
> else
> cEndRow += substr(cEndRange,i,1)
> endif
> next
>
>
> // build an array of the columns to
> // import. LIMITATION: Col "AA" etc
> // are not handled.
> aCols = new array()
> //cCols = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
> cCols = "A |B |C |D |E |F |G |H |I |J |K |L |M |N |O |P |Q |R |S |T
|U
> |V |W |X |Y |Z |"+;
>
>
"AA|AB|AC|AD|AE|AF|AG|AH|AI|AJ|AK|AL|AM|AN|AO|AP|AQ|AR|AS|AT|AU|AV|AW|AX
> |AY|AZ|"+;
>
>
"BA|BB|BC|BD|BE|BF|BG|BH|BI|BJ|BK|BL|BM|BN|BO|BP|BQ|BR|BS|BT|BU|BV|BW|BX
> |BY|BZ|"+;
>
>
"CA|CB|CC|CD|CE|CF|CG|CH|CI|CJ|CK|CL|CM|CN|CO|CP|CQ|CR|CS|CT|CU|CV|CW|CX
> |CY|CZ|"+;
>
>
"DA|DB|DC|DD|DE|DF|DG|DH|DI|DJ|DK|DL|DM|DN|DO|DP|DQ|DR|DS|DT|DU|DV|DW|DX
> |DY|DZ|"+;
>
>
"EA|EB|EC|ED|EE|EF|EG|EH|EI|EJ|EK|EL|EM|EN|EO|EP|EQ|ER|ES|ET|EU|EV|EW|EX
> |EY|EZ|"+;
>
>
"FA|FB|FC|FD|FE|FF|FG|FH|FI|FJ|FK|FL|FM|FN|FO|FP|FQ|FR|FS|FT|FU|FV|FW|FX
> |FY|FZ|"+;
>
>
"GA|GB|GC|GD|GE|GF|GG|GH|GI|GJ|GK|GL|GM|GN|GO|GP|GQ|GR|GS|GT|GU|GV|GW|GX
> |GY|GZ|"+;
>
>
"HA|HB|HC|HD|HE|HF|HG|HH|HI|HJ|HK|HL|HM|HN|HO|HP|HQ|HR|HS|HT|HU|HV|HW|HX
> |HY|HZ|"+;
>
>
"IA|IB|IC|ID|IE|IF|IG|IH|II|IJ|IK|IL|IM|IN|IO|IP|IQ|IR|IS|IT|IU|"
> nPos = 1
> do
> if substr(cCols,nPos,2) => cStartCol and ;
> substr(cCols,nPos,2) =< cEndCol
> aCols.add(substr(cCols,nPos,2))
> endif
> nPos+=3
> until substr(cCols,nPos,2) > cEndCol
>
> // Append the data from the spreadsheet
> // into the table.
> for nRow=val(cStartRow) to val(cEndRow)
> q.rowset.beginAppend()
> for nCol=1 to aCols.size
> cRange = trim(aCols[nCol])+ltrim(str(nRow))
> xValue = oExcel.Range( cRange ).value
> q.rowset.fields[nCol].value = xValue
> next
> next
> q.rowset.save()
> oExcel.workbooks.close()
> q.active = false
> oExcel = null
> q = null
>
> Ken Mayer [dBASE, Inc.]
|
| Post Reply
|
|
|