[solved] XML import to Lianja table

**gcjm** · 2024-05-06, 09:42

The following "simple" task that I can't do:

I have exported an "excel" file (xls from OpenOffice) as an XML file
and would like to import the XML file into a Lianja table.

The xls file only has two columns (A and B) that should be in the table:
- Datum (date)
- Text (varchar)
In my opinion, I understood the Lianja documentation.
I could send the XML file too. Maybe there is a problem?

Thanks for your help.
Georg

In the meantime:
I'll try csv import:
OpenOffice table save as csv ("cells as shown")

I think the attached xml is a wrong export from OpenOffice.
Georg

**yvonne.milne** · 2024-05-07, 05:06

Hi Georg,

To import from XML, the XML file needs to be in ADO format (your attachment is not).

Yes, use CSV or you may be able to use the XQuery functions.

Regards,

Yvonne

**gcjm** · 2024-05-07, 06:53

XML:
It seems OpenOffice Calc cannot save or export as ADO format.

CSV:
append from <filename>
[while <condition as logical>] [for <condition as logical>]
[type] sdf | fixed | delimited | delimited with blank | delimited with <delimiter> | csv

csv does not ask for field and line delimiters (I set $ and §)
I cannot use the standard delimiters , and " because there are a lot such signs in the text.

Another idea?
Thank you!

**HankFay** · 2024-05-07, 09:01

Perhaps a different exporter will help: http://digitalimprint.com/misc/oooex...hat's%20it.

Hank

**gcjm** · 2024-05-07, 10:38

Hi Hank,

the xml looks fine now with the digitalimprint addon. But nether create (0 byte) nor import (lianja app builder crashes) do work.

I append the xml fiel as zip

Thank you!
Georg

**barrymavin** · 2024-05-07, 20:51

Hi Georg

Use xml_decode_file()
See doc: https://www.lianja.com/doc/index.php/XML_DECODE_FILE()

Code:

olddate = set("DATE")
set date german
drop table btest if exists
create table if not exists btest (datum date, vtext varchar) 
use btest
orows = xml_decode_file("unbennant1.xml", "ooo_sheet", "ooo_row")
foreach orows as orow
    insert into btest (datum, vtext) values(ctod(orow.column_1), strconv(orow.column_2))
endfor
set date &olddate
use btest
list datum, etos(vtext)

**gcjm** · 2024-05-08, 04:42

Hi Barry,
thanks for your help!
Unfortunately your code is too "genious" for me and also doesn't work, altered by me .
Error see below.

https://www.lianja.com/doc/index.php/XML_DECODE_FILE()
doesn't help me. I don't understand parameters 2 and 3.

olddate = set("DATE")
set date german

// drop table btest
// if exists create table if not exists btest (datum date, vtext varchar)

if !file("btest.dbf")
create table btest (datum date, vtext varchar)
endif

use btest

// following line brings error => "ARRAY name was expected"
orows = xml_decode_file("unbennant1.xml", "ooo_sheet", "ooo_row")

foreach orows as orow
insert into btest (datum, vtext) values(ctod(orow.column_1), strconv(orow.column_2))
endfor
set date &olddate
use btest
list datum, etos(vtext)

Georg

**barrymavin** · 2024-05-08, 05:21

Georg,

I ran the code before I gave you it.

It worked fine.

Im assuming you used the xml file you attached to you ticket. That’s what I used.

Open your xml file in a text editor and you will see the xml tags ooo_sheet and ooo_row.

orows = xml_decode_file("unbennant1.xml", "ooo_sheet", "ooo_row")

if you do not use the xml file structure you attached to the ticket obviously it will not work and obviously it will not create the dynamic array containing the rows.