topic Re: trouble loading in XML file in QlikView

trouble loading in XML file

hopkinsc — Mon, 06 Oct 2014 14:08:34 GMT

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

Re: trouble loading in XML file

Anonymous — Mon, 06 Oct 2014 14:21:31 GMT

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.

Re: trouble loading in XML file

hopkinsc — Mon, 06 Oct 2014 14:36:42 GMT

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.

Re: trouble loading in XML file

mjayachandran — Mon, 06 Oct 2014 15:10:59 GMT

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

Re: trouble loading in XML file

Anonymous — Mon, 06 Oct 2014 15:11:31 GMT

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 ?

Re: trouble loading in XML file

hopkinsc — Mon, 06 Oct 2014 15:14:03 GMT

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.

Re: trouble loading in XML file

Anonymous — Mon, 06 Oct 2014 15:19:02 GMT

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

Re: trouble loading in XML file

hopkinsc — Mon, 06 Oct 2014 15:27:55 GMT

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

Re: trouble loading in XML file

pedrolau — Mon, 03 Nov 2014 20:59:26 GMT

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   ===================================//

Re: trouble loading in XML file

Anonymous — Tue, 04 Nov 2014 06:50:56 GMT

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

Re: trouble loading in XML file

mjayachandran — Tue, 04 Nov 2014 07:00:21 GMT

hi alfredo,

Just a suggestion, reading from xml can sometimes not be so easy. Thats why i always execute a vbs script to convert the xml to xls and then read from xls.

---------------------------------------------------

VBS

--------------------------------------------------

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

------------------------------------------------------------------------------

after you read the xls file successfully. you can again execute a command to delete the generated xls file.