Reading from Excel File

[New_Datastage]

Hi,
We are trying to read EXCEL through use of ODBC:
With reference to the post viewtopic.php?t=104985&highlight=Reading+from+excel
We have found the post which states:

Code: Select all

ODBC Stage -Create a system DSN using Microsoft Excel Driver (*.xls) 
-Select a Workbook. 
-Import into datastage usinf ODBC Table Definitions. (You have to check the "Include System Tables", because internally it takes the sheets as tables) 
-Select the Sheet$ 
-You should be able to see the data in the excel from the "view data" 

We have done first 2 steps..but regarding the import metadata we are not able to dothe same since in the drop down list the DSN is not present.
Shall we add New DSN in the uvodbc.config file so that we can able to see DSN in the import .. ??
Please advice..

Thanks in Advance!

[ray.wurlod]

Have you created an entry for your DSN in the uvodbc.config file in your project?

[New_Datastage]

THanks for your reply!
I have not added DSN in the project folder..
Shall i add an entry in the uvodbc.config file as:
<SEVER_NAME>
DBMSTYPE = ODBC
By searching in this forum i have found that for unix OS import we require an external ODBC Driver??

Thanks in Advance!

[ray.wurlod]

The entry for uvodbc.config is essentially correct. <SEVER_NAME> (sic) is the Data Source Name.

You said that you had defined a DSN, which is why I assumed that you had already installed a driver - you can not define a DSN without one. Yes, you need to have a UNIX-based ODBC driver for Microsoft Excel.

[New_Datastage]

Thanks for the reply.
Actully I have defined DSN in windows, as per steps mentioned in my first post . But by searching in forum I have found that the steps which I have followed are steps of DSN configuration in WIndows, not in UNIX.
How to confiure same in UNIX (do I need to have a separatedriver , i.e. DSN for UNIX?)
PLease advice.
Thanks again for your help ..

[ray.wurlod]

You have to configure the DSN on the UNIX machine, by editing a file called .odbc.ini (also referencable as $ODBCINI). The UNIX machine then becomes the client to Excel on the Windows machine.

[New_Datastage]

Thank you very much for all the help you have provided.
I was just checking the .odbc.ini....but there are lot of things in that file..i am not sure what exactly i want to add in that file.
In that i have seen some heading regarding the ODBC following are the contains in:

Code: Select all

[ODBC]
IANAAppCodePage=4
InstallDir=/opt/datastage801/dev/Server/branded_odbc
Trace=0
TraceDll=/opt/datastage801/dev/Server/branded_odbc/lib/odbctrac.so
TraceFile=odbctrace.out
UseCursorLib=0

and there is one more DSN connection with following contains:

Code: Select all

[SERVER_NAME]
Driver=/opt/datastage801/dev/Server/branded_odbc/lib/VMmsss22.so
Description=DataDirect SQL Server Wire Protocol driver
Database=LandingPad
LogonID=dsdev
Password=datastage12
Address=server_Name,1034
QuotedId=No
AnsiNPW=No

Shall i add one more DSN which will be having same contains as mentioned above..??

Again thanks for your continuous support.

[chulett]

The post you linked to explains what to do when your DataStage server is on Windows. For UNIX? Different story - you use the [Text] driver as far as I know. Make a copy of it to a new name and try that.

[New_Datastage]

Thanks for the reply!
As per your recodmendation i have change the .odbc.ini file and added new DSN connection .The details are :

[Text]
Driver=/opt/datastage801/dev/Server/branded_odbc/lib/VMtxt22.so
Description=DataDirect TextFile(*.*) Driver
AllowUpdateAndDelete=0
ApplicationUsingThreads=1
CacheSize=4
CenturyBoundary=20
Database=/scratch/DataDirect_5.2/odbc/demo
DataFileExtension=TXT
DecimalSymbol=.
Delimiter=
FileOpenCache=0
FirstLineNames=0
IntlSort=0
ScanRows=25
TableType=Comma
UndefinedTable=GUESS

Addition of new field:
[SERVER_NAME]
Driver=/opt/datastage801/dev/Server/branded_odbc/lib/odbccurs.so
Description=DataDirect TextFile(*.*) Driver
AllowUpdateAndDelete=0
ApplicationUsingThreads=1
CacheSize=4
CenturyBoundary=20
Database=/scratch/DataDirect_5.2/odbc/demo
DataFileExtension=TXT
DecimalSymbol=.
Delimiter=
FileOpenCache=0
FirstLineNames=0
IntlSort=0
ScanRows=25
TableType=Comma
UndefinedTable=GUESS

I have change the Driver name,but still it is not working...as ray suggested i have to add the DSN in odbc.ini but in the driver which driver i have to specifiy ...there are four ".so" files in the lib path .
Following are the names:

Code: Select all

odbctrac.so    
odbccurs.so    
libodbc.so     
libodbcinst.so 

Ca you please tell me what exactly i have to add in the odbc.ini file.
Thanks all for your support.I hope that we can resolve this issues

[chulett]

Why did you feel the need to change the driver name? That surely is the path to the Dark Side...

[ray.wurlod]

Step back for a moment. Do you have a UNIX-based ODBC driver for Excel? If you don't the rest of the conversation is moot.

[chulett]

Was wondering when that was going to come up. Can the ODBC 'text' driver be made to work for that or do you need to purchase one specifically for Excel?

[ray.wurlod]

You need to purchase one specifically for Excel if you want to read directly from the worksheet. If you want to work from a CSV or TXT dump from the worksheet then a text driver would work, but you need to organize for the dump to occur.

[Nagaraj]

Ray,

Okay Lets assume that the ODBC excel driver is installed, after installatio is done, can i do any simple test at the unix level itself to check its connectivity?
How do i test the connectivity of Text driver as well?

I have done connectivity test for database accesed via odbc by executing the script given in IS8.0.1 $DSHOME/branded_odbc/example

[ray.wurlod]

There are test utilities in branded_odbc that can be used with any configured DSN. Within DataStage you can use DS_CONNECT (from the TCL prompt). You can also try importing metadata and View Data via an ODBC stage.