Import from MS XLS (EXCEL) file to teradata table.

Database

Import from MS XLS (EXCEL) file to teradata table.

I am trying to import data into a Teradata table from an excel file is this possible?

I can save the excel file as tab delimited text file and then import it from queryman (sql assistant)that works.
But when i try to do this directly from the excel file I get an error
"wrong number of data values in import file"

If somebody worked on this, I'd appreciate your help.

Thanks

2 REPLIES

Re: Import from MS XLS (EXCEL) file to teradata table.

This is what Queryman help has to say on import rules: -

The Import file must be a delimited text file containing data values that are separated from each other by the delimiter character specified on the Export tab of the Options dialog box.

The option you have is to save the ".xls" file as ".csv" format, change the export tab of the option dialog box to , (comma) and then import the data. But you need to be careful for date fields if you have any in your input file. This is because, again the Queryman import rule says that: -

Date, Time and Timestamp values must be in the following format:
YYYY-MM-DD
HH:MM:SS
YYYY-MM-DD HH:MM:SS respectively
(For Teradata data sources, they must be in the format specified for the corresponding column on the Create Table statement. Either ‘-‘ or ‘/’ may, however, be used as the date separator.)

And when you will save your .xls into .csv, the date format might change.

Hope this helps.

Regards,
Sunil
N/A

Re: Import from MS XLS (EXCEL) file to teradata table.

Have you considered using some ETL tool ?  They are focused on such tasks as reading from somewhere (e.g. Excel) and writing to databases.

There are even some opensource/free tools, some of them Java based which means platform independent.

You may look at tools like  CloverETL or Kettle/Pentaho. Some more are listed on etltools.net