excel to dbf 1

[sqlpro]

hi friends

I have excel file with data in 1st col like following..
1
2
3
4
AA1
AA2
AA3
AA4
C2PI
and when i run following command in vfp 6
loExcel.ActiveWorkbook.Activesheet.SaveAs( ADDBS(lcPath) + "ACCCodeXLS",FORMAT_DBASE4)

it saving only 1,2,3 and 4 only and ignoring other values.
i even tried saving directly from excel also but samething happens.
i even chnaged column to text(i mean using formatcells)
any ideas please.
Thanks

cheers

 

[jimoo]

Judging by the name of the thread it looks like you are trying to read the data an update a vfp table. It may be best if you try using the import command to import the data into a VFP table or cursor and then use that to update your production VFP table.

Look at the import command.

* depending on your vfp version
IMPORT FROM myfile.xls TYPE XLS
IMPORT FROM myfile.xls TYPE XL8


For more information type: HELP IMPORT in the command window.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[sqlpro]

Yes Jim Osieczonek
you are right.we get an excel file which we import to dbf.
i tried ur command and got following error
"Microsoft Excel file format is invalid"

what i am currently doing is
lcpath = JUSTPATH(tcfile)
lcFile = ''
loExcel = CREATEOBJECT ('excel.application')
loExcel.Columns("C:C").Select
loExcel.Selection.ColumnWidth = 10
loExcel.Selection.NumberFormat = ["@"]
WAIT WIND NOWAIT "Please wait .... Saving Excel file as 'ACCCODEXLS.DBF' "
loExcel.Sheets(1).Range(lcRange).Select()
loExcel.ActiveWorkbook.Activesheet.SaveAs( ADDBS(lcPath) + "ACCCodeXLS",FORMAT_DBASE4)

thats what i am doing.

cheers

 

[sqlpro]

to add above
actually there r other cols in dbf which need 2 b updated once data imported from excel.

cheers

 

[baltman]

As far as 'missing' the other columns, this is probably happening because the column name (row 1) is starting with a number, which is not a valid dfb column name. Try re-naming the column.

In general, Excel to DBF is tricky business. I'd recommend saving as a delimited file and then appending into a table for clean-up.

You may encounter other issues depending on data types/formats.

We can help you with specific issues if you post 'em.

Brian

 

[baltman]

I think I misread your post and you are missing rows, not columns. Other Excel 'bugs':

1. it judges datatype from 1st instance of data, so place a single quote in front of the data or a dummy line with character data in the 1st row, and make sure its a character datatype when imported.

2. it can truncate your data simply based on the column width in Excel, so manually make the column widths wider than they'd ever need to be.

Brian

 

[sqlpro]

Thanks for the posts brian
---------
1. it judges datatype from 1st instance of data, so place a single quote in front of the data or a dummy line with character data in the 1st row, and make sure its a character datatype when imported.
------------------------

i tried to single quote and even @ but with no success.
finally what did was i replaces first value (i.e 1 with yyyyyyy)
so now it took all other values


cheers

 

[sqlpro]

sorry Brian i may b vague
-------
I think I misread your post and you are missing rows, not columns. Other Excel 'bugs':
------------
actually what happens is
after importing to dbf
the dbf shows only 1,2,3 and 4 in cols and rest of the rows showing empty instead of showing other values i.e
AA1
AA2
AA3
AA4
C2PI


cheers

 

[keepingbusy]

sqlpro

Just copied and pasted your data as shown into an Excel file and saved it as TEST.XLS

1
2
3
4
AA1
AA2
AA3
AA4
C2PI

I then issued the command jimoo posted with a slight amendment:

IMPORT FROM C:\WINDOWS\DESKTOP\test.xls TYPE XLS

This creates a table called test with all your data.

Am I missing something here?

I'm sure you know this but you can change the field names after import to suit you etc

use test excl
alter table test add column yourfield
go top
do while not eof()
replace yourfield with A && Your data
skip
enddo
alter table test drop column A

Hope that helps
Lee....

Alone we can do so little, together we can do so much

http://www.customsoftware.co.uk

 

[sqlpro]

Thank you very much "keepingbusy "
ur solution looks good


cheers

 

[keepingbusy]

My pleasure as always
Lee

Alone we can do so little, together we can do so much

http://www.customsoftware.co.uk

 

[sqlpro]

Great Lee
I just tried ur idea.it works great
thank you very much

cheers

 

[keepingbusy]

Thanks for the star sqlpro

Good luck

Alone we can do so little, together we can do so much

http://www.customsoftware.co.uk

 

[sqlpro]

by the way Lee why on some excel files the following command
IMPORT FROM C:\WINDOWS\DESKTOP\test.xls TYPE XLS
giving me error like..
Microsoft Excel file format is invalid
any ideas?
Thank you.

cheers

 

[keepingbusy]

The only thing I can think of is with some older versions it doesn't recognise the format. We had the same thing but got around it by first opening up the old XLS file, then saving it (that's when you get messages such as, This is a previous version / format - save it as version... some of the formatting may be lost etc" If you just save it then try the import, you shouldn't have any problems. We haven't lost any formatting yet!

I agree it can be nuisance but simply saving it as a newer version, always sorts out the problem

Hope that helps you
Lee......

Alone we can do so little, together we can do so much

http://www.customsoftware.co.uk

 

[sqlpro]

Thanks Lee.
Thats what i thought.


cheers

 

[sqlpro]

Hi Lee r u there?
i have some date cols in my excel file
when i import its truncating year part
for example excel has 1-Feb-92
when i import it chnages 01/02/199
i even tried like
loExcel.Columns("K:K").Select
loExcel.Selection.NumberFormat = "dd/mm/yyyy;@"
before importing
but does not work.

cheers

 

[keepingbusy]

Bear with me sqlpro, you may need a piece of code to sort that one out. When I'm in the office, I'll post back

Lee

Alone we can do so little, together we can do so much

http://www.customsoftware.co.uk

 

[sqlpro]

cheers my friend

cheers

 

[keepingbusy]

sqlpro

Can you just confirm that your dates in Excel are always in this format?

01-Feb-92
03-Mar-96
21-Apr-02
16-Dec-04

Let me know
Lee

Alone we can do so little, together we can do so much

http://www.customsoftware.co.uk