Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have XML on http (API) which have authentication.The XML contain multiple -nested Tag.I am trying to import XML to Qlik.There are more then 60000 XMLs.
I am trying to load few Attributes to a table ,but I am facing an issue as the API is password protected.How to pass UserID and Password to get the data.
Example:
GeocodeResponse:
// [Attributes_Name]
BookID,BookName,Author
FROM [
http://maps.googleapis.com/maps/api/geocode/xml?address=InputLocation&sensor=false", False] (XmlSimple, Table is [GeocodeResponse]);
sleep 500;
Error: "Access is denied."
How to pass USerID and Password?
Regards,
Deepika
this might be helpful:
Hi Frank,
Thank you for the reply.
Now I am able to connect respective URL with UserID and Password.
http://UserID:Password@maps.googleapis.com/maps/api/geocode/xml?address=InputLocation&sensor=false
Solved !
Now I am trying to import data from XML to Qlik.
Attached a sample XML file.
The file comtain multiple tags with nested tags.
Regards
Deepika
i guess QV is not recognizing your file as xml:
Through above link I am getting error for Root Tag.
But in my file I have Root tag
Root --> Articles --> Article --> so on
Hi,
I am able to import XML file in Qlik using "Table Files" option. 🙂
But I am facing issue as few data are missing .
Structure of XML
Article
--> Feature
--> Complex
--> LeafElement
--> Simple
i,e 3 nested tages
Using"Table Files" I am able to till Complex tag only
Say Complex Tag contain 9 LeafElement,but in Qlik it extract only one LeafElement and not all 9 LeafElement.
When I import this XML in Excel ,I get all the Tages but not in Qlik.
if you erase the hyphens in the xml then QV will recognize the file as xml.
then you should be able to read the file into QV as xml without loosing structure/information.
hope this helps
Thank Frank!
I downloaded XML files from a Site using VBA
Sub DownloadFile()
Dim MainURL As String
Dim DOCURL As String
Dim RowCount As Long
Dim Articleno As String
Dim VariantNo As String
Dim BundleNo As String
Dim r As Integer
MainURL = "MainULR"
Dim WinHttpReq As Object
RowCount = Sheets("ListOfDoc").UsedRange.Rows.Count
For i = 1 To RowCount
If ThisWorkbook.Sheets("ListOfDoc").Cells(i, 2).Value = "" Then 'checking a flag
DOCURL = MainURL & ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value
Articleno = ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value
Articleno = Mid(Articleno, InStr(Articleno, "mmsArtNo=") + 9, InStr(Articleno, "&variantNo=") - (InStr(Articleno, "mmsArtNo=") + 9))
VariantNo = ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value
VariantNo = Mid(VariantNo, InStr(VariantNo, "&variantNo=") + 11, InStr(VariantNo, "&bundleNo") - (InStr(VariantNo, "&variantNo=") + 11))
Language = Right(ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value, 2)
BundleNo = ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value
BundleNo = Mid(BundleNo, InStr(BundleNo, "&bundleNo=") + 10, InStr(BundleNo, "&language=") - (InStr(BundleNo, "&bundleNo=") + 10))
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", DOCURL, False, "deepika.shinde", "Pass-word-24"
WinHttpReq.send
'DOCURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.responseBody
oStream.SaveToFile ThisWorkbook.Sheets("Details").Range("B2").Value & "\" & Articleno & "_" & VariantNo & "_" & BundleNo & "_" & Language & ".xml", 1 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If
ThisWorkbook.Sheets("ListOfDoc").Cells(i, 2).Value = 1 'Setting a flag
End If
Next i
End Sub
Let vRoot = '*.xml';
Let i = 1;
for each vFile in filelist('*.xml')
TRACE i ;
Then import XML files into Qlik:
Code:
Article:
LOAD
FileBaseName() as ArticleNo_A,
PLKZ,
ART_NO,
VAR_NO,
BUNDLE_NO,
SUBSYS_NO,
MISC_ID,
PDF_URL,
[Features/SimpleFeature/MISC_feature_ID] as [SimpleFeature/MISC_feature_ID],
[Features/SimpleFeature/MISC_feature_Label] as [SimpleFeature/MISC_feature_Label],
[Features/SimpleFeature/MISC_feature_SortId] as [SimpleFeature/MISC_feature_SortId],
[Features/SimpleFeature/MISC_feature_Uom] as [SimpleFeature/MISC_feature_Uom],
[Features/SimpleFeature/MISC_feature_Value] as [SimpleFeature/MISC_feature_Value],
[Features/SimpleFeature/MISC_feature_type] as [SimpleFeature/MISC_feature_type],
[Features/SimpleFeature/MISC_feature_norm_label] as [SimpleFeature/MISC_feature_norm_label],
[MISC_Properties/MISC_Language] as MISC_Language,
[MISC_Properties/MISC_ComText2] as MISC_ComText2,
[MISC_Properties/MISC_ComTextWebshop] as MISC_ComTextWebshop,
FileBaseName() & %Key_Article_CCAD92DEFFCA5924 as Key_Article // Key for this table: Root/Articles/Article
FROM $(vFile) (XmlSimple, Table is [Root/Articles/Article]);
i = i + 1; |
Next vFile
But above process takes around 16 hours for approx 79000 records (7 hours to download approx 79000 xml files and 9 hours to import 79000 xml files to Qlik).
Is there any other way which will be more faster than this.?