Skip to Content
0
Former Member
Mar 08, 2013 at 01:52 PM

Creating a query in Data Services using headers that ignores column order

280 Views

Hello all!

Our company gets lists of customer data (in excel-files) on a daily basis that needs to be imported into the database. The target database fields stay the same each time but the source lists have their data in random order. I'm trying to create a query in Data Services Designer that maps the columns based on column headers and ignoring the column order. The headers also change somewhat so just re-importing schema each time would create invalid mappings.

Examples of source list headers:

list 1 headers: firstname, lastname, address, phone

list 2 headers: primary_address, first_name, last_name, mobile_phone

list 3 headers: name-first, name-last, phone

(really there are dozens of lists with columns in different order)

I'm trying to map the right columns to right database fields:

Target Datase:

firstname -> map: firstname in list 1, first_name in list 2, name-first in list 3

lastname -> map: lastname in list 1, last_name in list 2, name-last in list 3

etc.

Right now I have two sources joined:

headers (just the headers row for source list)

data

And Mapping-tab for firstname has

ifthenelse(headers.F1 = 'first_name', data.F1,

ifthenelse(headers.F2 = 'first_name', data.F2,

ifthenelse(headers.F3 = 'first_name', data.F3,

ifthenelse(headers.F4 = 'first_name', data.F4,

null

))))

This kind of data handling is very slow so I was wondering if there was a more efficient way to do this?