November 14, 2002 at 4:05 pm
Currently I am involved on a project that requires 4 AS/400 teams to export a daily extract file to a text file with a row delimiter of CRLF and field delimiter of |. However since the "stream" method on AS400 is not being used the AS400 inserts an End of record on it's own immediately before the CRLF is inserted. When I then attempt to bulk insert this text file into SQL server it picks up this hexadecimal character at the end of the row. For example a field that expects 'Y' / 'N' is not getting 'Y0' / 'N0'. Has anyone ran into this issue? Thanks!
November 14, 2002 at 4:12 pm
How about specifying EOFCRLF as the row terminator when you BCP in? Then it shouldn't bring the EOF into the field.
-Dan
-Dan
November 14, 2002 at 4:30 pm
I don't believe it is that simple. Have you tried that? Once the text file is in place I don't think that it would recognize this method
November 14, 2002 at 4:48 pm
This is another example of EBCDIC to ASCII conversions that has issues. We encounterd several cases of things like this. Write a BCP input file and strip the last character was our answer. You can use bcp to output a few rows on a test system, use the option to create the definition file, modify it to exclude that character, and you got it.
We also had some files which had either the carriage return with no line feed or the line feed with no carriage return, I don't remember which off the top of my head, but it caused us no end of fits. And wait till you get a file of packed data, its amazing what funky little control characters in a file can do when they hit code. OMG....
Really though, I have a multitude of conversion routines for about any kind of AS/400 files you might want.
November 14, 2002 at 8:54 pm
Would you be able to share that information.? The less research required the better.
Thanks,
Scott
November 14, 2002 at 9:41 pm
Sure, where to start?
There seem to be a lot of people working with EBCDIC files lately...
I will assume you can handle generating the BCP definition files. Here is a striaght EBCDIC to ASCII conversion routine I use, I didn't write it, but found it easy to modify the exchange values for the character sets.
Its java and can be called through your script engine.
public class Translate {
private static final int SIZE = 95;
private static final int ASCII[] = {
0x0020, 0x0021, 0x0022, 0x0023,
0x0024, 0x0025, 0x0026, 0x0027, 0x0028,
0x0029, 0x002a, 0x002b, 0x002c,
0x002d, 0x002e, 0x002f,
0x0030, 0x0031, 0x0032, 0x0033,
0x0034, 0x0035, 0x0036, 0x0037, 0x0038,
0x0039, 0x003a, 0x003b, 0x003c,
0x003d, 0x003e, 0x003f,
0x0040, 0x0041, 0x0042, 0x0043,
0x0044, 0x0045, 0x0046, 0x0047, 0x0048,
0x0049, 0x004a, 0x004b, 0x004c,
0x004d, 0x004e, 0x004f,
0x0050, 0x0051, 0x0052, 0x0053,
0x0054, 0x0055, 0x0056, 0x0057, 0x0058,
0x0059, 0x005a, 0x005b, 0x005c,
0x005d, 0x005e, 0x005f,
0x0060, 0x0061, 0x0062, 0x0063,
0x0064, 0x0065, 0x0066, 0x0067, 0x0068,
0x0069, 0x006a, 0x006b, 0x006c,
0x006d, 0x006e, 0x006f,
0x0070, 0x0071, 0x0072, 0x0073,
0x0074, 0x0075, 0x0076, 0x0077, 0x0078,
0x0079, 0x007a, 0x007b, 0x007c,
0x007d, 0x007e
};
private static final int EBCDIC[] = {
0x0040, 0x005a, 0x007f, 0x007b,
0x005b, 0x006c, 0x0050, 0x007d, 0x004d,
0x005d, 0x005c, 0x004e, 0x006b,
0x0060, 0x004b, 0x0061,
0x00f0, 0x00f1, 0x00f2, 0x00f3,
0x00f4, 0x00f5, 0x00f6, 0x00f7, 0x00f8,
0x00f9, 0x007a, 0x005e, 0x004c,
0x007e, 0x006e, 0x006f,
0x007c, 0x00c1, 0x00c2, 0x00c3,
0x00c4, 0x00c5, 0x00c6, 0x00c7, 0x00c8,
0x00c9, 0x00d1, 0x00d2, 0x00d3,
0x00d4, 0x00d5, 0x00d6,
0x00d7, 0x00d8, 0x00d9, 0x00e2,
0x00e3, 0x00e4, 0x00e5, 0x00e6, 0x00e7,
0x00e8, 0x00e9, 0x00ad, 0x00e0,
0x00bd, 0x005f, 0x006d,
0x0079, 0x0081, 0x0082, 0x0083,
0x0084, 0x0085, 0x0086, 0x0087, 0x0088,
0x0089, 0x0091, 0x0092, 0x0093,
0x0094, 0x0095, 0x0096,
0x0097, 0x0098, 0x0099, 0x00a2,
0x00a3, 0x00a4, 0x00a5, 0x00a6, 0x00a7,
0x00a8, 0x00a9, 0x00c0, 0x006a,
0x00d0, 0x00a1
};
/**
* Description: Translates a int, either ASCII to
EBCDIC or vice versa
* @param int the int to be translated
* @return int the translated int
* @exception
**/
public final static int translateByte(int i) {
String thisCharSet = Config.getCharSet();
if (thisCharSet.equals("EBCDIC")) {
for (int j = 0; j<SIZE; j++) {
if (i == ASCII[j]) {
return EBCDIC[j];
}
}
}
return i;
}
}
If this will really help you, email me, and I'll give you access to my library of scripts. Its an awful lot to post in a newsgroup though.
November 15, 2002 at 10:11 am
This is great information. However, do you have any information that would be VBScript / JavaScript specific that could implemented within a DTS package? I don't believe JAVA will be utilized here.
November 15, 2002 at 2:59 pm
yes, lots. Click on my handle (scorpion_66) and it will show you my e-mail address. Shoot me an e-mail, and I'll send you all I got till you holler stop.....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply