12 Replies Latest reply: Jul 17, 2017 4:23 AM by Gysbert Wassenaar RSS

    Import Multiple CSV Files?

    John Lloyd

      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

          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?
              John Lloyd

              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?
                  Gysbert Wassenaar

                  Try adding these lines above the script:

                   

                  QUALIFY *;

                  UNQUALIFY FieldToBeUsedForAssociatingTheTablesHere;

                    • Re: Import Multiple CSV Files?
                      John Lloyd

                      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?
                          laleh haidari

                          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?
                              laleh haidari

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

                               

                               

                              Unqualify Value; 

                                • Re: Import Multiple CSV Files?
                                  John Lloyd

                                  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

                                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 *;

                        • Re: Import Multiple CSV Files?
                          laleh haidari

                          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?
                            John Lloyd

                            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?
                                laleh haidari

                                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?
                                John Lloyd

                                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