How to load and extract information from multiple CSV files

[Anonymous]

@Hey guys,

I'm a newbie in the Qlikview world and require your support in resolving 2 queries:

1. I intend to read data into Qlikview from multiple CSV files and correspondingly, use that data to plot a graph.  Each of these files also contain some unwanted header information and the data to be read from all files is also identical

My intention is to load these files, delimit them to remove the unwanted headers and to extract the required data information into separate tables.[For  Y1, Y2]

2. I loaded a single CSV file and tried to extract the required data. The script runs successfully, but I did not receive any information in the corresponding fields. Please find code used for this particular operation

Also, I did not face this problem while importing and carrying out the operation on a XLS file.

Code:

A:
LOAD "X [ms]",Y1,Y2,Y3,Y4,Y5,Y6,Y7,Y8 as Data
FROM
[..\Test.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 17 lines);
B:
LOAD  SubField(Data, ',' ,5) as Relative,
SubField(Data, ',' ,2) as Bit
Resident A;
DROP Table A;

Could anyone suggest the best way to carry out these activities?

Thanks!! .

[marcus_sommer · Accepted solution]

1. Normally the not initialized variable of vFileCounter should return NULL by the first call and rangesum() should just ignore it. Therefore the error-message looked quite odd. Nevertheless you could initialize the variable before the for-loop with something like: let vFileCounter = 0;

2. Here you couldn't use filebasename() else the variable FoundFile maybe in this way:

if left(subfield(FoundFile, '\', -1), 2) = 'AB' then

...

- Marcus

[evan_kurowski]

Suraj Ramachandrappa wrote:

I tried implementing something like this:

If( (Left (FileBaseName(),2 = 'AB')) then

   LOAD

else

   Exit

end if

But, unfortunately it did not work. How would I need to go about tackling this?

Thanks.

Regards,

Suraj

This particular portion is not working because FileBaseName() is not a function available in this context of your looping sequence (has to be used in conjunction with a load statement inside your file-loop).

Instead switch your logic to look for matches in your looping variable $(FoundFile)

$(FoundFile) will be assigned full paths, so when parsing for the file name you have to strip off the last sub-segment in the path tree.

Subfield('$(FoundFile)','\',-1)  //should isolate the FileBaseName + Extension


IF( Left( Upper(Subfield('$(FoundFile)','\',-1)) , 2) = 'AB' THEN  //case-sensitive, maybe throw in an upper for extra-measure

Trace you could give her this;

ELSE

Trace Or you could give her that;

END IF

[evan_kurowski]

Suraj Ramachandrappa wrote:

Hello Evan,

Sadly, it's not a Christmas Card. It's just data fields pulled from a sensor with the data read every 2.5 ms

Thank you for the suggestions and the feedback. But, have some follow queries with the same:

1. Tried loading COUNT(DISTINCT [FileName]). The script runs, but I do not get the variable under which the values are stored as a field after reloading.

Thanks.

Best regards,

Suraj

Regarding this issue... the approach suggested was to use a user-interface expression to calculate your number of distinct input files.  You don't use this in the script portion to assign a variable (you could do this, but my suggestion sidestepped this approach)

If after reloading you have the field [FileName] present in the data model, then create a text box and assign the expression =COUNT(DISTINCT [FileName]).  This should be a readily available count of your inputs, and unless there's some other reason you need the count of files recorded to table and/or variable, there's no rule that says you have to calculate this via scripting.  If you do have to tabulate during scripting, then techniques incrementing the counter per loop pass (even incrementing conditionally whether falling into IF criteria), as Marcus has pointed out, should work fine.

[marcus_sommer]

1. Yes, count() won't work to assign a value to a variable - it's an aggregation-function and worked within the gui or in the script in an aggregation-load with group by clause. Here within the script you could use instead:

let vTotFileCount  = fieldvaluecount('FileName');

2. If there is just one folder you don't need a loop through folders and could just put the path into filedir(). The same is with the extension - with just one extension it's not needed. If there are more than one extension and/or the files have different data-sructure you would need to query which value has the current file to choose within further if-loops or case-statements which file-format and load-statement should be used.

An explicit concatenate-statement is only needed if the data-structure could be differ in some way otherwise the automatic table-concatenation from Qlik would do the job.

- Marcus

[Anonymous]

Hi Marcus,

Yes, your impementation suggestion for counting the number of files worked perfectly.

Have attached a screenshot of the same below:

Also, the conditional check  worked as per my expectations. Using the mid/left/ right functions, can accordingly select the data to be checked:

Thanks a lot for your help.

Best regards,

Suraj

[Anonymous]

Thank you Marcus and Evan for your valuable suggestions!!

Regards,

Suraj

[evan_kurowski]

Marcus Sommer wrote:

An explicit concatenate-statement is only needed if the data-structure could be differ in some way otherwise the automatic table-concatenation from Qlik would do the job.

- Marcus

Mentioning again...

Automatic table-concatenation feature is a beautiful idea, but probably only safe for use if you're in a controlled lab environment, or directly prepare the formats/structures of your own data inputs.

With zero exaggeration, probably every auto-concatenation I've attempted to use in which data inputs were prepared by another party, without exception were eventually disrupted by an unannounced upstream table structure change (either an add, drop, or alias).  If you're going to develop around an auto-concatenation, make sure you get your data providers to commit to a stable format.  It is an amazing technical feature, but sometimes you have to take into account the human element.

~E

[marcus_sommer]

You are right that it need some attention. In my case I control most of my data-sources and usually don't load the rawdata with the wildcard * else with explicit named fields so that the risk of any failure is quite small especially to any unnoticed failures - if a field is missing the script will stop and if there is something new what will break the automatic concatenation I will probably get huge synthetic keys which are also quite unlikely not to notice.

And if I would just fix it ...

- Marcus

[evan_kurowski]

Agree, been through all of those scenarios, many times.