From Excel to a dbf table

[Ernesto_Paez]

Hi everyone,
here is my question, would like to know some suggestions, to see if this is possible.

i have an application named Autodesk inventor 2018, where we create dwgs, but each object in there has a tag or value, so i can use some vb scripts to extract the data to excel but have not been able to extract it as the dbf structure as the table has a lot of fileds where i am not be filling from the excel, the problem is that the dbf file structure is in a particular order and the excel file extracted will have some of the columns needed with values but not in the same order as the table, so i am wondering if i can do something in VFP 9.0 to remap those columns/values to the corresponded fields in the table ?

FOR example the excel file extracted columns can look something like this.

DRAW NO  REV  BALL NO   DESCRIPT PART NO   VENDPART NO
values  values  values  values     values    values

and my tables structure fields is like this.

job_no  draw_no ball_no part_no vend_partno  descript rev   etc etc more fields

so i am wondering if it is way to remap the values from the excel file above to the table ? if so can i get some clues?
Thanks in advance

 

[Olaf Doschke]

Well, in VFP you can't have spaces in column names, that's one of the problems, but it's a chrtran with underline to solve that, perhaps.

If you get excel into a dbf as is,and just have a column order problems, that's no problem for append, as that will not map field1 to firld1, but map fields by their name.

Simple example, once you are in two cursors with similar columns in different order:

Create Cursor crsSource (draw_no I, Descript c(100), ball_no I)
Insert Into cvrsSource(1,"test",2)
Create Cursor crsTarget (ball_no I, draw_no I, Descript c(100))
Append From Dbf("crsSource")
Browse

So this shows the order of columns won't matter, once you have it. You just will need to get from excel into any staging cursor and then append to your target dbf from there, not append from xls, and you make use of the nature of append to map by column names no matter how they are ordered. Directly appending or importing from excelyou get the ata in excel column order, but you shouldn't import or append from an xls or xlsx, take an excel oledb provider and use an excel sheet as data source reading into cursor or maybe make use of

http://praisachion.blogspot.de/2015/08/importfromxlsx-11.html

Bye, Olaf.

 

[Ernesto_Paez]

Olaf,
I understand what u are saying but i did not explain myself correctly

these,are the columns name in excel and the order they have in the excel sheet

DRAW NO REV BALL NO DESCRIPT PART NO VENDPART NO

so you are recommending no to import or append from excel to dbf as it won't work correct ?
instead use and OLEDB or Vilhelm approach ?
Thanks

 

[Olaf Doschke]

Yes, first import/append into a cursor that fetches excel dat as is, then append to your target dbf, perhaps after cleaning data and renaming columns, whatever is necessary. So take an intermediate step to solve your problem.

Bye, Olaf.