Accessing .xls or .mdb files in a Unix directory

bobthebuilder · Post by **bobthebuilder** » Mon Sep 12, 2005 2:28 pm

Hi,

Can DataStage can access .xls or .mdb files in a UNIX directory via ODBC connection or do we need to create a mini-ETL directory on a Windows box for DataStage to access these types of files.

Thanks
Bob

ray.wurlod · Post by **ray.wurlod** » Mon Sep 12, 2005 4:14 pm

You need the executable (excel.exe or msaccess.exe) as well as the ODBC driver. As far as I am aware there is no UNIX variant of either of these.

kduke · Post by **kduke** » Mon Sep 12, 2005 8:38 pm

You can easily export and import csv files from either.

vmcburney · Post by **vmcburney** » Mon Sep 12, 2005 9:22 pm

I did come across a few third party products that help in this area. One is the Unix to Windows bridge, it creates a bridge between a Unix server and a Windows server and gives the Unix operating system access to all the ODBC drivers on that Windows server. This will in turn give you access to any data sources on that server such as Access or Excel. Do a web search for Unix Windows ODBC bridge.

bangorlad@hotmail.com · Wed Oct 05, 2005 2:28 am

Hi,
I was just wondering if you managed to resolve your connection problems. I am having the same issues and have been trawling the web for an answer. The best I have come up with, are the Sequelink set of drivers from Datadirect (who actually provide the ODBC drivers for Datastage) but at quite a cost.

I think that it's fairly ridiculous that Datastage cannot connect to either MS Access or process xls files without having to use third party products and is a problem that they need to address.

ArndW · Post by **ArndW** » Wed Oct 05, 2005 4:15 am

Bangorlad,

I disagree with your assertion that Ascential should write a driver - accessing XLS files on a UNIX host is not part of the expected or mainstream processing for this product, and as such the engineering effort required to write a special solution and maintain it is not worth it to Ascential.

The ODBC standard was devised for just such cases. DS can connect to MS Access and .XLS on their native platform. I think it more appropriate that Microsoft write ODBC drivers for .XLS files on UNIX than for Ascential to do so.

You can see the complexities involved in writing this interface by looking at DataDirect's price tag for their solution.

bangorlad@hotmail.com · Wed Oct 05, 2005 4:47 am

If the drivers are available to Ascential through DataDirect, why don't they port with Datastage?

Datadirect have obviously seen the requirement as they wouldn't have bothered writing the sequelink drivers in the first place.

Sorry, I am just a bit vexed that I and my colleague have to spend days trawling the web to address some basic functionality issues, to which the only answer is to spend an inordinate amount of money or to create a CSV file.

The fact that you can't call SQL Server stored profcedures through Datastage is fairly disappointing (to put it mildly) aswell.

ArndW · Post by **ArndW** » Wed Oct 05, 2005 5:14 am

I agree with the stored procedure issue (which I hadn't known about); the .XLS stuff of UNIX is just something that has always been an issue to a few (I've had the same problem at sites before). The DataDirect drivers for UNIX XLS are not part of the deal with packaged drivers, that would have cost Ascential too much - since you not only get connectivity to excel in that bundle. I seem to recall that you can download a 14 or 28 day trial version for Solarix/HP/AIX from DataDirect that would let you do a couple of excel extractions if you have a one-off type of job to do.

bangorlad@hotmail.com · Wed Oct 05, 2005 5:40 am

Thanks.
I was just wondering whether it would be a sensible idea to have a section within the forum on known issues/things you can't do within the Datastage family of products. This would help people like me, who are fairly new to the application from spending hours searching for answers where there aren't any.

I could post two to begin with.

ArndW · Post by **ArndW** » Wed Oct 05, 2005 6:01 am

I don't think that type of an area (i.e. a section/forum) is a good idea - known limitations can be found with a search and there is already a forum for enhancement or change wishes (where your two issues should go).

I don't know where one would draw a line between limitations and wishes - stating that "DS can't read and convert my comma delimited Wordperfect v5 document into DSX" isn't a valid restriction but is a valid enhancement request [that will never be addressed, but that is beside the point].

chulett · Post by **chulett** » Wed Oct 05, 2005 7:06 am

Well... perhaps a post in the FAQ Discussion forum? See if a legitimate list of things can be generated and then perhaps a FAQ could come out of it.

battaliou · Post by **battaliou** » Wed Oct 05, 2005 10:28 am

I had a requirement to load data from a spreadsheet into unix. I wrote a macro to select the area I wanted out of the spreadsheet and saved it as a csv. This was then ftp'd to unix. Easy.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 05, 2005 4:56 pm

Anything you post in the Enhancement Requests forum is forwarded to IBM. However, nothing is promised.

bangorlad@hotmail.com · Thu Oct 06, 2005 3:18 am

I think that you are missing the point battaliou. My remit is to transpose a host of dts packages from SQL Server into Ascential datastage. The reaoning behind this are a range of high level audit and securiity issues.
I could also use a similar implementation as you are describing but this would be niether secure, auditable or reliable. Datastage should be a fully integrated system through which this kind of functionality should be seamless.

Thanks for the feedback though.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 06, 2005 4:53 pm

You have the alternative of accessing through SQL Server, for which there are UNIX-based drivers that ship with DataStage.

You also have the alternative of running DataStage on a Windows server rather than UNIX.

DSXchange

Accessing .xls or .mdb files in a Unix directory

Accessing .xls or .mdb files in a Unix directory

use a macro