• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

Informix CSV extract conversion to SQL Server 2000

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
MiddlesbroughPCT
Asked:
MiddlesbroughPCT
  • 6
  • 4
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
twoboatsCommented:
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
 
MiddlesbroughPCTAuthor Commented:
I don't have access to informix...
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
twoboatsCommented:
Ah yes.

Well, your options are

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

0
 
MiddlesbroughPCTAuthor Commented:
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
 
twoboatsCommented:
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
 
MiddlesbroughPCTAuthor Commented:
like I say I don't have access to informix.
0
 
twoboatsCommented:
"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
 
MiddlesbroughPCTAuthor Commented:
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
 
twoboatsCommented:
ahhh - i see - the tables on that page give you names, not numbers....

0
 
twoboatsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now