Adding fields to source as months go by

[vvvvvvizard]

Quite a silly question

Each month i load in am excel file containing Column A , B , C  for month 1 , 2 , 3 ...

as the months pass on by , sometime additional columns get added A , B , C ,D E , month 4 , 5 , 6

Is it possible to load all of these with one load statement .  I currently have one load statement for month 1 , 2, 3 and then an  outer Join statement to table to load month 4, 5, 6 and each time new columns get added , i have a new outer join and a new load statement ect ect ect , now there's just to many outer joins and loads

How can i load them all with one Load statement .

[jagan]

Hi,

Instead of specifying columns in the Load statement, directly use * in the load statement so that all columns will be loaded.

Example:

LOAD

*

FROM FileName.xls;

Hope this helps you.

Regards,

Jagan.

[jonathandienst]

Data like this should be loaded with a cross table load- this will give two fields - a month (as data), and a value -- rather than a field for each month. You have already encountered some of the problems with the latter approach.

Load something like this:

Crosstable(Month, Value, <n>)

LOAD *

FROM .......xlsx

(ooxml, ....);

(replace <n> with the number of attribute columns before the first month column). The results table will contain the <n> attribute fields, a Month field (which will contain the column header in the spreadsheet) and a Value field. The * in the load means it will load whatever months are in the file.

[vvvvvvizard]

well then you get many tables with lots of sythetic keys , the source files with extra columns get loaded as Table1 , those with more columns as Table 2 , table 3  , ect

Try

Table1:

Load *

with 2 excel sheets , one with columns A , B and other with A, B, C

you get 2 tables instead of one ,

[vvvvvvizard]

Apologies for not giving enough info , i currently get the date field from the file name of the excel worksheet . All the data in the worksheet is in a tabular format (not in cross table format) .

[datanibbler · Accepted solution]

Hi,

I have this exact issue in an app which I built a while ago. Every month, one column is added. My solution:

- First I do a FIRST 1 LOAD with the * so that I get all the names of all the fields.

- I have a loop starting at the first row which I am not certain about anymore and counting from there the number
   of months still to come (say it runs from 37 to (37+8=)44)

- In the LOOP, the names of the fields are identified and

     - I query whether they begin with some letters leading me to believe there is useful data in there

          - I have a variable reading like > v_field_37 < and one like > AS_v_field_37 <

          - If they DO contain these letters, then v_field_37 is filled with the actual name and AS_v_field_37 with
            a logical alias_name
            <-> otherwise, both are filled with the text "dummy_37"

- Then comes the actual LOAD where I load all these fields, whether there is useful monthly_data in there or
    not.

- After the LOAD, I have another LOOP where I parse all the fields in the result_table again, beginning at the first one
   I'm not sure about anymore and again counting up for all the months to come
   => When the field_names start with "dummy", I just delete them again.

That's it in short. You'll have to do quite some trying out and testing. So did I, that thing with the LOOPs is not exactly easy.

HTH

[vvvvvvizard]

Sounds like a plan , il test and advise