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

[varmekontrol]

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.

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

[felipedl]

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

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