Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
i have a report that pulls in any txt file from a specific file location, parses out the relevant data and then displays it. currently its configured to then store the information into a csv.
however, i now need it to save a csv for each txt file it reads in and to keep the original name.
so in my folder i will have txt file: A, B, C..
do i need to do something like
"for each FolderName in ('\\folder\*.txt')
for each File in filelist (FolderName)
details:
load * from '\\folder\*.txt'
;
Store...? into [csv] (txt);
how do i get Qlik to save a csv for each txt file it reads in and keep the name? do i need to do a loop to grab a txt file one at a time?
any help would be appreciated
thanks
I think you need to do a loop and process each file. Something like:
For each vfile in filelist('\\folder\*.txt'
details:
load * from $(vfile) ...;
let vfile = replace('$(vfile)', '.txt', '.csv');
Store details into $(vfile);
Drop table details;
Next vfile;
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
I think you need to do a loop and process each file. Something like:
For each vfile in filelist('\\folder\*.txt'
details:
load * from $(vfile) ...;
let vfile = replace('$(vfile)', '.txt', '.csv');
Store details into $(vfile);
Drop table details;
Next vfile;
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
hi @rwunderlich
thanks a million for that, using your example i was able to store the txt files as csvs. however that stores them in the same location where the txts are stored and also not in the correct format.
i am doing some manipulation of the txt files in my Details load, only bringing in certain values e.g.
[@12:32] as [Ac Desc]
when i go to open the new csv files created, it doesn't display what is displayed in my Details table in my qlik app, the csv files look like:
the ideal scenario is that i parse out the information that i need from each txt file via my Details Load, it then saves all the csv files in a different file location with the parsing done and then it still displays the Details table in the qlik app for users...
is this possible?
i tried just changing the "Store details into .." to a different folder location but i get an error,
thanks!
The Store is generating qvd files, which is the default. To create csv files, you need to add "(txt)".
Store details into $(vfile) (txt);
With regards to the location, it should just be a matter of building up the string correctly. Something like:
For each vfile in filelist('\\folder\*.txt'
let vBasename = SubField(SubField('$(vFile)', '/', -1), '.', 1);
details:
load * from $(vfile) ...;
Store details into [lib://newloc/$(vBasename).csv] (txt);
Drop table details;
Next vfile;
@rwunderlich thanks for your response,
i put in the following:
for each vfile in filelist ('\\...Inbox\*.txt')
let vBasename = SubField(SubField('$(vFile)', '/', -1), '.', 1);
Details:
load ...* from $(vfile) ...;
let vfile = replace('$(vfile)', '.txt', '.csv');
Store Details into [\\..Outbox\$(vBasename).csv] (txt);
Drop table Details;
Next vfile;
however when i go to view the outbox folder after the qlikview app is reloaded, there are no csv files present. the app runs fine and looks like it is processing each txt file,
any ideas what im doing wrong here?
Take a look at the script log to see what is happening in the loop.
-Rob
it looks like an issue with my store details?
its picking up the txt file name, but not saving it as a csv name?
it doesn't look like it pulling in the vBaseName that i set? its just trying to save it as a \csv? would that be correct?
thanks,
It's hard to say without seeing the log that sets the vBasename variable.
-Rob
@rwunderlich ah apologies,
here is a screensnip from my log file showing the first time vBasename is set:
I typed the expression with a "/" it should have been a "\".
let vBasename = SubField(SubField('$(vFile)', '\', -1), '.', 1);
I also see that your $(vFile) value is not showing up in the log. You are spelling it "vfile" (lower case f) in the foreach. Variables are case sensitive so make sure you are using the same case in both the foreach and $().
-Rob