How to Import from Excel to Teradata

Face it...there are times when you need to build a table in Teradata and your only source is an Excel spreadsheet that someone gave you. So how do you easily load Excel data into Teradata?

First, create an empty table structure in Teradata as your target. Be sure to specify the correct datatypes for each column. Use the syntax: create table CONTAINER.TABLENAME (col1name col1datatype, col2name col2datatype,......) primary index (colxname). (Obviously, replace the values with the relevant container, tablename, column names, and data types.)

Be sure to build a primary index and make sure that the primary index column is as close to a unique value as possible.

Next, make sure you have the columns in Excel in the same order as the table you created. Remove the header rows from the Excel file and save as a Text (Tab Delimited) file.

From Teradata SQL Assistant, click 'File', then 'Import Data'. This sets TDSA into an import mode which will prompt you for a file location when you run a query.

Clear all other sql commands from the query window. When in Import mode, TDSA doesn't like to run only the highlighted query.

Run the command: insert into CONTAINER.TABLENAME values (?,?,?....) You must supply a ? (question mark) for every column. So, for a table with 5 columns the values command would look like: values (?,?,?,?,?). They will load in the order in which they appear in your text file, which is why it is important to match the columns in Excel with the order of the columns in your table.

Turn off the Import Data mode and resume querying.

And that's how you import from Excel to Teradata!