Loading Data from many table files where a new field intruduced in some

[#1]

Hi

I load lots of daily files (thousands) into QView and there's been a change in the layout which has introduced a new field.

I would like to load this new data but in the load process I get a field not found error as the field doesn't exist in all files.

I'm very new to this - so just a quick question...

Is there a way of loading a field only if it exists

Here's a cut down version of what I am loading (number of XXXX only exists in recent files.

LOAD

Date,
Total_Billable,

NumberOfXXXX

FROM

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

[its_anandrjs · Accepted solution]

Hi,

Try this

LOAD

*
FROM

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

Note:- If file name start with IFilename then use this with * or put Load * for loading all fields in this way is any changes occurs that field also loaded.

Regards

Anand

[#3]

Thanks for that.  Should have thought of that.

There's several hundred fields in there that I don't really want (or need) to load.

In general then is there a way to load only if a field exists?

[jonathandienst]

Hi

Doing a wild card load relies on QV's concatenation rules, so you will land up with at least 2 final tables. You will need to be more explicit using a For Each loop, like this:

For Each VFile In ('Z:\Finance\IFile\IFilename*.*')

  // Ignore errors
  Set ErrorMode = 0;

  // Try loading the file with the extra field
  T_Data:
  LOAD
       Date, 
       Total_Billable,
       NumberOfXXXX
  FROM [$(vFile)]
  (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

  // Reset error mode to normal
  Set ErrorMode = 1;

  // Is T_Data empty (or not created)? 
  // Alt() prevents null if the table does not exist
  If Alt(NoOfRows('T_Data'), 0) = 0 Then

       // Now try loading the file without the extra field
       T_Data:
       LOAD
            Date, 
            Total_Billable,
       FROM [$(vFile)]
       (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

  End If

  // Is this the first load into Data?
  If Alt(NoOfRows('Data'), 0) = 0 Then

       // NoConcat to prevent Data being lost into T_Data through auto concatenate
       Data:
       NoConcatenate
       LOAD * Resident T_Data;

  Else

       // Do a forced concatenation as the field list will not always contain the extra field
       // (cannot rely on auto concatenation)
       Concatenate(Data)
       LOAD * Resident T_Data;

  End If


  Drop Table T_Data;

Next vFile

HTH

Jonathan

[#5]

Thanks.  Appreciate the detail.

As mentioned I'm new (or rather old but inexperienced).  The earlier answer will provide it and yours gives me the option of something to play with to test out.

Thanks so much

[its_anandrjs]

Yes you are right, and you can drop some of the fields if it is not required for the load

LOAD

*
FROM

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

Drop fields Field1,Field2;

Regards

Anand

[#7]

Thank you.  I've learned something else as well.  Didn't know I could drop fields.

That's v useful