March 11, 2009 at 8:42 pm
[font="Verdana"]
I have an EBCDIC file that I am trying to bulk insert into a SQL Server table, hopefully converting from EBCDIC to something more meaningful along the way.
The file contains a mixture of text and BCD columns.
I've created an XML format file, and I'm using the following command to load the data.
bulk insert dbo.MyFile
from '\\server\folder\MyFile.dat'
with (
codepage = 'OEM',
datafiletype = 'char',
formatfile = '\\server\folder\MyFile.format.xml',
maxerrors = 1,
order(TRAN_SEQ_N),
tablock,
lastrow=2
);
I have tried varying the codepage and datafiletype values, and no matter what combination I use, I get the following error:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (BANK).
Has anyone else done this? If so, how do you get it to work and actually do the conversion?
Here's the table definition:
create table dbo.MyTable(
BANK decimal(2, 0) not null,
BRCH decimal(4, 0) not null,
ACNO decimal(7, 0) not null,
SUFX decimal(2, 0) not null,
TRANTYPE_N decimal(1, 0) not null,
TRAN_KEY_N decimal(6, 0) not null,
TRANFRMT_C char(4) not null,
OLD_TRCD_C char(4) not null,
TRAN_SEQ_N decimal(7, 0) not null,
TRAN_A char(14) not null,
TRAN_BAL_A char(14) not null,
PROD_N decimal(10, 0) not null,
TRAN_D numeric(8, 0) not null,
LASTTRAN_D numeric(8, 0) not null,
ACUNLMTN_D numeric(8, 0) not null,
TRANBRCH_N decimal(4, 0) not null,
BTC_SEQ_N decimal(8, 0) not null,
TRANPSLN_N decimal(6, 0) not null,
BRCHCASH_N decimal(2, 0) not null,
OVRDPSNL_N decimal(6, 0) not null,
TRAN_CAT_N decimal(1, 0) not null,
TRANMNTN_C char(2) not null,
TERMBDAY_Q char(1) not null,
ENTRMODE_N decimal(1, 0) not null,
TERMMODE_N decimal(1, 0) not null,
STMT_REF1 char(12) not null,
STMT_REF2 char(12) not null,
STMT_REF3 char(12) not null,
STMT_OTHERPARTY char(20) not null,
STMT_CFTT_N char(8) not null,
CIDM_N decimal(8, 0) not null,
CARD_IDNN_X char(20) not null,
TRAN_GEN_D numeric(8, 0) not null,
TRAN_GEN_T numeric(6, 0) not null,
OPTY_PITM_C char(22) not null,
BSS_RCMD_C char(3) not null,
BSS_VALU_N decimal(2, 0) not null,
BSS_RISK_N decimal(2, 0) not null,
BSS_OOO_A char(14) not null,
TRTP_N decimal(8, 0) not null,
BRIF_T char(50) not null,
FILLER_1 char(5) NULL,
JRNL_TIME varbinary(3) not null,
JRNL_DVTP_N varbinary(1) not null,
BP_LOGGED varbinary(1) not null,
CHQSDEPOSITED_Q varbinary(2) not null,
CIF_BANK varbinary(1) not null,
CIF_N varbinary(5) not null,
NOTES_A varbinary(6) not null,
COINS_A varbinary(4) not null,
CHQ_A varbinary(6) not null,
CASH_HANDLING_TYPE char(2) not null
);
alter table dbo.MyTable
add constraint MyTable_PK
primary key clustered(
TRAN_SEQ_N asc
);
I've attached the XML format file.
Thanks!
[/font]
March 11, 2009 at 8:57 pm
Ugh, nightmares of working with EBCDIC. I don't envy you and I'm going to decline to dig in here. Always a pain.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 11, 2009 at 9:19 pm
[font="Verdana"]I've been playing around with this for days, and I'm about to give up. I think what I will do is get the packed decimal fields written out as normal text fields, and then rely on the normal FTP to convert from EBCDIC to ASCII. From there it just becomes a simple fixed with file load.
That means find time/resource to get a program implemented on the mainframe. Joy.
[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply