After using Import Wizard to import Excel data, with the option to create and import into "Single New Table", trying to query that tables gives "Invalid Identifier" error.
Sample:
Import data using Import Wizard. Choose to create "single new table". This creates a table like...
,
3:42:31 PM Thread (26) Import Started [10/7/2011 3:42:31 PM]
3:42:31 PM Thread (26) Processing "test_casey.xlsx" into a new table MATHEW.test_casey
3:42:31 PM Thread (26) Dropping Table
3:42:31 PM Thread (26) Creating table MATHEW.TEST_CASEY
3:42:31 PM Thread (26) CREATE TABLE MATHEW.TEST_CASEY ("col1" NVARCHAR2(50),
"col2" NVARCHAR2(50))
3:42:31 PM Thread (26) Importing 1 out of 1 files
3:42:31 PM Thread (26) Reading from file test_casey.xlsx
3:42:31 PM Thread (26) Inserting row 2 into table MATHEW.test_casey
3:42:31 PM Thread (26) 2 rows from file test_casey.xlsx were processed.
3:42:31 PM Thread (26) A total of 2 out of 2 rows were imported to table TEST_CASEY.
3:42:31 PM Thread (26) Import Finished [10/7/2011 3:42:31 PM]
3:42:31 PM Thread (26) Import time: 00:00:00.6093828
When a query is run, like...
SELECT col1 FROM TEST_CASEY
... this gives the error. But the query is a valid query. Not specifying the column will work without issue (i.e. SELECT * FROM TEST_CASEY).
When creating a new table, Toad uses quotations around the column name to preserve column name format, based off the header name from the Excel source file (i.e. headers in the Excel sheet are lower case).
Notice in the sample the create statement:
CREATE TABLE MATHEW.TEST_CASEY ("col1" NVARCHAR2(50), "col2" NVARCHAR2(50))
When running the query, the column is not recognized if the query does not use quotations around the column name also.
i.e.
This will error out since it doesn not recognize col1 without the quotations.
SELECT col1 FROM TEST_CASEY
But this will not error out.
SELECT "col1" FROM TEST_CASEY
WORKAROUND 1:
Create table manually without the quotations when creating the columns, before importing the data.
WORKAROUND 2:
Alter table and edit column names after table has been created by the import process.
WORKAROUND 3:
This is only an issue when importing from an Excel file with headers that are in lower case format. If the headers are in all upper case format, then it will not create with quotations around the column names.
Before importing data from the Excel file, make sure the headers for all columns are in all upper case.
