Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm loading a CSV file from a REST connection, now I want to add a column to it with a key so I can link the entries with other tables.
I'm loading the CSV like this:
for i = 1 to 5
let filename = i & '.csv';
RestConnectorMasterTable:
SQL SELECT
"MyCol1",
"MyCol2",
...
FROM CSV (header on, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(Url "$(filename)");
NEXT i;
So I will get a big table with the entries of all CSVs right beneath each other. However I don't have a way to distinguish from which file they came. That's why I would like to add an extra column 'file' that stores the file name where each entry got loaded from.
If I understand correctly I can't use any calculated fields with an SQL Select statement directly. I thought of using
LOAD * INLINE [
// file
// '$(filename)'
// ](delimiter is ',');
right after the statement above, but this will straight up deliver no results. I think using Join or Keep are no options either, because I don't have a key to match yet.
What would be the best way to accomplish this?
use preceding load to achieve that
like
for i = 1 to 5
let filename = i & '.csv';
RestConnectorMasterTable:
Load *, filename() as filename;
SQL SELECT
"MyCol1",
"MyCol2",
...
FROM CSV (header on, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(Url "$(filename)");
NEXT i;
Regards,
Prashant Sangle
use preceding load to achieve that
like
for i = 1 to 5
let filename = i & '.csv';
RestConnectorMasterTable:
Load *, filename() as filename;
SQL SELECT
"MyCol1",
"MyCol2",
...
FROM CSV (header on, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(Url "$(filename)");
NEXT i;
Regards,
Prashant Sangle