Insert data from Excel to Terdata table via SQL assistant.

Sometimes you need to get data from excel to Teradata table. There are a few ways you can import data from excel to Teradata; one of them is using Teradata SQL assistant. I am going to explain step by step process of inserting data into Teradata table from an excel data sheet.

For example, you have customer information in the excel (Fig 1) and want to import the excel data to the teradata table.

Fig 1: Data in Excel

Step 1: Save excel as .csv file
First you need to save the excel file as .CSV

Fig 2: Save as .CSV

Step 2: Remove cloumn names from the excel
You can edit the .csv file like below (Fig:3 )

Fig 3: Edit .csv file

Then remove the column name from the .csv file. (See fig:4)

Fig 4: remove column names

Step 3: Create a temp table to insert the data from Excel

Create a volatile table in teradata (see fig: 5), SQL Code for creating the volatile table:

create volatile table Customer_Info
(Customer_no  int,
 Cutomer_First_Name varchar(30),
 Customer_LastName varchar(30),
 Phone int)
 on commit preserve rows;

Fig 5: Create table to store data from excel

And there are no records in the table.
                                       

Fig 6: Newly created table, waiting to get data from .csv

Step 4: Choose 'Import Data' from SQL Assistant

From Teradata SQL Assistant, go to File->Import data and click at Import data

Fig 7: Import data from Teradata SQL assistant.

And then you will see message "Ready for Import operation" at the above of SQL editor in teradata SQL assistant. (see Fig:8)

                                             Fig 8: Message 'Ready for Import operation'

Step 5: Execute insert statement

Now you need to write below SQL statment and press F5

                                             Fig 9: SQL syntax for inserting data
When you press F5 then you will find option to select your .csv file that you have completed in step 1 and step 2. (See figure 10)

Fig 10: Choose .csv file to get data into the table

When you select the .csv file SQL assistant import functionality will start taking the data from .csv and will insert records into the table 'Customer_Info', you will find message at the bottom of the windows (see fig;11)

Fig 11: data imported to the table

Now you can select the table to test if the table is filled by the data from the excel.

Note: Please click again 'Import data' from File->Import data to disable import mode in SQL assistant.