All Forums Database
pundir 5 posts Joined 09/14
04 Sep 2014
Importing Data from .XLS

Hi
 
I need to import data from .xls file using bteq command , I have tried all the options( REPORT,DATA) mode but  error is coming "source parcel length error "
is coming. plzz help me , its very urgent.

Raja_KT 1246 posts Joined 07/09
04 Sep 2014

You can save your file as csv. You can try something like this:

 

     .import vartext ',' file='temp.csv' ;

     .quiet on

 

     .repeat*

     using

     ( f1  varchar(10),

       f2 varchar(10)

     )

 

     insert into db1.temp_table

     (  f1

     ,  f2 )

 

     values

     ( cast(:f1 as integer )

     , cast(:f2 as integer ) )

     ;

     .quit

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

pundir 5 posts Joined 09/14
04 Sep 2014

Rajat,
It is working fine with vartext and CSV but I want only .xls to import.
Will DIF work to import .XLS and what is the syntax of DIF

Raja_KT 1246 posts Joined 07/09
04 Sep 2014

Even in SQL Assistant, if you import, the format is csv. I am not sure if DIF will work with xls. But you can save your file in csv right?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

pundir 5 posts Joined 09/14
04 Sep 2014

Can we Fastload or Mload. ?

SuSeSi 61 posts Joined 10/08
04 Sep 2014

Yes you can do fastload/Mload importing data from Excel using OleLoad (OLE DB Access Module).
check out the link https://forums.teradata.com/forum/analytics/excel-to-teradata
 

-Sudhansu

pundir 5 posts Joined 09/14
05 Sep 2014

I don't have access to OleLoad.
@Rajat - I tried with CSV and it work fine, Thanks for the solution.
But I have to load only .xls format file.
I am not getting any thing on google and tried almost all the options like  IMPORT MODES REPORT,DATA,VARTEXT.
Is there any option to importing  .xls data via Mload or Fastload without using OleLoad ??

pundir 5 posts Joined 09/14
05 Sep 2014

Teradata 14.02 supports supports importing .xls data.
I am using 13.0 version. Is this option not available with teradata 13.0 ?

Raja_KT 1246 posts Joined 07/09
05 Sep 2014

 I think it will also be difficult to load from excel if it works.For example, if the data for few columns are frozen and saved or data is taken from tabs , which are computed and some more features of excel, then there is a chance of  getting wrong data . CSVLD function is also csv.
You give a try. No harm in trying :) . I am more of a unix scriptor :).

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
05 Sep 2014

No release of Teradata supports importing Excel files.
I doubt there's any DBMS which can load .xls directly, as it's Microsoft's proprietary format and changing with every release.
So you need to access .xls files using a suitable ODBC or OLEDB driver.

Dieter

You must sign in to leave a comment.