trouble loading in XML file

[hopkinsc]

Hi all,

I have an XML file that i have no idea how to load in properly, could someone possibly help please?

i have attached a sample file (saved as xls) and i need to load in the data as it is (like a standard xls, with the columns being loaded in a field names)

any help would be great.

Thanks

[Anonymous]

Keep the file name with an xml extension.

In the script editor :

     <Click>     Table Files

     Select your xml file

     Make sure File Type is xml

     <Click> Finish

This will generate the load script to load your xml, now reload it.

I expect you will need to adjust the generated script, depending on your requirements.

[hopkinsc]

Hi Bill,

Thanks, i have already used the wizard but i am not getting the field names i require. all field names are being put into a data field.

I have no idea how to split them out.

[mjayachandran]

When I encounter such xml issue i normally execute a vbscript that converts xml to xls then I load the data from xls and delete the xml.

Vbscript to convert to xls :

Dim xlApp, xlWkb, SourceFolder,TargetFolder,file

Set xlApp = CreateObject("excel.application")

Set fs = CreateObject("Scripting.FileSystemObject")

file ="Sample.xml"

Const xlNormal=1

SourceFolder="C:\Users\mjayachandran\Desktop"

TargetFolder="C:\Users\mjayachandran\Desktop"

xlApp.Visible = false

  Set xlWkb = xlApp.Workbooks.Open(SourceFolder & "\" & file)

  BaseName= fs.getbasename(SourceFolder & "\" & file)

  FullTargetPath=TargetFolder & "\" & BaseName & ".xls"

  xlWkb.SaveAs FullTargetPath, xlNormal

  xlWkb.close

fs.DeleteFile("C:\Users\mjayachandran\Desktop\Sample.xml")

Set xlWkb = Nothing

Set xlApp = Nothing

Set fs = Nothing

[Anonymous]

I am not convinced your file is a true xml data file per se, but looks more like an Excel spreadsheet saved in xml format.

Do you know where it came from & how it was generated ?

[hopkinsc]

it came from a client, and they say they run a macro which converts an ascii file into this xml.

I do agree though, it looks very strange.

[Anonymous]

Can you go back to client and ask for the original ascii file ?

[hopkinsc]

we initially had trouble with the ascii file also which is why they have done this. I think i need to go back and ask if they can export the file into any other format.

Thanks

[pedrolau]

Hi guys, I found myself troubled by this event, basically all the field names and values are listed in a single field and I extract it using the following code:

* It's in spanish but I think you will be fine.

* It generates a file named "TABLA.TXT", you can get rid of it with a macro or vb.

//================================ PARAMETROS =====================================//


SET vArchivo = Datos\1410.xls;
SET vNombreTabla = Actividades;


//================================   SCRIPT   =====================================//


XML_META:
LOAD [Worksheet/WorksheetOptions/Panes/Pane/ActiveRow] as Filas,
    [Worksheet/Table/ss:ExpandedColumnCount] as Columnas
FROM $(vArchivo) (XmlSimple, Table is [Workbook]);


LET vColumnas = PEEK('Columnas',0,'XML_META');
LET vFilas = PEEK('Filas',0,'XML_META');


DROP TABLE XML_META;


TABLA:


LOAD ROWNO() AS ROW
AUTOGENERATE $(vColumnas);




FUENTE:
LOAD ROWNO() AS ROWFUENTE,
  Data
FROM $(vArchivo) (XmlSimple, Table is [Workbook/Worksheet/Table/Row/Cell]);




FOR vLoop = 1 TO $(vFilas)


LEFT JOIN(TABLA)
LOAD ROWNO() AS ROW,
  Data AS $(vLoop)
RESIDENT FUENTE
WHERE ROWFUENTE > ($(vColumnas) * ($(vLoop)-1) ) AND ROWFUENTE <= ($(vColumnas) * $(vLoop));


NEXT


DROP TABLE FUENTE;
DROP FIELD ROW;


STORE TABLA INTO TABLA.TXT (TXT);
DROP TABLE TABLA;


[$(vNombreTabla)]:


LOAD *
FROM
TABLA.TXT (txt, utf8, embedded labels, delimiter is ',', msq, header is 4 lines, filters(Rotate(right)));




//=============================   FIN SCRIPT   ===================================//

BR

[Anonymous]

Contact Qlik support support@qlik.com for help, if there is a valid maintenance.