Connecting to an ISAM database from SQL server

Hi,

I have an ODBC driver 'Transoft ODBC driver' which I use to connect to an ISAM database via Excel.  I have also tried to connect directly from SQL Server but get so far and than have an error message 'Cannot find column -1'.  I can see all the isam tables - they have double quotes around them.  I can also preview the data but when I go to click 'next' i get that error message.  

Please help!  

Anita
ADhal37Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
I don't believe there is a solution (at least directly). If you link to Excel, see if you can link your SQL Server instance to that linked spreadsheet.

In the past I've had you use an ISAM viewer to extract the data to Access then import (after massaging all those double quotes out).

As I only have had to do this for static data as a one off, it has been a pragmatic solution.


Kelvin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Commented:
Is the connection on a mapped drive?

SQL can't see the mapped drive, it could see a UNC though.
0
DcpKingCommented:
If this is a one-off, you might try using SSIS if you're using SQL Server 2005 or later ...
Transoft are a company that specialises in getting at legacy data, so they might be able to extract it for you.

Do you know (are you allowed to say? !) what the original product was that stored data into the ISAM files? Sometimes we make it more difficult that needs be for ourselves - there maybe someone here who's also an expert in just the long-lost system that you're dealing with! As an example, Paradox is hardly in use any more; I supported it for about 15 years and still get work from people getting data out of its legacy clutches!

hth

Mike
0
ADhal37Author Commented:
Hi, Thanks for your replies.

The original system I am trying to extract information from is VIXEN.  It is a product that a company called Causeway built.  It is legacy now as it is not a product they develop any more. but our company still use it!

I could try using SSIS. I will have to look into this as haven't used ssis that much yet. This is something I am going to be doing on a weekly basis so needs to be robust.

Kind Regards
Anita
0
DcpKingCommented:
Ok Anita, so Vixen was merged into Causeway a while ago, but they should still be able to offer input and output to/from it.

One fairly easy way to handle this - seeing as you only need the info weekly and not in real-time - would be to get Vixen to output a flat-file - a .csv file or a spreadsheet - and then suck it in with SSIS, which is a really easy thing to do. Your hardest part may be getting Vixen to push out the info you need into a text file. I think you might start looking here and / or talk to your support people from Causeway.

hth

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.