Importing Excel into dBASE Plus
Tom Barbour
Plus, as I was getting a curious error message (too many characters in a
field more than 1000 lines past the bottom of my table). I tried some of the
advice in the responses, with similar results, and then I posted a report in
dbase.bug-reports. The response to that had me post the relevant files in
dbase.binaries, and there was some response to that as well.
Returning to the problem, I tried to find my previous postings . . . but
they are GONE!
Now I still want to import Excel spreadsheet contents into DBF files, but
nothing works, and there are no helpful information in the KnowledgeBase. So
far, I've tried converted simple spreadsheets into TXT files, CSV files, and
MDB (MS Access) files, and I've attempted to use the Import Wizard to
transfer their contents into empty, defined, Dbase tables.
Can this be done?
Thanx,
Tom Barbour
Ken Mayer [dBASE, Inc.]
>Returning to the problem, I tried to find my previous postings . . . but
>they are GONE!
Check your browser, you probably have it set to only show you new
postings.
>Now I still want to import Excel spreadsheet contents into DBF files, but
>nothing works, and there are no helpful information in the KnowledgeBase. So
>far, I've tried converted simple spreadsheets into TXT files, CSV files, and
>MDB (MS Access) files, and I've attempted to use the Import Wizard to
>transfer their contents into empty, defined, Dbase tables.
It should be possible.
Ken
---
Ken Mayer [dBASE, Inc.]
** Please respond ONLY in the newsgroups **
"Think OOP"
dBASE, Inc. website: http://www.dbase.com
Michael Nuwer
"Tom Barbour" <tbar...@stny.rr.com> wrote in message
news:b30og6$6tp$1...@news.dbase.com...
>
> Now I still want to import Excel spreadsheet contents into DBF files, but
> nothing works, and there are no helpful information in the KnowledgeBase.
So
> far, I've tried converted simple spreadsheets into TXT files, CSV files,
and
> MDB (MS Access) files, and I've attempted to use the Import Wizard to
> transfer their contents into empty, defined, Dbase tables.
>
Excel can save the active sheet as a dbf (level 4) file. Use Save As and
choose dbf as the file type. I find it helpful to autofit the column widths
before saving the sheet. Is this what you need?
Headhunter
Yes, this works, up to a point. I'm trying to build a requirements
traceability type of program, to use in development of proposals for
government contracts. One of the fields I must use is "section numbers," in
the form of "n.n.n.n...n," e.g. "3.2.2.1"; and another is a "requirement,"
which may be as long as 254 characters. The section numbers get changed from
"Text" in Excel to "Number" in DBF, and are dropped if they are longer than
"n.n." I would like to use a "Memo" type field for the requirement, so that
I don't have fields stretching across several monitor screens (which I do
not have.) If I try to change the field type ("Character" to "Memo"), the
content is changed to a boxed "A."
In the past, I've done this in MS Access. I begin with a document, like a
specification, in Word, parse out the individual requirements, and then
convert to a table. I copy the table into MS Excel, and add a field (column)
for where the requirement is to be discussed in the proposal, and one for
the assigned author. I make another table for the proposal outline, which
has section numbers similar to the specification numbers. Another table for
author assignments. Then I build the relationships, and I can create queries
or reports to build cross-references in any direction, and forms to make
changes. Setting this up is very time-consuming, and the capabilities of MS
Access are limited. I believe I can do the job I need much more effectively
with a dBASE run-time application.
I'd like to be able to design my tables in dBASE and import from the Excel
worksheets, but the Import Wizard only allows certain data types: SDF, CSV,
Comma delimited with quotes, tab separated values, and table. I've converted
the Excel worksheets to each of these, and the import always bombs out,
saying there is a field containing more than 4096 characters in line 1078 or
1080. I haven't tried this with any tables of more than 201 rows, so I don't
understand how there can be a problem that far down the list.
I thought I read that dBASE can handle Access data, but there is no
reference to this in Help or in the Knowledge Base.
I apologize for the length of this posting, but simple questions don't
convey the complexity of what I'm trying to do. With this background, I have
the following questions"
1. Can field types be managed when converting XLS to DB4?
2. Can a dBASE table content be linked to an Excel Sreadsheet?
3. Can dBASE import from an MS Access MDA or MDB file?
Thanx, Grazie, Merci, etc!
Tom Barbour
"Michael Nuwer" <nuw...@econ.potsdam.edu> wrote in message
news:b311ic$8gh$1...@news.dbase.com...
Headhunter
You're right! It was my browser settings. My apologies!
However, I still have not been able to make the import wizard work.
Tom Barbour
"Ken Mayer [dBASE, Inc.]" <kma...@dbase.com> wrote in message
news:cvq75v01rpcpb86tn...@4ax.com...
Ken Mayer [dBASE, Inc.]
>You're right! It was my browser settings. My apologies!
>However, I still have not been able to make the import wizard work.
If, as I recall, you have blank data, you won't get the Import Wizard
to work with dQuery currently. This is a known bug. That means it
doesn't work.
Bowen Moursund [dBASE Inc.]
<g>.
I believe that MS installs an ODBC driver for Excel tables along with
Excell itself. The BDE seems to find it - I see an 'Excel Files' alias.
It might be possible to create a DSN for the Excel table in question and
access it in dBASE as you would any ODBC data source.
--
Bowen Moursund (dBASE Inc.)
dBASE Newsgroups info & guidelines at:
http://www.dBASE.com/Docs/NewsGrps.htm
Michael Nuwer
"Headhunter" <headh...@stny.rr.com> wrote in message
news:b33caj$kc9$1...@news.dbase.com...
> Michael --
>
> Yes, this works, up to a point. I'm trying to build a requirements
I would agree that the Excel export to a dbf leaves much to be desired.
> traceability type of program, to use in development of proposals for
> government contracts. One of the fields I must use is "section numbers,"
in
> the form of "n.n.n.n...n," e.g. "3.2.2.1"; and another is a
"requirement,"
> which may be as long as 254 characters. The section numbers get changed
from
> "Text" in Excel to "Number" in DBF, and are dropped if they are longer
than
> "n.n."
Yep, been there seen that.
> I would like to use a "Memo" type field for the requirement, so that
> I don't have fields stretching across several monitor screens (which I do
> not have.) If I try to change the field type ("Character" to "Memo"), the
> content is changed to a boxed "A."
This a dBASE issue. The A is displayed in a grid. What I do is to not
include the memo field in the grid and place an editor near the grid so that
the selected record has it's memo data displayed in the editor. Another
possiblitity, if the memo field is read only, is to display it with a text
object, which will wrap the text.
>
> In the past, I've done this in MS Access. I begin with a document, like a
> specification, in Word, parse out the individual requirements, and then
> convert to a table. I copy the table into MS Excel, and add a field
(column)
> for where the requirement is to be discussed in the proposal, and one for
> the assigned author. I make another table for the proposal outline, which
> has section numbers similar to the specification numbers. Another table
for
> author assignments. Then I build the relationships, and I can create
queries
> or reports to build cross-references in any direction, and forms to make
> changes. Setting this up is very time-consuming, and the capabilities of
MS
> Access are limited. I believe I can do the job I need much more
effectively
> with a dBASE run-time application.
>
> I'd like to be able to design my tables in dBASE and import from the Excel
> worksheets,
I have had many of the same frustrations as you when trying to work with
Excel and Access. For that reason I've been using OLE Automation with my
dBASE apps. Below is some dBASE code that will open an excel file and read
the first two columns of the active sheet, row-by-row, into a dbf file.
Perhaps you could something like this for the project you're working on.
See also:
http://www.dbase.com/Knowledgebase/adv/activex/ExcelOle/excel.htm
oExcel = new oleAutoclient("Excel.Application")
cFileName = "c:\cmp310_fall2001.xls"
oExcel.workbooks.open(cFileName)
if file("test.dbf")
drop table test
endif
create table Test (Field1 char(10),Field2 blob)
Q = new QUERY()
with (Q)
sql = 'select * from "test.DBF"'
active = true
endwith
// first row has header info so
// we start reading at row 3
nRow = 2
bMore = true
do while bMore=true
q.rowset.beginAppend()
oCell = oExcel.ActiveSheet.cells( nRow, 1 )
q.rowset.fields['field1'].value = ;
oCell.value
oCell = oExcel.ActiveSheet.cells( nRow, 2 )
q.rowset.fields['field2'].value = ;
oCell.value
nRow++ // same as skip
// I don't know how to test for the last row
// with data. i.e. the equivalent to:
// do while not eof()
// So I' m testing for the first empty cell in
// column A. That might not work in your
// workbook.
oCell = oExcel.ActiveSheet.cells( nRow, 1 )
if empty(oCell.value)
q.rowset.save()
bMore = false
endif
enddo
oExcel.workbooks.close()
q.active = false
oExcel = null
q = null
> but the Import Wizard only allows certain data types: SDF, CSV,
> Comma delimited with quotes, tab separated values, and table. I've
converted
> the Excel worksheets to each of these, and the import always bombs out,
> saying there is a field containing more than 4096 characters in line 1078
or
> 1080. I haven't tried this with any tables of more than 201 rows, so I
don't
> understand how there can be a problem that far down the list.
>
> I thought I read that dBASE can handle Access data, but there is no
> reference to this in Help or in the Knowledge Base.
dBASE can read an Access table directly. Does your data start in Access?
>
> I apologize for the length of this posting, but simple questions don't
> convey the complexity of what I'm trying to do. With this background, I
have
> the following questions"
>
> 1. Can field types be managed when converting XLS to DB4?
Not that I know of.
>
> 2. Can a dBASE table content be linked to an Excel Sreadsheet?
With code similar to the above, but not in the Microsoft sense of an OLE
link.
>
> 3. Can dBASE import from an MS Access MDA or MDB file?
Sure:
Use :AccessAlias:TableName
Copy to :dBASEAlias:TableName
If you have Access < 2000, the BDE has a native driver to create the alias.
If you have Access => 2000 you must use the ODBC driver.
Michael Nuwer
> Michael, am interested in looking at this more closely, possibly for
> eventual use in the ImportData routine I have ... if I can get it to
> work in a more generic fashion, is it okay if I borrow this?
Hi Ken, feel free to use anything that helps.
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.
oExcel = new oleAutoclient("Excel.Application")
cFileName = "c:\cmp310_fall2001.xls"
oExcel.workbooks.open(cFileName)
if file("test.dbf")
drop table test
endif
create table Test (Field1 char(10),Field2 char(10))
Q = new QUERY()
with (Q)
sql = 'select * from "test.DBF"'
active = true
endwith
// The user defines the Excel range
// they wish to import
cRange = "C2:D10"
// 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"
nPos = 1
do
if substr(cCols,nPos,1) => cStartCol and ;
substr(cCols,nPos,1) =< cEndCol
aCols.add(substr(cCols,nPos,1))
endif
nPos ++
until substr(cCols,nPos,1) > cEndCol
// Append the data from the spreadsheet
// into the table.
for i=val(cStartRow) to val(cEndRow)
q.rowset.beginAppend()
for j=1 to aCols.size
cRange = aCols[j]+ltrim(str(i))
nValue = oExcel.Range( cRange ).value
q.rowset.fields[j].value = nValue
next
next
q.rowset.save()
Ken Mayer [dBASE, Inc.]
> nRow++ // same as skip
>
> // I don't know how to test for the last row
> // with data. i.e. the equivalent to:
> // do while not eof()
> // So I' m testing for the first empty cell in
> // column A. That might not work in your
> // workbook.
> oCell = oExcel.ActiveSheet.cells( nRow, 1 )
> if empty(oCell.value)
> q.rowset.save()
> bMore = false
> endif
>enddo
>
>oExcel.workbooks.close()
>q.active = false
>oExcel = null
>q = null
Michael, am interested in looking at this more closely, possibly for
eventual use in the ImportData routine I have ... if I can get it to
work in a more generic fashion, is it okay if I borrow this?
Ken
Ken Mayer [dBASE, Inc.]
>
>Hi Ken, feel free to use anything that helps.
>
>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.
Thanks. I'll take a look. This is just something that's been sitting
in the back of my fuzzy head for awhile, and I haven't spent any time
dealing with OLE automation. I'll see what I can see. <G>
Ken Mayer [dBASE, Inc.]
>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"
//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.]
>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.
Question: Is it possible to specify a page of the worksheet? Noting
that Excel has multiple pages, I would imagine someone might need the
second, third, etc. page of the spreadsheet, rather than the first?
(This has probably been covered somewhere and I'm just overlooking it
...)
Thanks -- Ken
Michael Nuwer
> Question: Is it possible to specify a page of the worksheet? Noting
> that Excel has multiple pages, I would imagine someone might need the
> second, third, etc. page of the spreadsheet, rather than the first?
> (This has probably been covered somewhere and I'm just overlooking it
> ...)
In the context of the code you're using, the sheet name can be added as part
of the range. You would use the Excel syntax: Sheet1!A1
I successfully tested the following:
cSheetName = "Sheet2"
for nRow=val(cStartRow) to val(cEndRow)
q.rowset.beginAppend()
for nCol=1 to aCols.size
cRange = trim(cSheetName + "!" + aCols[nCol])+ltrim(str(nRow))