Variations in names on a batch load

[#1]

Hi,

Im not sure of the terminology for this type of load but I have hundreds of files in a folder that are all formatted the same with a defined naming function that indicates the week they relate to. I do a load where I replace the file name with a * and it loads all of the files in that folder... possibly a wild card??

e.g. FROM (ansi, txt, delimiter is '\t', embedded labels, msq);

The problem I have come across is that one of the columns was previously called "Sales" in older reports and it has changed to "Sales ($)" in the last few weeks.

If it was just an internal report I would make sure they were uniform but as they are reports downloaded from an external source I need to find a way around it.

Below is the load, is there a way to tell it to load Sales if the file has Sales as the heading or Sales ($) if thats the heading.

LOAD State,
[Store] as Location,
Product,
Barcode as Barcode#,
Sales
FROM (ansi, txt, delimiter is '\t', embedded labels, msq);

I tried adding a second line under Sales as "Sales ($) as Sales" but that didnt work.

Any ideas?

Thanks

David

[#2 · Accepted solution]

how about something like

if(isnull(Sales),[Sales ($)], Sales) as Sales

[#3]

dragonauta wrote:if(isnull(Sales),[Sales ($)], Sales) as Sales

Hi Dragonauta,

When I use this I get an error that Sales ($) doesnt exist when it starts with the reports where its called Sales

David

[GabrielAraya]

Hi ... I think that the solution is very simple:

When you choose a XLS file to read with QVW in the script, you need to choose Labels: NONE in the "File Wizard", the name of your columns will be: @1, @2 .. etc and you need to drop the first rows that have the name of the columns, for example:

Directory;

LOAD

@1 as FieldName,

@2

,

@3

,

@4

,

@5

,

@6

,

@7

FROM [Llamadas_92380625 Ago.xls] ( biff, no labels, table is [Detalle de Llamadas$], filters (

Remove

(Row, Pos(Top, 1 )) )) ;

So, that way is not matter the name of the columns in your original XLS files ..

Gabriel

[#5]

Hi Gabriel,

Sometimes the simplest solution is the best, thanks for that

David