question

Gary P avatar image
Gary P asked

How do I use "friendly" field names in a query against raw imported data fields?

I am importing several years of quarterly reporting data on companies from several different data sources (1 CSV file per quarter). The raw CSV has ugly field names (e.g. "RKFLD345") that vary from source to source. I also have a single table with general company info, like address, website, industry sector, etc.

I want to keep my SQL queries clean with "friendly fields" in my graphing/reporting tools.

Is there an elegant way to allow "friendly fieldnames" in SQL queries against "raw data" tables, thus keeping the CSV import simple?

Or is it better to create custom import routines to map the ugly fields to consistent field names on every import?

What is the easiest way to achieve this? (sample query and output below)

I have started a "Fields" table that matches the actual field names to friendly names. Where fields are equivalent between sources, they use the same friendly name (see below).

FIELDS TABLE:

DataSource|Quarter|ReportCode|FieldCode|Shortname|Fullname|FieldType|FieldSize
ABC|2009q4|ABC2009q4|CORPID|firm_id|Company ID|int|4
XYZ|2009q4|XYZ2009q4|FIRMID|firm_id|Company ID|int|4
ABC|2009q4|ABC2009q4|TTLASSETS|assets|Total Assets|int|4
XYZ|2009q4|XYZ2009q4|ASSETS|assets|Total Assets|int|4
XYZ|2009q4|XYZ2009q4|RKFLD345|equity_total|Total Equity|int|4

DESIRED USER-SIDE QUERY:

select firm_id, name, address, assets, equity_total from merged_data
where assets > 500000
and quarter = '2009q4'

RESULTING DATA I WANT OUTPUT:

SELECT c.firm_id,
c.name,
c.address,
a.TTLASSETS as assets,
x.RKFLD345 as equity_total
FROM Corp_Info c, ABC_Data a, XYZ_Data x
WHERE c.firm_id = a.CORPID
AND c.firm_id = x.FIRMID
AND a.TTLASSETS > 5000000
AND a.Quarter = '2009q4'
AND x.Quarter = '2009q4'

My table designs are not set in stone, so if there's a better data structure to import into, I'm open to suggestions...

Mucho thanks in advance!

queryimport-datalookups
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Matt Whitfield avatar image
Matt Whitfield answered

I would definitely go down the mapping route, and use the friendly names table at import time to work out your mappings. It shouldn't be too difficult, using C#, the CsvReader and SqlBulkCopy...

That way you can just add mappings to SqlBulkCopy, then read from one and write to the other...

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.