How can I use bcp or sql to convert the EBCDIC to ASII?

Last Post 06 Jan 2013 11:01 AM by rm. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jeffdat
New Member
New Member

--
05 Jan 2013 11:47 AM
Convert EBCDIC to ASCII Question?
Recently I was given 200 EBCDIC .dat files with 5 to 500 columns and various rows per file. This data then needs to be mapped to an existing db.table.column in sql r2.
I reviewed and article by http://mikedavissql.com/2011/08/31/...i-in-ssis/ which offers a solution. The problem is the time involved mapping column0 to column500 to a corresponding db.table.column in sql server r2.
What I would ideally like is to use is bcp (
bcp db1.eb.h_plan_type in C:\fmt\ebcidic_1.dat -f C:\fmt\ ebcidic_1.xml -S -T) or sql

SELECT d_b,id_no,a_seq_no,b_cd,c_p,r_d
FROM OPENROWSET(BULK 'C:\fmt\ ebcidic_1.dat',
FORMATFILE=' f C:\fmt\ ebcidic_1.xml’,CODEPAGE = 437(tried every combination)
) AS t1;
to convert the EBCDIC to ASII. My dilemma is when I use the bcp or sql it doesn’t translate it. It just places unreadable characters in the EBCDIC collated database which SSIS doesn’t. If I try to change the collation either through sql or ssis from EBCDIC database to SQL_Latin1_General_CP1_CI_AS database it still remains encoded. I’ve tried it seems every field COLLATION and datatype in my format xml and at the database. The only thing that works is the above article
How can I use bcp or sql to convert the EBCDIC to ASII?
rm
New Member
New Member

--
06 Jan 2013 11:01 AM
Sql doesn't support EBCDIC as I understand, tried import data to unicode column?
You are not authorized to post a reply.

Acceptable Use Policy