Excel File on different server

ghostraider · Post by **ghostraider** » Wed Oct 31, 2007 7:33 pm

Hello all, i have to use excel file as a source in one of my jobs and i am doing it using ODBC. I am able to read the file when it is placed on DataStage Server but not able to read if it is on a mapped network drive. That is the file originally is on a different server and i have a copy of it on my DataStage server as of now. When i try to read it from the original path after setting the DSN and configuring it i am not able to read the file.

Does the excel file need to be on the DataStage server only or can it reside on any other mapped drive? Please let me know.

Thanks much

ArndW · Post by **ArndW** » Wed Oct 31, 2007 7:56 pm

Try approaching it from a different angle. Try to read the excel file on a remote machine using your DSN from another tool, i.e. msquery. If it works then the error is in DataStage realm; if it doesn't work then you have an ODBC limitation (I believe that this is going to be the case).

ghostraider · Post by **ghostraider** » Thu Nov 01, 2007 10:59 am

ArndW wrote:Try approaching it from a different angle. Try to read the excel file on a remote machine using your DSN from another tool, i.e. msquery. If it works then the error is in DataStage realm; if it doesn't work then you have an ODBC limitation (I believe that this is going to be the case).

When i try reading data from excel using msquery i do not see any DSNs i have created. The following is the error i am getting when i try to import table definiton for the excel file on a different server.

DSR.MetaGeta(GET.DSNINFO)(SQLConnect('Users_Excel','admin')): BCI Error:
SQLSTATE=S1009,CODE=-1023,[DataStage][SQL Client][ODBC][Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path.
Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

I am mapped to the server and i am sure i am giving the right path.

ArndW · Post by **ArndW** » Thu Nov 01, 2007 4:37 pm

Ignore DataStage for the moment. If you can't get it to work with MSQUERY then you won't get it to work in DataStage.

When i try reading data from excel using msquery i do not see any DSNs i have created.

Fix this first. Did you specify a system DSN?

ghostraider · Post by **ghostraider** » Thu Nov 01, 2007 5:29 pm

ArndW wrote:Ignore DataStage for the moment. If you can't get it to work with MSQUERY then you won't get it to work in DataStage.

When i try reading data from excel using msquery i do not see any DSNs i have created.
Fix this first. Did you specify a system DSN?

I did specify the system DSN, but will i be able to see it in MS Query after i specify? I wasn't able to find the DSN, i could see the system tables though.

Regards

ghostraider · Post by **ghostraider** » Thu Nov 01, 2007 5:37 pm

And by the way, i tried converting the same excel sheet to a .csv file but i couldn't read it either. I am thinking i don't have the necessary permissions to read from that server using datastage.

ArndW · Post by **ArndW** » Thu Nov 01, 2007 5:38 pm

It should work, but I can't test or answer that for you.

If it won't work for (simple) MSQuery then it won't work for (complex) DataStage.

Have you looked in the web or the ODBC documentation to see if this is supported functionality?

ghostraider · Post by **ghostraider** » Tue Nov 06, 2007 4:51 pm

ArndW wrote:It should work, but I can't test or answer that for you.

If it won't work for (simple) MSQuery then it won't work for (complex) DataStage.

Have you looked in the web or the ODBC documentation to see if this is supported functionality?

ArndW, i have searched on google with the error i got. It is the same case, where either the database is on a different server or the file is on a different server and not on the hosting machine. I did a lot of search but couldn't find an exact answer. I am not able to see the created DSN in MS query, not sure why. I will check with my server admin to see if there are any limitations. Thanks much

meet_raghu · Post by **meet_raghu** » Wed Nov 07, 2007 12:05 pm

DataStage can't read any file from the mapped / shared drive. It is a limitation in DataStage.