?
Solved

Informix CSV extract conversion to SQL Server 2000

Posted on 2007-08-10
11
Medium Priority
?
958 Views
Last Modified: 2013-12-24
I have a CSV extract of an informix database, I'm wanting to import the data into sql server 2000 along with the table properties.

I do not have access to informix.

I notice I have a syscolumns table the same as I would have in SQL which has a coltype column, however I dont have a systypes table which tells me what data type each number relates to.

does anyone have a conversion table e.g. where I have coltype = 6 in my informix table it tells me what data type that equates to in sql.

also any code that looks at my informix tables and imports them directly into SQL would be appreciated.

Thanks
0
Comment
Question by:MiddlesbroughPCT
  • 6
  • 4
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19668451
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19668739
If you've got an odbc driver for your version of informaix, you can add it as a sql linked server, and select directly from it.
0
 
LVL 1

Author Comment

by:MiddlesbroughPCT
ID: 19668752
I don't have access to informix...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:twoboats
ID: 19668765
Ah yes.

Well, your options are

Data Transformation Services (DTS)
Bulk Copy Program (BCP)
Linked server using text driver to .csv file

0
 
LVL 1

Author Comment

by:MiddlesbroughPCT
ID: 19668978
bulk copy program? - through a package or something else?
got 1,315 tables... so DTS is no good without spending all day.

a conversion table which  tells me what the data types in informix equates to in sql is the main goal.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19669838
BCP is a command line program - run it from a DOS command shell.

"got 1,315 tables... so DTS is no good without spending all day"

It's going to take a while, whichever of the 3 options you use - each needs specific info for each file.

"a conversion table which  tells me what the data types in informix equates to in sql is the main goal."

Have a look at this - you need to go through 2 steps - informix to odbc to sql server (under Data Type Mappings towrads then end of the page)

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/infmxsql.mspx#ETAAK
0
 
LVL 1

Author Comment

by:MiddlesbroughPCT
ID: 19669917
like I say I don't have access to informix.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19669971
"like I say I don't have access to informix."

sry - I don't follow. There are 2 tables on that page

first one maps sql datatypes to odbc, second maps informix to odbc, therefore, you have informix to sql indirectly.
0
 
LVL 1

Author Comment

by:MiddlesbroughPCT
ID: 19670260
unless im missing something (could you cut and paste for me).

im looking for  a table that tells me what the number equates to in the coltype column within the syscolumns table.

e.g. where I have coltype = 6 in my informix table something that tells me what data type that equates to in sql.

this table would be called systypes within SQL.

thanks
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19670314
ahhh - i see - the tables on that page give you names, not numbers....

0
 
LVL 14

Accepted Solution

by:
twoboats earned 1500 total points
ID: 19670417
I think this is what you're after - these are the informix values

coltype SMALLINT Code for column data type:

0 = NCHAR
1 = SMALLINT       
2 = INTEGER       
3 = FLOAT       
4 = SMALLFLOAT       
5 = DECIMAL       
6 = SERIAL       
7 = DATE       
8 = MONEY
10 = DATETIME
11 = BYTE
12 = TEXT
13 = NVARCHAR
14 = INTERVAL
15 = NCHAR
16 = NVARCHAR


from p42 here

http://mediasrv.ns.ac.yu/databases/informix/4365.pdf
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question