Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

johnlloyd45
New Contributor

Import Multiple CSV Files?

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!

12 Replies

Re: Import Multiple CSV Files?

You can try a wildcard load:

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


talk is cheap, supply exceeds demand
johnlloyd45
New Contributor

Re: Import Multiple CSV Files?

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

laleh-haidari
New Contributor III

Re: Import Multiple CSV Files?

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

johnlloyd45
New Contributor

Re: Import Multiple CSV Files?

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!

laleh-haidari
New Contributor III

Re: Import Multiple CSV Files?

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

johnlloyd45
New Contributor

Re: Import Multiple CSV Files?

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.

Capture.PNG

Re: Import Multiple CSV Files?

Try adding these lines above the script:

QUALIFY *;

UNQUALIFY FieldToBeUsedForAssociatingTheTablesHere;


talk is cheap, supply exceeds demand
johnlloyd45
New Contributor

Re: Import Multiple CSV Files?

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

laleh-haidari
New Contributor III

Re: Import Multiple CSV Files?

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

Community Browser