Multiple excel files with different headers

[#1]

Hi all,

I have a situation where I need to import multiple files into 1 table.  The files can have different column headers and some include columns others don't etc.

e.g. file 1 might include:

CaseID, Case Name, Case Date

File 2 might include:

CaseID, Case Title, CaseDate, Case Manager

File 3:

Case Number, Case Title, Case Manager

etc

In the above example, I would want my output to have the following columns

CaseID,(which includes values from CaseID and Case Number)

CaseTitle,(which includes values from Case Nameand Case Title)

CaseDate

CaseManager

I've created a static list with the column headers I want in my output and was thinking of having a lookup table which maps all the potential column headers I might get in the files to my preferred list:

e.g

CaseID = CaseID

Case Number = CaseID

CaseTitle = CaseTitle

Case Name = CaseTitle

Case Manager = CaseManager

CaseManager = CaseManager

then I can do some kind of map on the column headers for each excel file to give me the columns I want when it comes to the consolidation of all files.

Is this the best approach?  How can I extract all column headers and rename them before I add each file to the consolidated list?

Many thanks for any advice.

[Gysbert_Wassenaar · Accepted solution]

Perhaps with something like this:

RenameMap:

MAPPING LOAD * INLINE [

Old, New

Case Number,CaseID

Case Name,CaseTitle

CaseManager,CaseManager

];

For Each vFile in FileList('*.xlsx')

     Temp:

     LOAD * FROM [$(vFile)] (ooxml, embedded labels, table is Sheet1);

     RENAME FIELDS USING RenameMap

     STORE Temp INTO $(vFile).qvd (qvd);

     DROP TABLE Temp;

Next vFile

ResultTable:

LOAD * FROM *.qvd (qvd);

[#3]

Hi Gysbert,

This is great, just what I was looking for.  The 2nd issue I have is that some of these column headers have carriage returns in them, e.g.  The column header is

"Case Found?

1) Yes - Match

2) No

3) Possible Match"

(Don't ask me why someone set up an excel with this as a column header!!)

Once I've loaded the data into Temp, can I then loop through the headers and either remove the line feeds or crop the header to the line feed so I can then carry out the Rename Fields mapping?

Thanks again

[Gysbert_Wassenaar]

Instead of an Inline table you can also use an excel file as source for the field mapping table. That way you can simply copy over all the field names, carriage returns and all, from all the excel files and add the new field names that should replace them to that excel file too.

[#5]

This is very helpful.  Thanks again Gysbert