I'm trying to load data from Excel file uploaded to S3. I use "Advanced mode" and in the SQL Query I mapped needed columns.
In the Excel file - data is stored in the columns A, B and C. In the future, column D will be filled but now it is empty. I want to import column D in the Redshift table filled with "null" values.
Anyway, when I try to call column "D" in the Excel Query component (advanced mode, sql query) it gives me back error "column D was not found".
Off the top of my head without trying anything one option comes to mind. I am guessing you have tried to include column D in the select statement and it doesn't end up in Redshift.
This is a bit of a long shot but in the Connection Options for the Excel Query component add NullValueMode and set the value to ReadAsNull. In your select query include column d.
The other approach would be to leverage SQL. I don't know what is syntactically allowed in the Excel Query when you are in advanced mode but some options to try would be to do a cast:
CAST(COLUMN_D as string)
or
IFNULL(COLUMN_D, '')
or
CASE WHEN COLUMN_D IS NULL OR COLUMN_D = '' THEN NULL END
Those are some things that come to mind (I had to look the NullValueMode up though 🙂). Hopefully this helps!