Need to load all the CSV files from specific folder inspite of issue in on of the file

[onkarkulkarni]

Hi Experts,

I am working on a dashboard issue. I am loading multiple country level sales files as input to dashboard.
E.g India_sales.CSV
    UK_sales.CSV
China_Sales.CSV and so on.

Each file has same columns -  Country, Year, Sales.

My simple load script is like:
Load
Country, Year, Sales
from C:\Users\Desktop\ABC\*_sales.CSV

Now the issue that I am facing is - if any file has column header missing or any specific column missing then complete load fails and none of the file gets loaded.
Solution that I am expecting is - If there is issue in 1 out of 10 files then 9 files should get loaded and I should be able to identify the file with issue.

Thanks in advance

Regards,

Onkar Kulkarni

[tomasz_tru]

I would make sure data quality is as good as it could be. Otherwise:

1. Load file names into table:

file_list:
LOAD distinct filename( ) as _file
FROM [lib://*_sales.CSV];

2. loop file_list table

     3. check if column names are correct (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/TableFunctions...

     4. if column names are correct - load the data

     5. if not log error

[evan_kurowski]

//Option description -- specify specific columns to go after, ignore errors, & report row counts

//Create a placeholder to append to
[ALL_COUNTRIES_SALES]:
NOCONCATENATE LOAD 1 AS Sales AUTOGENERATE(0);

[LOAD_STATS]:
NOCONCATENATE LOAD 'Atlantis' AS COUNTRY AUTOGENERATE(0);

Set ErrorMode = 0;  //don't stop the script if your attempt to read a specific file doesn't succeed

For Each vFile In FileList('csv\*_SALES.csv')

Let vPriorRowPass = NoOfRows('ALL_COUNTRIES_SALES');
Let vCountry = Subfield('$(vFile)','\',-1);

CONCATENATE('ALL_COUNTRIES_SALES')
LOAD
'$(vCountry)' AS COUNTRY,
Dept,
Rep,
Year,
Sales
FROM
[$(vFile)]
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);

IF NoOfRows('ALL_COUNTRIES_SALES') = $(vPriorRowPass) THEN
TRACE PROBLEM! You didnt read any rows from $(vFile);
ELSE 
TRACE SUCCESS! You got some data from $(vFile);
END IF 

CONCATENATE('LOAD_STATS')
LOAD '$(vCountry)' AS COUNTRY,
NoOfRows('ALL_COUNTRIES_SALES') - $(vPriorRowPass) AS NUM_ROWS_LOADED AUTOGENERATE(1);


NEXT //vFile

[onkarkulkarni]

Thanks a lot Evan for detailed solution.

However I am little confused on implementing this code in my script.

Should I define the variables before I execute script?  I guess the solution also provides the Count of records in each file. Right now my basic requirement is to load all error free files  and find the file with error. Could you please help me modified my script that I posted in my query above ?

Thanks again.

[evan_kurowski]

onkar kulkarni wrote:

Thanks a lot Evan for detailed solution.

However I am little confused on implementing this code in my script.

Should I define the variables before I execute script?  I guess the solution also provides the Count of records in each file. Right now my basic requirement is to load all error free files  and find the file with error. Could you please help me modified my script that I posted in my query above ?

Thanks again.

Hello onkar,

In Qlik scripting, variables can be defined in your load script, and using this method will create the variable in that particular application the first time the line defining the variable is executed.  After creation, the variable is persistent and will remain even if you comment or remove the line originally defining it.  Once created, you can reassign its value either in script or on the user interface, interchangeably. (you can also create variables via the user interface. variables are not treated differently whether created via script or ui)

So for example, the code lines:

Let vPriorRowPass = NoOfRows('ALL_COUNTRIES_SALES');
Let vCountry = Subfield('$(vFile)','\',-1);

When the script runs these lines of code the first time, it creates variables vPriorRowPass & vCountry.  After that, these variables persist in the .QVW.

The script posted earlier creates the table [LOAD_STATS], and this is what you look to for error conditions.

[LOAD_STATS] should have a row for every input file consumed from your source directory and report the number of rows imported per each file. 

After each reload, any rows in [LOAD_STATS] where the field [NUM_ROWS_LOADED] = 0 was a problematic load and should be reviewed (because it retrieved 0 rows of data).

[llauses243]

WROTE:

...
Dept,
Rep,
Year,
Sales
...

MUST WRITE:

/*
Dept,
Rep,
Year,
Sales
*/
* //ALL FIELDS FOUNDED

GOOD LUCK

[llauses243]

solution:  replace some lines by one line see adjust image, good luck