1 Reply Latest reply: Dec 19, 2017 1:56 PM by Felip Drechsler RSS

    Dataload - Importing only specific information from a list with the specific information.

    Daniel Sørensen
    Daniel Sørensen Dec 19, 2017 8:08 AM

      HI

       

      I am looking for a solution were the Dataload look in a folder, and only loads the specific information that is listed in another file that is also included in a data load for the same app.

       

      Every day I receive about 10.000 .csv files. (containing data from Meters and Sensors)

      The 10.000 .csv files are divided into 5 categories, that have a unique number for that category, and then include the timestamp that the file was created.

       

      1. ColdWaterMeter (0006044884_55148549_valuereport_20170829043827_2105.csv)
      2. HeatMeter (0006044884_68205337_valuereport_20170829043144_2105.csv)
      3. HotWaterMeter (0006044884_55148560_valuereport_20170829043655_2105.csv)
      4. SensorHumidityTemperature (0006044884_00011310_valuereport_20171116133543_2105.csv)
      5. SensorWindowDoor (0006044884_00011315_valuereport_20171116133450_2105.csv)

       

      Each of these categories contains their own buildup of rows and information. My plan is to make 5 different load script, 1 for each category.  And that would be pretty easy to do if there were only 1 file pr. category, and the unique value i would use for the "where" function is a value that could change from one day to another. (if the meter or sensor is replaced)

       

      But my problem, and also the reason for this question is that it is not just 1 file pr. category, it is 2.000 files pr. category on a daily basis, 2.000 individual files (1 daily file for one unique meter number)

       

      So I was hoping that it was possible to make a dataload, where the dataload would look in a Meterlist file, to locate what files to load.

       

      Udklip.JPG

       

      I got confused a couple of times writing this question, so I would perfectly understand if you did as well. 

      • 56 Views
      • Tags:
        • Re: Dataload - Importing only specific information from a list with the specific information.
          Felip Drechsler
          Felip Drechsler Dec 19, 2017 1:56 PM (in response to Daniel Sørensen)

          Hi Daniel,

           

          I've built something real quick that might be what you need.

          The attached zip file contains 3 things:

          - Meter_Parameter.xlsx contains the parameters you want to use

          - Folder with some subfolders and csv files to simulate your needs

          - QVW containing the load code

           

          Extract all to a folder you like and run the QVW.

          A brief description of the code would be

           

          The Meter_Parameter file fields are:

          MeterList, MeterMeasure and MeterUtility are your defined values

          Folder is the subfolder inside the provided 'Sample Directory' folder.

           

          // Loads the parameters from Meter_Parameters

          data:

          LOAD

               // Key field is used to get complete filename to search for

               MeterList & MeterMeasure &  Folder as Key,

               MeterList,

               MeterMeasure,

               MeterUtility,

               Folder

          FROM

          [C:\Users\felipe.lourenco\Desktop\Meter_Parameters.xlsx]

          (ooxml, embedded labels, table is Plan1);

           

          // for all parameters contained in the Meter_Parameters file

          for i = 0 to NoOfRows('data')-1

           

          // gets fields from the above loaded table

          let vLookUpDirectory = peek('Folder',$(i),'data');

          let vLookUpUtility = peek('MeterUtility',$(i),'data');

          let vLookUpKey = peek('Key',$(i),'data');

           

          Data:

          LOAD

          '$(vLookUpKey).csv' as FileOrigin,

          Order,

               Date,

               Supplier,

               Part,

               Quantity,

               Value,

               Utility

          FROM

          // dollar ($) expansion is used to get the specified file defined by vLookUpKey in vLookUpDirectory

          [Sample Directory\$(vLookUpDirectory)\$(vLookUpKey).csv]

          (txt, codepage is 1252, embedded labels, delimiter is ';', msq)

          // condition to filter the current file being loaded.

          where Utility = '$(vLookUpUtility)';

           

          next i;