topic Re: Import Multiple CSV Files? in Connectivity & Data Prep

Import Multiple CSV Files?

johnlloyd45 — Mon, 10 Jul 2017 16:08:18 GMT

Hi, I'm completely new to Qlik Sense and am really struggling with working out how to import multiple data files.

My data sets are very simple in that they are two column CSV files with the first column containing the Timestamp and the second column a list of values. Unfortunately all of my data is in separate CSV's so I can have many different files, none of which have headings on the columns; the only unique identifiers are the filenames.

I am able to manually import these files one at a time, type in field-heading names (Timestep for column 1, whatever the filename is for column 2) and associate the files. But this is very time consuming and I'm sure there is a way to script this process.

Can anyone help me out with this?

Thanks!

Re: Import Multiple CSV Files?

Gysbert_Wassenaar — Mon, 10 Jul 2017 17:50:22 GMT

You can try a wildcard load:

LOAD * FROM [LIB://mycsvfiles/*.csv] (txt, codepage is 1252, no labels, delimiter is ',', msq, header)

Re: Import Multiple CSV Files?

johnlloyd45 — Tue, 11 Jul 2017 09:59:16 GMT

Thanks for the suggestion.

That's not given me quite what I'm looking for. It appears to have brought all of the data in to a single table. When I import the CSV files manually they all come in as separate files and I'm able to associate them with each other. This is what I'm trying to achieve.

And any suggestion for automatically naming the field heating using each CSV filename?

Thanks,

John

Re: Import Multiple CSV Files?

Anonymous — Tue, 11 Jul 2017 15:03:45 GMT

I agree with gwassenaar‌!

If the files are identical i.e. the number and name of columns, delimiters, header sizes (where the first row starts), etc are the same, then using a wildcard load should concatenate them into one table.

If your tables are loaded as separate tables, I suggest you to check the above attributes of the files just so that you make sure everything is ready for an automatic concatenation.

Your script should look something like:

Table:

Load

Column1 As TimeStamp,

Column2 As Value

From [lib://ConnectionName\*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

Let us know how it goes,

Laleh

Re: Import Multiple CSV Files?

johnlloyd45 — Tue, 11 Jul 2017 16:26:00 GMT

Hi Laleh,

I don't want to concatenate the files into one table; each CSV file represents a different variable (15 minute readings from different sensors in a building) so I'd like to import them all as separate tables.

Also, each file has different time-steps and row numbers. I could send an example if that helps?

Any ideas?

Also my question on naming Column2 automatically using each CSV file name.

Thanks!

Re: Import Multiple CSV Files?

Anonymous — Wed, 12 Jul 2017 08:14:46 GMT

John,

Sorry for misunderstanding your question!

Right - I think you need a for loop that loops through the files and loads them into individual tables. Pretty much like the code below:

For each vFileName in Filelist ('lib://Path\*.csv')

Load *,

'$(vFileName)' As FileName,

FileBaseName() As Column2

From [$(vFileName)]

(txt, utf8, embedded labels, delimiter is ',', msq);

Next vFileName

FileBaseName() gives you the name of each file being loaded. Not sure if that's what you want but give it a try and see if it works!

Laleh

Re: Import Multiple CSV Files?

johnlloyd45 — Wed, 12 Jul 2017 09:07:51 GMT

Laleh,

Thank you so much for helping, it's really appreciated.

Unfortunately, the new code has again not quite given what I'm looking for. It's imported all of the data but still into a single large table. It also added a third column containing the full library location and filename, and a fourth column titled Column2 containing the File Base Name.

Re: Import Multiple CSV Files?

Gysbert_Wassenaar — Fri, 14 Jul 2017 10:52:10 GMT

Try adding these lines above the script:

QUALIFY *;

UNQUALIFY FieldToBeUsedForAssociatingTheTablesHere;

Re: Import Multiple CSV Files?

johnlloyd45 — Fri, 14 Jul 2017 11:33:57 GMT

That's partly worked; it's now imported my tables separately but it's also created a load of other tables titled INLFED, INLFED-1, INLFED-2....etc. I can't understand what these are referring to

It won't let me Synchronise Scripted Tables so I cannot see the data in the Data Manager and no longer recognises the timestamp as a date field so I cannot use the Calendar Measures.

I really appreciate the help. You can download the script I'm using, the CSV files I'm trying to import and a screenshot of what it's giving me in Data Manager using this link. It'd be great if you could have a look at this and make any sense of it.

Thanks,

John

Re: Import Multiple CSV Files?

Anonymous — Fri, 14 Jul 2017 11:58:06 GMT

John,

gwassenaar‌ means this (I believe):

Qualify *;

Unqualify @2; // replace @2 with the other field if this is not the right field to associate the tables

LOAD

@1 AS Timestep,

@2 AS Value

FROM [lib://Data Connection/*.csv]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

See if this works,

Laleh

Re: Import Multiple CSV Files?

Anonymous — Fri, 14 Jul 2017 12:01:28 GMT

Sorry! Since you aliased @2, it make sense to use the new name in Unqualified:

Unqualify Value;

Re: Import Multiple CSV Files?

johnlloyd45 — Fri, 14 Jul 2017 12:48:35 GMT

Yeah, I'd realised that and tried both the @1 and Timestep references in Unqualified. Sorry, I uploaded the wrong version of the script. Below is what I was actually using.

This gives the same issues as I described in that it won't let me Synchronise Scripted Tables so I cannot see the data in the Data Manager and no longer recognises the timestamp as a date field so I cannot use the Calendar Measures.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD MMMM YYYY';
SET TimestampFormat='DD MMMM YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Qualify *;
Unqualify Timestep;

LOAD
@1 AS Timestep,
@2 AS Value
FROM [lib://RD Test Files/*.csv]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Re: Import Multiple CSV Files?

Gysbert_Wassenaar — Mon, 17 Jul 2017 08:23:34 GMT

Try adding a line with Unqualify * after the loading of the csv files to restore the normal way of naming fields so only the tables generated from the csv files get prefixed field names.

Qualify *;
Unqualify Timestep;

LOAD
@1 AS Timestep,
@2 AS Value
FROM [lib://RD Test Files/*.csv]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Unqualify *;