| Author |
Import excel file in a new Dbase table
|
|
| Bob Romeijn 2005-07-27, 7:27 am |
| Hello All,
Can someone help me.
I am trying to import an excel file into a dbase table.
I just can't figure out how it must be done....
Any help is welcome.
Thanks in advance
Bob
| |
| Roland Wingerter 2005-07-27, 7:27 am |
| Bob Romeijn wrote:
>
> I am trying to import an excel file into a dbase table.
>
> I just can't figure out how it must be done....
--------
Check out Ken Mayer's importData.wfm in the dUFLP.
Roland
| |
| Bob Romeijn 2005-07-27, 7:27 am |
| Hello Roland,
I did find the wfm you mentioned.
Is there a specific way to use this form? should i extract dUFLPto a
specific folder?
I compiled the WFM however i receive error messages when executing the form.
Thanks so far for helping me.
"Roland Wingerter" <RW@germany.de> schreef in bericht
news:ZaVrOAqkFHA.2024@news-server...
> Bob Romeijn wrote:
> --------
> Check out Ken Mayer's importData.wfm in the dUFLP.
>
> Roland
>
>
| |
| Bob Romeijn 2005-07-27, 9:23 am |
| Already solved the problem by executing the program SetupSourceAlias.prg
However when I select my excelsheet I receive an OLE Autoclient error:
368--Error OLE Unknown error:8002:80018
"Bob Romeijn" <bromeijn@tiscali.nl> schreef in bericht
news:bej3shqkFHA.2024@news-server...
> Hello Roland,
>
> I did find the wfm you mentioned.
>
> Is there a specific way to use this form? should i extract dUFLPto a
> specific folder?
>
> I compiled the WFM however i receive error messages when executing the
> form.
>
> Thanks so far for helping me.
>
> "Roland Wingerter" <RW@germany.de> schreef in bericht
> news:ZaVrOAqkFHA.2024@news-server...
>
>
| |
| Roland Wingerter 2005-07-27, 9:23 am |
| Bob Romeijn wrote:
>
> I did find the wfm you mentioned.
>
> Is there a specific way to use this form? should i extract dUFLPto a
> specific folder?
>
> I compiled the WFM however i receive error messages when executing
> the form.
--------
For instructions how to set up the dUFLP see readme.txt.
After you have created a source alias you just call the form with
do :dUFLP:importData.wfm
The form will guide you through the process step by step. For further
information see file header.
Roland
| |
| Roland Wingerter 2005-07-27, 9:23 am |
| Bob Romeijn wrote:
> Already solved the problem by executing the program
> SetupSourceAlias.prg
>
> However when I select my excelsheet I receive an OLE Autoclient error:
> 368--Error OLE Unknown error:8002:80018
--------
Is Excel installed on your machine?
Roland
| |
| Bob Romeijn 2005-07-27, 9:23 am |
| Yes it is Roland.
"Roland Wingerter" <RW@germany.de> schreef in bericht
news:AdTORBrkFHA.1756@news-server...
> Bob Romeijn wrote:
> --------
> Is Excel installed on your machine?
>
> Roland
>
>
| |
| Roland Wingerter 2005-07-27, 9:23 am |
| Bob Romeijn wrote:
> Yes it is Roland.
--------
Sorry, then I have no idea what's wrong.
Roland
| |
| Bob Romeijn 2005-07-27, 9:23 am |
| No problem Roland.
I just keep looking for an answer.
Thanks for your time anyway.
Regards,
Bob
"Roland Wingerter" <RW@germany.de> schreef in bericht
news:C8djuIrkFHA.1528@news-server...
> Bob Romeijn wrote:
> --------
> Sorry, then I have no idea what's wrong.
>
> Roland
>
>
| |
| Roland Wingerter 2005-07-27, 11:37 am |
| Bob Romeijn wrote:
> Already solved the problem by executing the program
> SetupSourceAlias.prg
>
> However when I select my excelsheet I receive an OLE Autoclient error:
> 368--Error OLE Unknown error:8002:80018
-------
Searching the newsgroup archive I found this message (watch line wrap):
>
[url]http://64.132.211.166/App/FmtNws.dbw? NwsFile=f%3A%5Ccorpu
snws%5Cen%5Cprogram[
/url]
ming%5C80598%2Enws& Subject=Re%3A+Who+to
+open+Excel+document
s
Hope this helps.
Roland
| |
| Eric Logan 2005-07-27, 11:37 am |
| Bob;
Do you know the basic method?
a) Save Excel file as CSV.
b) Create DBF with structure to receive columns from CSV. Include additional
columns for modified values as needed.
c) 'Append from <csv file name>.CSV delimited'
d) Process your DBF file to fill additional fields, ie. fill true dates from
character dates.
e) Select valid data rows (no blanks or header rows) and fields into final
table.
I guess it was assumed that everybody knows this method.
E.L.
"Bob Romeijn" wrote ...
> I just keep looking for an answer.
| |
| Ken Mayer [dBVIPS] 2005-07-27, 8:24 pm |
| Eric Logan wrote:
> Bob;
> Do you know the basic method?
> a) Save Excel file as CSV.
> b) Create DBF with structure to receive columns from CSV. Include additional
> columns for modified values as needed.
> c) 'Append from <csv file name>.CSV delimited'
> d) Process your DBF file to fill additional fields, ie. fill true dates from
> character dates.
> e) Select valid data rows (no blanks or header rows) and fields into final
> table.
> I guess it was assumed that everybody knows this method.
> E.L.
Excel can also save as a .dbf ... that's my understanding, I haven't
played with it. Heck, I did things the hard way by creating the code in
question (well, lifted some of it from other folk) ....
Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/
*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase
| |
| Gerald Lightsey 2005-07-27, 8:24 pm |
| On Wed, 27 Jul 2005 11:55:56 +0200, in the dbase.how-to group, Bob
Romeijn said...
> Hello All,
>
> Can someone help me.
> I am trying to import an excel file into a dbase table.
> I just can't figure out how it must be done....
> Any help is welcome.
>
Method 1.
1. Open Excel.
2. Click in the row indicator for row 2.
3. Right click and select "Insert"
4. Click in the cell where the row indicators and column indicators
intersect. (This selects all rows and columns).
5. Hover the mouse in the column selector row where the border between
ANY TWO columns is displayed until the mouse cursor turns into a two-
headed arrow with a vertical bare between. Double-click to autosize
every column.
6. Type in some average-length character such as lower case X in row 2
to fill each column to its size set in step 5. (This is all important
because your export to .DBF will have truncated field width if you don't
have a good indicator for it at the top of the spreadsheet.
7. Click on File in the Excel menu and select Save As.
8. In the Save As dialog click on the Save As Type drop down and select
DBF (dBASE IV)(*.dbf)*
9. Give the .DBF a name in the entryfield just above.
10. If the spreadsheet has multiple sheets (pages) you will get a
warning that the .DBF will be only from the current sheet. Click Yes
that you understand that.
11. You will get a warning that the spreadsheet may contain some
information that cannot be translated to a .DBF. Click yes that you
understand that.
12. When you close the spreadsheet in Excel, (which now sees the file as
BEING a .DBF) you will be asked if you want to save it click NO because
you don't want to have a habit of changing .DBF's in MS Excel because of
a variety of things that it may do wrong.
13. Open the new .DBF exclusive. Go to the top and delete the record
full of "x" characters you added to control field width. Pack the
table.
14. Make any changes necessary to the structure of the table
concentrating first on the field names which, although dBASE will accept
and use it will not accept them when changing structure. After the name
are good and acceptable you can either change the data types or copy
values in one column to values in a newly created field with the data
type you want.
Believe it or not, I have found this method to probably be the fastest
when you do this a lot and where creation of a new .DBF is involved.
Method 2
If you already have a table structure in dBASE you can export data into
one of several types from MS Excel then import that type into the .dbf
table. The types are .CSV and several .TXT types. Each has their
idiosyncrasies. Just test them to find the ones you like.
Method 3.
If you already have a table structure in dBASE you can use dQUERY to
export the data from MS Excel to your dBASE table. You will need to be
familiar with the spreadsheet structure as well as the .DBF structure to
provide field mapping instructions. The advantage is that there is not
intermediate file left over as in Method 2.
Gerald
| |
| Eric Logan 2005-07-27, 8:24 pm |
| Ken;
I wonder when people just need the basic answer. It would be a good sign to
have lots of beginners here, even if maybe they bought the software at
e-bay!
Creating the DBF structure in Excel might be the most direct
non-programmatic method . Exporting from Excel to text has its pitfalls. For
example, cell formatting has an effect on the output. If numbers in Excel
are displayed with commas for thousands separators, the exported CSV file
keeps the commas, and the numbers are chopped up. Dumb. Also, it's hard to
anticipate all the ways that data in an XLS might not fit in a table
structure, like when people type N/A in numeric columns...
More power to you and the book;
Eric Logan
> "Ken Mayer [dBVIPS]" ...
> Excel can also save as a .dbf ... that's my understanding, I haven't
> played with it. Heck, I did things the hard way by creating the code in
> question (well, lifted some of it from other folk) ....
| |
| Bob Romeijn 2005-07-28, 3:24 am |
| Hello Eric,
Many thanks for your answer.
I thought that there would be something like importing an execel file in
Access.
I will use this method.
Thanks again.
Regards,
Bob
"Eric Logan" <jelogan@pcweb.net> schreef in bericht
news:bd3jJ3skFHA.1768@news-server...
> Bob;
> Do you know the basic method?
> a) Save Excel file as CSV.
> b) Create DBF with structure to receive columns from CSV. Include
> additional
> columns for modified values as needed.
> c) 'Append from <csv file name>.CSV delimited'
> d) Process your DBF file to fill additional fields, ie. fill true dates
> from
> character dates.
> e) Select valid data rows (no blanks or header rows) and fields into final
> table.
> I guess it was assumed that everybody knows this method.
> E.L.
>
> "Bob Romeijn" wrote ...
>
>
>
| |
| Bob Romeijn 2005-07-28, 3:24 am |
| Gentlemen,
I am truly sorry for waisting everybodys time.
You can save the excelsheet as DBF format.
The only thing you have to do is to check wether your headings from the
excelsheet are correctly imported.
Nothing the less it works fine.
Thanks all
Bob
"Ken Mayer [dBVIPS]" < dbase@_nospam_golden
stag.net> schreef in bericht
news:BhhRdRukFHA.1796@news-server...
> Eric Logan wrote:
>
> Excel can also save as a .dbf ... that's my understanding, I haven't
> played with it. Heck, I did things the hard way by creating the code in
> question (well, lifted some of it from other folk) ....
>
> Ken
>
> --
> /(Opinions expressed are purely my own, not those of dataBased
> Intelligence, Inc.)/
>
> *Ken Mayer* [dBVIPS]
> /Golden Stag Productions/
> dBASE at goldenstag dot net
> http://www.goldenstag.net/GSP
> http://www.goldenstag.net/dbase
| |
| Bob Romeijn 2005-07-28, 3:24 am |
| Hi Roland,
Thanks again.
I will check out this article
Bob
"Roland Wingerter" <RW@germany.de> schreef in bericht
news:m4XBeRskFHA.1768@news-server...
> Bob Romeijn wrote:
> -------
> Searching the newsgroup archive I found this message (watch line wrap):
>
> [url]http://64.132.211.166/App/FmtNws.dbw? NwsFile=f%3A%5Ccorpu
snws%5Cen%5Cprogram[
/url]
> ming%5C80598%2Enws& Subject=Re%3A+Who+to
+open+Excel+document
s
>
> Hope this helps.
>
> Roland
>
>
| |
| Bob Romeijn 2005-07-28, 3:24 am |
| Many thanks to you to Gerald.
After trying all the possibilities everyone has offered me I know have to
find out wich one works best for me.
I wil try them all and let you all know.
Thanks again everyone
Regards,
Bob
"Gerald Lightsey" <glightsey1@cox.net> schreef in bericht
news:MPG. 1d51a0b66fd854779898
61@news.dbase.com...
> On Wed, 27 Jul 2005 11:55:56 +0200, in the dbase.how-to group, Bob
> Romeijn said...
> Method 1.
>
> 1. Open Excel.
> 2. Click in the row indicator for row 2.
> 3. Right click and select "Insert"
> 4. Click in the cell where the row indicators and column indicators
> intersect. (This selects all rows and columns).
> 5. Hover the mouse in the column selector row where the border between
> ANY TWO columns is displayed until the mouse cursor turns into a two-
> headed arrow with a vertical bare between. Double-click to autosize
> every column.
> 6. Type in some average-length character such as lower case X in row 2
> to fill each column to its size set in step 5. (This is all important
> because your export to .DBF will have truncated field width if you don't
> have a good indicator for it at the top of the spreadsheet.
> 7. Click on File in the Excel menu and select Save As.
> 8. In the Save As dialog click on the Save As Type drop down and select
> DBF (dBASE IV)(*.dbf)*
> 9. Give the .DBF a name in the entryfield just above.
> 10. If the spreadsheet has multiple sheets (pages) you will get a
> warning that the .DBF will be only from the current sheet. Click Yes
> that you understand that.
> 11. You will get a warning that the spreadsheet may contain some
> information that cannot be translated to a .DBF. Click yes that you
> understand that.
> 12. When you close the spreadsheet in Excel, (which now sees the file as
> BEING a .DBF) you will be asked if you want to save it click NO because
> you don't want to have a habit of changing .DBF's in MS Excel because of
> a variety of things that it may do wrong.
> 13. Open the new .DBF exclusive. Go to the top and delete the record
> full of "x" characters you added to control field width. Pack the
> table.
> 14. Make any changes necessary to the structure of the table
> concentrating first on the field names which, although dBASE will accept
> and use it will not accept them when changing structure. After the name
> are good and acceptable you can either change the data types or copy
> values in one column to values in a newly created field with the data
> type you want.
>
> Believe it or not, I have found this method to probably be the fastest
> when you do this a lot and where creation of a new .DBF is involved.
>
> Method 2
> If you already have a table structure in dBASE you can export data into
> one of several types from MS Excel then import that type into the .dbf
> table. The types are .CSV and several .TXT types. Each has their
> idiosyncrasies. Just test them to find the ones you like.
>
> Method 3.
> If you already have a table structure in dBASE you can use dQUERY to
> export the data from MS Excel to your dBASE table. You will need to be
> familiar with the spreadsheet structure as well as the .DBF structure to
> provide field mapping instructions. The advantage is that there is not
> intermediate file left over as in Method 2.
>
> Gerald
>
>
| |
| Roland Wingerter 2005-07-28, 3:24 am |
| Bob Romeijn wrote:
>
> I am truly sorry for waisting everybodys time.
>
> You can save the excelsheet as DBF format.
------
No need to apologize, it was my fault. I misunderstood your message, I
thought you wanted to do it programmatically.
Roland
|
|
|
|