Accessing .xls or .mdb files in a Unix directory
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 17
- Joined: Mon Aug 15, 2005 3:13 pm
Accessing .xls or .mdb files in a Unix directory
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 12
- Joined: Wed Oct 05, 2005 1:44 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.
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.
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 12
- Joined: Wed Oct 05, 2005 1:44 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.
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 12
- Joined: Wed Oct 05, 2005 1:44 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.
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.
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].
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].
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
use a macro
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.
3NF: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. So help me Codd.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 12
- Joined: Wed Oct 05, 2005 1:44 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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
You also have the alternative of running DataStage on a Windows server rather than UNIX.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.