Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have these KML files generated automatically with different filename depend on time generated. I have no problem to load each of the files, but is there any way to load all the files automatically using modified load script so I can just set the load task in Management Console without manually adding new kml file first?
Hi QlikCsgg,
I found all KML files didn't have any common field, so then I remove Concatenate command and put some declaration to change the field name into 1 name. Then I add (kml) command after FROM line to get only the KML content. Finally these are my latest working load script:
LET vRoot = 'lib://Oil/';
SUB DoDir (Root)
FOR each FILE in FileList(Root & '*.kml')
SET vPath = [$(FILE)];
LET vFileName = mid(vPath,11,67)&'.Area';
OilPolygon:
LOAD $(vFileName) as Polygon
FROM [$(FILE)]
(kml);
NEXT FILE
END SUB;
CALL DoDir(vRoot);
You can edit the load script and use a star (*) as a wildcard to load multiple files with the same structure in one go .
So edit your current load script and remove parts of the file name that is different and replace it by *.
location*.kml
Hi Yan
You can use the following script:
First define a variable containing the path to the kml files.
Let vRoot = '..here add the path to where your kml-files are located....';
SUB DoDir (Root)
FOR each FILE in FileList(Root& '\My_kml_File_from_date*.kml')
Concatenate
LOAD
*
FROM [$(FILE)];
NEXT FILE
END SUB;
CALL DoDir(vRoot);
Best regards
Hi QlikCsgg,
These are my script version :
Let vRoot = 'lib://TestLoad/ftp\shp_oildetection';
SUB DoDir (Root)
FOR each FILE in FileList(Root& '\*.kml')
Concatenate
LOAD *
FROM [$(FILE)];
NEXT FILE
END SUB;
CALL DoDir(vRoot);
And this are the result : "Data has not been loaded. Please correct the error and try loading again."
I noticed you not using table attribute, is it correct?
Hi Yan
Table attribute should not be an issue here. The problem you ran into is that in Qlik Sense file path descriptions use "/" instead of "\". In Qlikview, however, you would normally use the "/". Thus, when you replace
Let vRoot = 'lib://TestLoad/ftp\shp_oildetection'; ... and ... FOR each FILE in FileList(Root& '\*.kml') ... to
Let vRoot = 'lib://TestLoad/ftp/shp_oildetection'; ... and ... FOR each FILE in FileList(Root& '/*.kml')
then this should work in Qlik Sense.
Best regards
Hi QlikCsgg,
So now I've change the part you mention thus here are the result :
Let vRoot = 'lib://TestLoad/ftp/shp_oildetection';
SUB DoDir (Root)
FOR each FILE in FileList(Root& '/*.kml')
Concatenate
LOAD *
FROM [$(FILE)];
NEXT FILE
END SUB;
CALL DoDir(vRoot);
But the error are still the same message. Is there any log or note that have detail information of the error?
Hi Yan
Let's get then to the starting point. Are you using Qlikview or Qlik Sense?
If you're using Qlikview, then you need to define your variable vRoot with a proper file path to where your kml-files are stored, e.g.
let vRoot = 'C:\Users\geomarketing\Documents\Qlikview\010_rawdata\'
and the variable FileList(Root&'\Customer_with_GeoLoc_20*.kml')
If you're using Qlik Sense, then you need to define first a connector within your QS application, which you define as a path to a file folder, e.g.
C:/Users/geomarketing/Documents/QlikSense/010_rawdata/ and as a Library-Name you could use "KML_File_Folder".
The variable vRoot would then be
let vRoot ='LIB://KML_File_Folder'
and the variable FileList(Root&'/Customer_with_GeoLoc_20*.kml')
Of course, when you let this script run as a scheduled Management Console "job", you need to grant the "User of the Management Console" (in most cases this is a technical user) to have access rights to the folder where your kml-files are stored.
That should work - at least it does with my setup. Hope this works for you too.
Best regards
Hi QlikCsgg,
I found all KML files didn't have any common field, so then I remove Concatenate command and put some declaration to change the field name into 1 name. Then I add (kml) command after FROM line to get only the KML content. Finally these are my latest working load script:
LET vRoot = 'lib://Oil/';
SUB DoDir (Root)
FOR each FILE in FileList(Root & '*.kml')
SET vPath = [$(FILE)];
LET vFileName = mid(vPath,11,67)&'.Area';
OilPolygon:
LOAD $(vFileName) as Polygon
FROM [$(FILE)]
(kml);
NEXT FILE
END SUB;
CALL DoDir(vRoot);
Hi Yan
So, your solution now works? or do you have some issues still open?
If the solution now works, it would be great, if you would mark your question as answered. 🙂
Best regards
Hi QlikCsgg,
It solved now, thank you very much for the guidance
Best Regards