Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
Hello,
This wil not work. the field is in a different column on each file.
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.
how about:
rangemax(stunum, Id, Sunum, StuNum, ID, PrintID, id) as Student_Id
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;
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 = '%';