Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
JohnSamuel123
Creator
Creator

Saving each txt file as a csv

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

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

11 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

JohnSamuel123
Creator
Creator
Author

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:

JohnSamuel123_0-1633507529954.png

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!

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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;

JohnSamuel123
Creator
Creator
Author

@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? 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Take a look at the script log to see what is happening in the loop.

-Rob

JohnSamuel123
Creator
Creator
Author

it looks like an issue with my store details?

JohnSamuel123_0-1633553925464.png

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,

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

It's hard to say without seeing the log that sets the vBasename variable. 

-Rob

JohnSamuel123
Creator
Creator
Author

@rwunderlich  ah apologies,

 

here is a screensnip from my log file showing the first time vBasename is set:

JohnSamuel123_0-1633623501519.png

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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