topic Re: Load multiple fields with different name into one. in Water Cooler

Load multiple fields with different name into one.

danm84306 — Tue, 19 Aug 2014 16:54:05 GMT

Hello,

I have an issue. I am trying to load
csv file data from a folder. The 30+csv files have 7 different names for the
same data. I want to load all files (*.csv) as 1 field. Example below. (not all
files will have all names listed. Each will have only 1 variation)

stunum as Student_Id,

Id as Student_Id,

Stunum as Student_Id,

StuNum as Student_Id,

ID as Student_Id,

PrintID as Student_Id,

id as Student_Id,

Re: Load multiple fields with different name into one.

JonnyPoole — Tue, 19 Aug 2014 17:03:21 GMT

i was thinking the field names may not be important as you can use the @ sign to bring in the first field.

Just make sure you specify if there are leading rows to ignore.

LOAD

@1 as ID

FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

attached are the 2 CSV files i tested with.

Re: Load multiple fields with different name into one.

danm84306 — Tue, 19 Aug 2014 17:34:49 GMT

Hello,

This wil not work. the field is in a different column on each file.

Re: Load multiple fields with different name into one.

JonnyPoole — Tue, 19 Aug 2014 18:44:44 GMT

K. I'll try dig around for some code that will read the field headers to see whats there and then pull only the relevant field for each file.

Re: Load multiple fields with different name into one.

simenkg — Tue, 19 Aug 2014 18:56:30 GMT

how about:

rangemax(stunum, Id, Sunum, StuNum, ID, PrintID, id) as Student_Id

Re: Load multiple fields with different name into one.

simenkg — Tue, 19 Aug 2014 18:59:44 GMT

To give you a full solution you can do something like this:

Table1:

Load * from path\*.csv etc.....

Table2:

load *,

rangemax(stunum, Id, Sunum, StuNum, ID, PrintID, id) as Student_Id

resident Table1;

drop table Table1;

Drop fields stunum, Id, Sunum, StuNum, ID, PrintID, id;

Re: Load multiple fields with different name into one.

Anonymous — Tue, 19 Aug 2014 19:08:17 GMT

or how about the following:

use the following statement after your load statement which reads fields and data from the csv file.

rename fields using Mapping_Rename_Table;

where Mapping_Rename_Table is defined (has to be before the load statement from csv file) as

Mapping_Rename_Table:

mapping load * inline[

%Oldname, %Newname

stunum, Student_Id

Id, Student_Id

Stunum, Student_Id

StuNum, Student_Id

ID, Student_Id

PrintID, Student_Id

id, Student_Id

];

this way you can actually load all the files and not worry about which csv has which variation of student_id field name. in fact, you can add any other fields that you want to rename to the list.

replace % with whatever hide prefix you set. if there isn't one already set, you can set it using the following statement after the standard set statements in the Main script tab

Set hideprefix = '%';