Teradata ↔ Excel transfers via OleLoad
Note:  These instructions assume that the reader is already familiar with the basics of using the Teradata OLE DB Access Module.  Readers unfamiliar with this product should first read the Teradata OLE DB Access Module product’s on-line help file, or the OLE DB Access Module chapter in the Teradata Tools and Utilities Access Modules Reference (B035-2425-093A) (available from the NCR Information Products Publishing library at http://www.info.ncr.com/).
How to load a Teradata table from a Microsoft® Excel spreadsheet using the OleLoad program that is included in the Teradata OLE DB Access Module (just the interesting steps):
·	First, set the
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows registry value to 0.  This will cause the Microsoft Jet 4.0 OLE DB Provider to examine the whole spreadsheet when determining data types as opposed to only examining the first 8 rows.  See <http://support.microsoft.com/default.aspx?scid=kb;en-us;281517> for more information on this.
•	In the OleLoad GUI, for Select a source, choose the Microsoft Jet 4.0 OLE DB Provider (Microsoft.Jet.OLEDB.4.0) entry.
·	On the All tab of the Data Link Properties dialog box, set Extended Properties to Excel 8.0;IMEX=1 and set Data Source to the pathname of the file containing your Excel spreadsheet (for example, 
C:\WINNT\Profiles\JohnDoe\My Documents\MyWorkbook.xls). 
•	Note that when you close the Data Link Properties dialog box, you will see an error message saying:
Error occurred while attempting to set the properties you entered.  To make changes and try again, click Cancel.  To accept this error and continue, click OK.  Failed Properties:  Persist Security Info (NOT SUPPORTED)
This is normal, just click OK.
·	For Select a destination, choose the NCR Teradata (“Teradata”) entry.
•	Ensure that your load script does not utilize row sizes that are too long and does not utilize field names that are objectionable to the load utility.	
How to export a Teradata table to a Microsoft® Excel spreadsheet using the OleLoad program that is included in the Teradata OLE DB Access Module (just the interesting steps):
•	In the OleLoad GUI, for Select a destination, choose the Microsoft Jet 4.0 OLE DB Provider (Microsoft.Jet.OLEDB.4.0) entry. 
•	On the All tab of the Data Link Properties dialog box, set Extended Properties to Excel 8.0 and set Data Source to the name of the target Excel file (i.e. C:\temp\MyNewWorkbook.xls).  If the file does not already exist, the Microsoft Jet 4.0 OLE DB Provider will create a new Excel file.
•	Note that when you close the Data Link Properties dialog box, you will see an error message saying:
Error occurred while attempting to set the properties you entered.  To make changes and try again, click Cancel.  To accept this error and continue, click OK.  Failed Properties:  Persist Security Info (NOT SUPPORTED)
This is normal, just click OK.
•	For Select a source, choose the NCR Teradata (“Teradata”) entry.
•	It is important to exactly follow this step in its entirety.  Ensure that the Selection radio button in the Select the desired table and column(s) group is selected.  Find and select the icon for the desired table.  Due to an unfortunate issue in the Microsoft Jet 4.0 OLE DB Provider regarding quoting, you will see a message saying, Cannot open the specified table, "`tablename`", because: '`tablename`' is not a valid name.  Make sure that it does not include invalid characters or punctuation and that it is not too long.  Click OK.  Now click the Name radio button in the Select the desired table and column(s) group.  Click on Settings and enter the desired worksheet name in the Table name location without any punctuation or special characters (for example, MyWorksheet).
•	It seems that the Microsoft Jet 4.0 OLE DB Provider will not open the Microsoft® Excel file if another process already has the Microsoft® Excel file open.  So, when you launch FastExport or BTEQ to write data to the Microsoft® Excel file you must ensure that no other process has the Microsoft® Excel file open.  Well, the OleLoad GUI counts as a process here.  So, you can either save the Access Module Job settings to a .amj file (via clicking File/Save), copy and paste the FastExport or BTEQ script, exit OleLoad, and then run the FastExport or BTEQ job manually, or you can start the FastExport job and then quickly exit OleLoad before FastExport gets to the part of the job where it tries to open the Excel file.

Disclaimer:  This has been tried with version 2.2.1.1 of the Teradata OLE DB Access Module, version 4.0.8015.0 of the Microsoft Jet 4.0 OLE DB Provider (included in Jet 4.0 Service Pack 8 which is available at http://download.microsoft.com/download/9/d/c/9dcd9e1c-badc-4fe4-a52e-8f2fdfb652db/Windows2000-KB829558-x86-ENU.exe), version 3.3 of the ODBC Driver for Teradata, version 7.6.3 of Teradata FastExport, version 4.7.1 of Teradata CLIv2,and version 8.2.1 of Teradata BTEQ running on Microsoft Windows 2000 Professional with Service Pack 4 against a V2R5.1 Teradata RDBMS.  I don't know of things that would cause it to work differently on other versions but I do not warrantee that it has been tried and verified in all possible environments or versions.

Note that in order to transfer Unicode strings properly, version 2.2.1.1 of the Teradata OLE DB Access Module (available from the Teradata Solutions Global Support Center patch server) is required and you must select the UTF8 session character set in the Teradata OleLoad – Advanced Settings dialog box.