INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

From Excel to a dbf table

From Excel to a dbf table

(OP)
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.

CODE -->

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

and my tables structure fields is like this.

CODE -->

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


RE: From Excel to a dbf table

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:

CODE

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/importfromx...

Bye, Olaf.

RE: From Excel to a dbf table

(OP)
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


RE: From Excel to a dbf table

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close