Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 *
…..
;
Next
Can anyone suggest a solution please?
Thanks
SB
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
SB
Try the For each option in the script.
Review the help available with examples
Hi,
Thanks for your response.
I tried similar script, but it's not working!
Thanks
SB
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) ;
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
SB