topic Re: Loop through multiple csv files and store into qvd for each csv file dynamically in QlikView

Loop through multiple csv files and store into qvd for each csv file dynamically - Qlik Sense

sudhakar_budde — Sat, 16 Nov 2024 04:53:08 GMT

Hi,

I have a requirement to load multiple monthly csv files from a directory and store as separate qvd for each file.

I have created a variable for the latest YYYYMM and can load all the records into a single qvd. But I wanted to load each csv into a qvd.

E.G:

Productinformaion_8900752613_28500_20180702_01.csv
i.e: Productinformaion_productnumber_categorycode_YYYYMMDD_01.csv

I have seen some posts on the community for Qlik view but those are not working in Qlik sense!

For example, there is a script with

ODBC connect to 'path ';

Exceltables:

SQLTABLES;

DISCONNECT;

For I = 0 to NoOfRows('Exceltable')-1;

Load *

…..

;

Can anyone suggest a solution please?

Thanks

Re: Loop through multiple csv files and store into qvd for each csv file dynamically

skamath1 — Thu, 27 Dec 2018 16:55:59 GMT

Try the For each option in the script.

Review the help available with examples

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptControlStatements/For%20Each.htm

Re: Loop through multiple csv files and store into qvd for each csv file dynamically

sudhakar_budde — Thu, 27 Dec 2018 17:57:29 GMT

Hi,

Thanks for your response.

I tried similar script, but it's not working!

Thanks

Re: Loop through multiple csv files and store into qvd for each csv file dynamically

skamath1 — Thu, 27 Dec 2018 18:39:03 GMT

Below is the example that worked for me. You tweak the code as required

FOR Each vFile in filelist ('*sales_fact.csv')
FactSales:
LOAD

OrderDate, Customer,Product,Sales,Qty
,FileName() as FileName
FROM
$(vFile)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Next vFile

Store FactSales into FactSales.qvd (qvd) ;

Re: Loop through multiple csv files and store into qvd for each csv file dynamically

sudhakar_budde — Fri, 28 Dec 2018 10:13:44 GMT

Hi,

I have achieved the solution that I wanted by using the below script:

//***This script helps to load each csv file dated Current Month in its name from a source folder and store as a separate qvd in another folder. ***//

//**======================================================================

// ***Define the Path ****

LET vFilePath = 'lib://Sales Source Files/';
Let vQvdPath = 'lib://Sales Qvds/';
Let vCurrentMonthYear = Date(Today(),'YYYYMM');

//*** Start of the For loop ****

FOR EACH file in FileList('$(vFilePath)\Sales_*$(vCurrentMonthYear)*.csv'); ///***** the back slash '\' in the path is important to notice here ***** ///

SalesData:
Load
@1 AS Product_Name,
@2 AS Unit_Price,
@3 AS Location,
@4 AS Quantity,
@5 AS "Total Amount",
Left(filename(), 63) as filename ///**** I wanted certain details in the file name so used appropriately ****//
From
[$(file)]

(txt, codepage is 28591, no labels, delimiter is ',', msq, header is 2 lines) //*** I have to skip 2 header lines for my requirement ***//

;

Let vFileName = Peek('filename');
Store SalesData into [$(vQvdPath)$(vFileName).qvd] (qvd); // *** Storing as separate qvds ***//

Drop Table SalesData;

Next; // *** End of For loop ***//
Exit Script;

//============================================================================

I hope this might help some other community members!

Thanks

Re: Loop through multiple csv files and store into qvd for each csv file dynamically

sudhakar_budde — Fri, 28 Dec 2018 10:15:38 GMT

Hi Skamath,
Thanks for your response.
Much appreciated for your efforts to help me out!

Thanks
SB