topic Re: Use alias name while loading a data for all the columns in Connectivity & Data Prep

Use alias name while loading a data for all the columns

Reko_freed — Tue, 13 Dec 2022 17:05:48 GMT

I am trying to load few files they are file A and file B for two years that is 2019 and 2020, and they are basically - file_2020_A, file_2019_A, file_2020_B, file_2019_B. The load script which I am using is
[a]:
LOAD *
FROM [filepath/*_A.csv]

[b]:
LOAD*
FROM [filepath/*_B.csv]

what I want is at the time of data loading it will add a suffix at the end of each and every column for all the files.

for e.g., - file A consist of column col1, col2, col3... it will be change to col1_a, col2_a, col3_a and simultaneously for b as well.

please suggest me the change in the existing load script so that I get my result.

Re: Use alias name while loading a data for all the columns

Lauri — Thu, 15 Dec 2022 13:57:40 GMT

I would:

Get the suffix from the file name during the initial load and store it in a temporary table along with the table name
Loop through the field names of each table and run a rename statement where you add the suffix

Re: Use alias name while loading a data for all the columns

Or — Thu, 15 Dec 2022 14:40:46 GMT

As a potential easier alternative, you could use Qualify *; to generate an automatic prefix (not suffix) of the table name.

Re: Use alias name while loading a data for all the columns

Reko_freed — Fri, 16 Dec 2022 12:39:39 GMT

I have tried Qualify, but let me tell you what issue I am facing, it creates four different suffix names for four different files, due to which I am not able to get a concatenated table(one below other). See my load script,

[a]:
LOAD *
FROM [filepath/*_A.csv
this will load the file name ending with 'A' and concat them and gives me a single file for A
what I want is - filename ending with 'A' the prefix/suffix should be same