Convert Multiple records into One (XLS to DBF)

[MoGrace]

I have an Excel file that is structured like this that I created a table from:
fieldname1 field_data1
fieldname2 field_data 2 ...etc.

I want to turn that into another table so that each fieldname# becomes a field in the table and the corresponding field_data its value in one record

fieldname1 fieldname2 fieldname3
field_data1 field_data2 field_data3

I was able to do the reverse, but now need to turn it back - how do I do that?

The project involves using a browse as a template to fill in about 50 different fields and then create a record in the corresponding table that has all 50 of those fields.

[Stan Mathews]

Re: Convert Multiple records into One (XLS to DBF)

Export to Excel, copy the active area, go to an empty area, paste special choosing transpose. Choose transposed area, save as dbf or import.

Just remembered I did a function for that. (Excel 2003 or earlier only)

brws_trans - 01-05-2017 -1.txt

[MoGrace]

Re: Convert Multiple records into One (XLS to DBF)

Thanks Stan, but the function is looking for an xlsx file and reports it is not found. I opened Excel and created the file but it still says not found. I don't understand enough of what this function is doing to know how to set it up for what it wants...

IW error:

brws_trans("fin_tmpl","testudf")
ERROR: command: xlApp.Workbooks.Open(tablename)
OLE Automation Error. Source is Microsoft Excel."

Changed to use full path and still got this error:
ERROR: command: xlApp.ActiveWorkbook.SaveAs(newtable,11) '-4143 is a constant for Excel Normal,
OLE Automation Error. Source is Microsoft Excel."
SaveAs method of Workbook class failed"

[Stan Mathews]

Re: Convert Multiple records into One (XLS to DBF)

'to call the function, use the example on the next line
'brws_trans("p:\sometable.dbf","p:\newtablename.dbf")

| should be backslash.

That's why the warning about Excel 2003 or earlier. Later versions don't support .dbf.

[MoGrace]

Re: Convert Multiple records into One (XLS to DBF)

Since I already have the 2 tables, I can skip the Excel intermediary can't I? Maybe I can use an array or modify an append with xbasic to get my multiple records to post to one record. I spent quite a bit of time in here and couldn't find any posts that tried to do this. Surely I am not the first!

If I open my one record table filtered to the record I want, I should be able to fetch thru the other and post the correct fields. I am stuck on how to map them.

[Stan Mathews]

Re: Convert Multiple records into One (XLS to DBF)

You didn't have both tables in your first post. If you want to attach a sample source table and an empty destination table I'll write a script to do what you want.


This should do it if you have both tables. Fieldn is the field name of the source table field containing the fieldname, Fieldd is the field containing the field data.

dim tbl_src as P
dim tbl_dest as P
tbl_src = table.open("source")
tbl_dest = table.open("dest")
tbl_dest.zap(.T.) 'assuming you have one record in the source table and want to create one record in the destination
flds = tbl_dest.fields_get()
tbl_dest.enter_begin()
for qx = 1 to flds
eval("tbl_dest."+tbl_src.fieldn) = tbl_src.fieldd
tbl_src.fetch_next()
next
tbl_dest.enter_end()
tbl_dest.close()
tbl_src.close()

[MoGrace]

Re: Convert Multiple records into One (XLS to DBF)

Aah, I can see how the eval() would work for the mapping - THANK YOU. I have to do some modification(s) to the template table, but this should get me going. I'll post an example when I get it working...

Actually I did say I had the table in the first line of the post - created from the Excel file. ;)

[Stan Mathews]

Re: Convert Multiple records into One (XLS to DBF)

Yes, but not both as I understood it.

[MoGrace]

Re: Convert Multiple records into One (XLS to DBF)

I made a test database in case anyone would like to see what it does...

TmplTest_20170108.zip