Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
sagarkharpude
Creator III
Creator III

MongoDB data extraction

Hi,

I am trying to pull the data from MongoDB. But I am not getting the data in exact format.

I am getting the data as below :

[{ "Address" : "00:0A:F7:2D:2C:D1", "IpAddresses" : [{ "Address" : "10.180.74.14", "IPSubnet" : "255.255.252.0" }, { "Address" : "fe80::b57d:f0e7:3891:bd21", "IPSubnet" : "64" }] }]

{ "InventorySiteId" : "0c396f1f-2139-4590-8c75-4a47ae8bb407", "SiteComputerId" : "0a604b58-1994-4958-b40f-adc8232b74a5" }

[{ "_t" : "DirectX", "Version" : "4.9.0.904" }, { "_t" : "Antivirus", "Name" : "AVG CloudCare AntiVirus", "State" : "262160" }]

How can I cleanse the data from MongoDB.

Thanks

Sagar

1 Solution

Accepted Solutions
Darren_Ball
Employee
Employee

Hi,

As the REST connector isn't part of the Qlik Web Connectors, I'm unable to comment on exactly how to use it with JSON.

However, in your load script you should be able to write your table to a local file and then using the Qlik Web Connectors' General Web Connector, you can use the GetFile parameter and enter the path of the file you've created.

So, i think if you add this line to your load script:

store mytablewithJSON into myfolderpath\myfilewithJSON.txt (txt);

this will write the contents of the table to a text file.

You can then pick one of the tables from the General Web Connector, for example JsonToTable:

2017-02-20 09_44_12-Qlik Web Connectors _ Qlik General Web Connector.png

Click on parameters and select FILE from the request method and entering the full path in the request URI:

2017-02-20 09_45_19-Qlik Web Connectors _ Qlik General Web Connector.png

Hit run and you should get some script to pop into your Qlik app.

I admit that writing back to disk isn't idea, but this should get you a step closer.

View solution in original post

7 Replies
Gysbert_Wassenaar

That looks like data in a JSON format. If you use the REST connector to pull the data from your mongodb database then the rest connector will convert the json data into tables that Qlikview and Qlik Sense can load.


talk is cheap, supply exceeds demand
sagarkharpude
Creator III
Creator III
Author

Hi Gysbert,

Thanks for the information.

Is there any other way to cleanse the data. Because I think I will need license to use REST connector. We don't have it.

Thanks

Darren_Ball
Employee
Employee

Hi,

What connector or method are you using to extract this data from MongoDB?

As Gysbert suggested the data is coming back in JSON, so if this is how the data is stored then you could, in your Qlik script, save it to a local file and then use the Qlik Web Connectors' General Web Connector, which can read local files and work with JSON.

Or, if the data isn't stored like this in your collection and the format is due to the connection method you are using, then you could try the dedicated Qlik Web Connectors' MongoDB connector.

If you are have the appropriate edition of MongoDB, I believe it's MongoDB Enterprise Advanced, then you can use their dedicated/official MongoDB BI Connector.

Gysbert_Wassenaar

The REST connector is free afaik. You can download it from Qlik Market


talk is cheap, supply exceeds demand
sagarkharpude
Creator III
Creator III
Author

Hi,

I am using below load to pull the data :

LOAD *

FROM

[http://localhost:5555/data?connectorID=MongoDBConnector&table=Collection&server=10.0.230.93&port=279...]

(qvx);

As per Gysbert's suggestion I have installed REST connector as well but don't know how to convert the JSON data to xml or Qlikview understandable format.

Can you please provide me any hint.

Thanks

Darren_Ball
Employee
Employee

Hi,

As the REST connector isn't part of the Qlik Web Connectors, I'm unable to comment on exactly how to use it with JSON.

However, in your load script you should be able to write your table to a local file and then using the Qlik Web Connectors' General Web Connector, you can use the GetFile parameter and enter the path of the file you've created.

So, i think if you add this line to your load script:

store mytablewithJSON into myfolderpath\myfilewithJSON.txt (txt);

this will write the contents of the table to a text file.

You can then pick one of the tables from the General Web Connector, for example JsonToTable:

2017-02-20 09_44_12-Qlik Web Connectors _ Qlik General Web Connector.png

Click on parameters and select FILE from the request method and entering the full path in the request URI:

2017-02-20 09_45_19-Qlik Web Connectors _ Qlik General Web Connector.png

Hit run and you should get some script to pop into your Qlik app.

I admit that writing back to disk isn't idea, but this should get you a step closer.

sagarkharpude
Creator III
Creator III
Author

Hi Darren,

Thanks for the information. But unfortunately we don't have web connector license.

Thanks

Sagar