Excel File on different server
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 49
- Joined: Wed Dec 13, 2006 6:31 pm
Excel File on different server
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
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
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).
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 49
- Joined: Wed Dec 13, 2006 6:31 pm
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.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).
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.
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.
Fix this first. Did you specify a system DSN?When i try reading data from excel using msquery i do not see any DSNs i have created.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 49
- Joined: Wed Dec 13, 2006 6:31 pm
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.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.
Fix this first. Did you specify a system DSN?When i try reading data from excel using msquery i do not see any DSNs i have created.
Regards
-
- Premium Member
- Posts: 49
- Joined: Wed Dec 13, 2006 6:31 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?
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 49
- Joined: Wed Dec 13, 2006 6:31 pm
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 muchArndW 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?
-
- Participant
- Posts: 5
- Joined: Wed Sep 05, 2007 3:34 pm