FASTLoad to load UK companies house data

Teradata Applications

FASTLoad to load UK companies house data

Hi,

I will start by saying I'm new to Teradata, and I have been given the challenge of loading the Free companies house CSV file into a Teradata table. The file is updated monthly by UK Companies house and available on their website. It is Comma Delimited with optional Double quotes. The fields are variable length. In the pasi I was able to load this with ease into Oracle 10g by using External table commands, and combining the four extracts.

When trying to load the same into Teradata I'm getting terribly stuck.  I have posted how far I have got, and if anyone would like to take up the challenge of correcting my Script, or indeed writting a script to load the Companies house data into Teradata I would be most grateful.

There is no confidential data in this file, and the field names are the same as those in the Companies house data files. I'm going to keep working on getting this fixed, but any assistance would be gratefully received.

/***************************************************************************

Fast Load Script for Table Test_FL_Table

****************************************************************************/

Load Login Parameters  */

/* I've listed dummy load parameters which usually reside in the file below */

sessions 16 8;

.logmech ldap;

.LOGON FOO/Userid,Password;

database A_Data_Base_Name;

.errlimit 100000;

/* .RUN file f:\FastLoad\PARAM.FLD; */

/**

############################################################################

#  Deleting Target

############################################################################

**/

drop table Test2_FL_Table ;

drop table et_Test2_FL_Table;

drop table uv_Test2_FL_Table;

create multiset table Test2_FL_Table

(

  COMPANY_NAME            VARCHAR(400 ),

  COMPANYNUMBER           VARCHAR(400 ),

  REGISTERED_ADDRESS      VARCHAR(400 ),

  POBOX                   VARCHAR(400 ),

  ADDRESSLINE1            VARCHAR(400 ),

  ADDRESSLINE2            VARCHAR(400 ),

  POSTTOWN                VARCHAR(400 ),

  COUNTY                  VARCHAR(400 ),

  COUNTRY                 VARCHAR(400 ),

  POSTCODE                VARCHAR(400 ),

  COMPANYCATEGORY         VARCHAR(400 ),

  COMPANYSTATUS           VARCHAR(400 ),

  COUNTRYOFORIGIN         VARCHAR(400 ),

  DISSOLUTIONDATE         VARCHAR(400 ),

  INCORPORATIONDATE       VARCHAR(400 ),

  ACCOUNTINGREFDAY        VARCHAR(400 ),

  ACCOUNTINGREFMONTH      VARCHAR(400 ),

  ACCOUNTDUEDATE          VARCHAR(400 ),

  LASTMADEUPDATE          VARCHAR(400 ),

  ACCOUNTS_CATEGORY       VARCHAR(400 ),

  RETURNS_DUE_DATE        VARCHAR(400 ),

  LASTRETURNDATE          VARCHAR(400 ),

  NUMMORTCHARGES          VARCHAR(400 ),

  NUMMORTOUTSTANDING      VARCHAR(400 ),

  NUMMORTPARTSATISFIED    VARCHAR(400 ),

  NUMMORTSATISFIED        VARCHAR(400 ),

  SICCODE1                VARCHAR(400 ),

  SICCODE2                VARCHAR(400 ),

  SICCODE3                VARCHAR(400 ),

  SICCODE4                VARCHAR(400 ),

  NUMGENPARTNERS          VARCHAR(400 ),

  NUMLIMPARTNERS          VARCHAR(400 ),

  URI                     VARCHAR(400 ),

  CHANGE_OF_NAME_DATE     VARCHAR(400 ),

  LAST_COMPANY_NAME       VARCHAR(400 ),

  CHANGE_OF_NAME_DATE_2   VARCHAR(400 ),

  LAST_COMPANY_NAME_2     VARCHAR(400 ),

  CHANGE_OF_NAME_DATE_3   VARCHAR(400 ),

  LAST_COMPANY_NAME_3     VARCHAR(400 ),

  CHANGE_OF_NAME_DATE_4   VARCHAR(400 ),

  LAST_COMPANY_NAME_4     VARCHAR(400 ),

  CHANGE_OF_NAME_DATE_5   VARCHAR(400 ),

  LAST_COMPANY_NAME_5     VARCHAR(400 ),

  CHANGE_OF_NAME_DATE_6   VARCHAR(400 ),

  LAST_COMPANY_NAME_6     VARCHAR(400 ),

  CHANGE_OF_NAME_DATE_7   VARCHAR(400 ),

  LAST_COMPANY_NAME_7     VARCHAR(400 ),

  CHANGE_OF_NAME_DATE_8   VARCHAR(400 ),

  LAST_COMPANY_NAME_8     VARCHAR(400 ),

  CHANGE_OF_NAME_DATE_9   VARCHAR(400 ),

  LAST_COMPANY_NAME_9     VARCHAR(400 ),

  CHANGE_OF_NAME_DATE_10  VARCHAR(400 ),

  LAST_COMPANY_NAME_10    VARCHAR(400 )

)

no primary index;

/*

############################################################################

# set record Specifies the delimter :

#  FILE - The name of the file from which the data will be loaded

############################################################################

*/

. set record vartext DELIMITER ',' QUOTE OPTIONAL;

define

   COMPANY_NAME            (VARCHAR(400 )),

   COMPANYNUMBER           (VARCHAR(400 )),

   REGISTERED_ADDRESS      (VARCHAR(400 )),

   POBOX                   (VARCHAR(400 )),

   ADDRESSLINE1            (VARCHAR(400 )),

   ADDRESSLINE2            (VARCHAR(400 )),

   POSTTOWN                (VARCHAR(400 )),

   COUNTY                  (VARCHAR(400 )),

   COUNTRY                 (VARCHAR(400 )),

   POSTCODE                (VARCHAR(400 )),

   COMPANYCATEGORY         (VARCHAR(400 )),

   COMPANYSTATUS           (VARCHAR(400 )),

   COUNTRYOFORIGIN         (VARCHAR(400 )),

   DISSOLUTIONDATE         (VARCHAR(400 )),

   INCORPORATIONDATE       (VARCHAR(400 )),

   ACCOUNTINGREFDAY        (VARCHAR(400 )),

   ACCOUNTINGREFMONTH      (VARCHAR(400 )),

   ACCOUNTDUEDATE          (VARCHAR(400 )),

   LASTMADEUPDATE          (VARCHAR(400 )),

   ACCOUNTS_CATEGORY       (VARCHAR(400 )),

   RETURNS_DUE_DATE        (VARCHAR(400 )),

   LASTRETURNDATE          (VARCHAR(400 )),

   NUMMORTCHARGES          (VARCHAR(400 )),

   NUMMORTOUTSTANDING      (VARCHAR(400 )),

   NUMMORTPARTSATISFIED    (VARCHAR(400 )),

   NUMMORTSATISFIED        (VARCHAR(400 )),

   SICCODE1                (VARCHAR(400 )),

   SICCODE2                (VARCHAR(400 )),

   SICCODE3                (VARCHAR(400 )),

   SICCODE4                (VARCHAR(400 )),

   NUMGENPARTNERS          (VARCHAR(400 )),

   NUMLIMPARTNERS          (VARCHAR(400 )),

   URI                     (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE     (VARCHAR(400 )),

   LAST_COMPANY_NAME       (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE_2   (VARCHAR(400 )),

   LAST_COMPANY_NAME_2     (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE_3   (VARCHAR(400 )),

   LAST_COMPANY_NAME_3     (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE_4   (VARCHAR(400 )),

   LAST_COMPANY_NAME_4     (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE_5   (VARCHAR(400 )),

   LAST_COMPANY_NAME_5     (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE_6   (VARCHAR(400 )),

   LAST_COMPANY_NAME_6     (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE_7   (VARCHAR(400 )),

   LAST_COMPANY_NAME_7     (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE_8   (VARCHAR(400 )),

   LAST_COMPANY_NAME_8     (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE_9   (VARCHAR(400 )),

   LAST_COMPANY_NAME_9     (VARCHAR(400 )),

   CHANGE_OF_NAME_DATE_10  (VARCHAR(400 )),

   LAST_COMPANY_NAME_10    (VARCHAR(400 ))

file=f:\CH_Data_File_1_4.csv; 

/*

############################################################################

#  The Function of the Begin Loading Statement is

# - Identify the Fastload table to recieve data transfered from a

#   data source on the client compter

# - Specify the name two Error table ( ERRORFILES )

# - Start a new fastload job or Restart a job that has been paused

# - Locks the target until the end loading statement is not issued

# - CHECKPOINTS, indicates how often they are taken.

############################################################################

*/

. record 2;

BEGIN LOADING Test2_FL_Table

    ERRORFILES et_Test2_FL_Table, uv_Test2_FL_Table

    CHECKPOINT 1000000;

/*

############################################################################

#  INSERT is a Teradata SQL statement that inserts data records into the

#  rows of the fastload table.

############################################################################

*/

insert into Test2_FL_Table(

  COMPANY_NAME             ,

  COMPANYNUMBER            ,

  REGISTERED_ADDRESS       ,

  POBOX                    ,

  ADDRESSLINE1             ,

  ADDRESSLINE2             ,

  POSTTOWN                 ,

  COUNTY                   ,

  COUNTRY                  ,

  POSTCODE                 ,

  COMPANYCATEGORY          ,

  COMPANYSTATUS            ,

  COUNTRYOFORIGIN          ,

  DISSOLUTIONDATE          ,

  INCORPORATIONDATE        ,

  ACCOUNTINGREFDAY         ,

  ACCOUNTINGREFMONTH       ,

  ACCOUNTDUEDATE           ,

  LASTMADEUPDATE           ,

  ACCOUNTS_CATEGORY        ,

  RETURNS_DUE_DATE         ,

  LASTRETURNDATE           ,

  NUMMORTCHARGES           ,

  NUMMORTOUTSTANDING       ,

  NUMMORTPARTSATISFIED     ,

  NUMMORTSATISFIED         ,

  SICCODE1                 ,

  SICCODE2                 ,

  SICCODE3                 ,

  SICCODE4                 ,

  NUMGENPARTNERS           ,

  NUMLIMPARTNERS           ,

  URI                      ,

  CHANGE_OF_NAME_DATE      ,

  LAST_COMPANY_NAME        ,

  CHANGE_OF_NAME_DATE_2    ,

  LAST_COMPANY_NAME_2      ,

  CHANGE_OF_NAME_DATE_3    ,

  LAST_COMPANY_NAME_3      ,

  CHANGE_OF_NAME_DATE_4    ,

  LAST_COMPANY_NAME_4      ,

  CHANGE_OF_NAME_DATE_5    ,

  LAST_COMPANY_NAME_5      ,

  CHANGE_OF_NAME_DATE_6    ,

  LAST_COMPANY_NAME_6      ,

  CHANGE_OF_NAME_DATE_7    ,

  LAST_COMPANY_NAME_7      ,

  CHANGE_OF_NAME_DATE_8    ,

  LAST_COMPANY_NAME_8      ,

  CHANGE_OF_NAME_DATE_9    ,

  LAST_COMPANY_NAME_9      ,

  CHANGE_OF_NAME_DATE_10   ,

  LAST_COMPANY_NAME_10    

)

VALUES(

  :COMPANY_NAME             ,

  :COMPANYNUMBER            ,

  :REGISTERED_ADDRESS       ,

  :POBOX                    ,

  :ADDRESSLINE1             ,

  :ADDRESSLINE2             ,

  :POSTTOWN                 ,

  :COUNTY                   ,

  :COUNTRY                  ,

  :POSTCODE                 ,

  :COMPANYCATEGORY          ,

  :COMPANYSTATUS            ,

  :COUNTRYOFORIGIN          ,

  :DISSOLUTIONDATE          ,

  :INCORPORATIONDATE        ,

  :ACCOUNTINGREFDAY         ,

  :ACCOUNTINGREFMONTH       ,

  :ACCOUNTDUEDATE           ,

  :LASTMADEUPDATE           ,

  :ACCOUNTS_CATEGORY        ,

  :RETURNS_DUE_DATE         ,

  :LASTRETURNDATE           ,

  :NUMMORTCHARGES           ,

  :NUMMORTOUTSTANDING       ,

  :NUMMORTPARTSATISFIED     ,

  :NUMMORTSATISFIED         ,

  :SICCODE1                 ,

  :SICCODE2                 ,

  :SICCODE3                 ,

  :SICCODE4                 ,

  :NUMGENPARTNERS           ,

  :NUMLIMPARTNERS           ,

  :URI                      ,

  :CHANGE_OF_NAME_DATE      ,

  :LAST_COMPANY_NAME        ,

  :CHANGE_OF_NAME_DATE_2    ,

  :LAST_COMPANY_NAME_2      ,

  :CHANGE_OF_NAME_DATE_3    ,

  :LAST_COMPANY_NAME_3      ,

  :CHANGE_OF_NAME_DATE_4    ,

  :LAST_COMPANY_NAME_4      ,

  :CHANGE_OF_NAME_DATE_5    ,

  :LAST_COMPANY_NAME_5      ,

  :CHANGE_OF_NAME_DATE_6    ,

  :LAST_COMPANY_NAME_6      ,

  :CHANGE_OF_NAME_DATE_7    ,

  :LAST_COMPANY_NAME_7      ,

  :CHANGE_OF_NAME_DATE_8    ,

  :LAST_COMPANY_NAME_8      ,

  :CHANGE_OF_NAME_DATE_9    ,

  :LAST_COMPANY_NAME_9      ,

  :CHANGE_OF_NAME_DATE_10   ,

  :LAST_COMPANY_NAME_10    

);

/*

############################################################################

#  The END LOADING command distributes all the rows that were sent from the

#  client to the Teradata RDBMS during the loading phase to their final

#  destination on the AMPs.

############################################################################

*/

END LOADING;

/*

############################################################################

#  LOGOFF - Quit Fastload and all the Teradata RDBMS Sessions.

############################################################################

*/

.logoff;

Tags (2)
5 REPLIES
Enthusiast

Re: FASTLoad to load UK companies house data

What error you are getting? Share the execution logs to know what issue you are facing!

Enthusiast

Re: FASTLoad to load UK companies house data

Also just my suggestion: Please share the data (at least sample or reasonable mask) besides logs and errors to avoid delay in response.

Cheers,

Raja

Re: FASTLoad to load UK companies house data

Thank you for your responces. I have identified the issue. It is in the data and is to do with the issues of Double Quotes. I have re-formatted the data and can now load it, therefore this is a non-issue now.

Enthusiast

Re: FASTLoad to load UK companies house data

Depending on which version of Teradata you are using there is a function CSVLD you can use.  You first need to load your data into a table as one long varchar column.  You can then split the fields out using code like this:

SELECT * FROM TABLE (CSVLD(my_tablename.column1, ',', '"')
RETURNS (
p01 varchar(100),
p02 varchar(100),
p03 varchar(100),
p04 varchar(100),
p05 varchar(100),
p06 varchar(100),
p07 varchar(100),
p08 varchar(100),
p09 varchar(100),
p10 varchar(100),
p11 varchar(100),
p12 varchar(100),
p13 varchar(100),
p14 varchar(100),
p15 varchar(100)

)) as T1;

There is also an opposite function CSV you can use to create files in this format.

Enthusiast

Re: FASTLoad to load UK companies house data

Hi Andy,

Thanks for sharing. May I know if we can use in TD 13.10?

Cheers,

Raja