Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Import/Export (DTS) and Replication (2000)  bulk importing a file containing EPCDIC chars

Author  Topic  koquinn Starting Member 1 Post Posted - 2002-01-31 : 18:21:24 Hi -- I have a quick (hopefully) question for you SQL gurus out there. The description is long, but the problem is fairly simple: I am trying to set up a DTS package to bulk import a file generated on a AS/400 and ftp'd over to our SQL 2000 Server. The problem is that the file they sent me contains a column with two weird EPCDIC (I assume) characters in it -- characters that cause the bcp/bulk insert/etc to barf on me. I am not an AS/400 person, so I may be stating this wrong, but from what I understand those characters represent a variable-length field on their DB2 database. The fact that would be in the output file to begin with tells me they aren't sending a "true" output file, but rather an actual binary database file. But that is another story... Anyhow, thus far the guys here don't know how to give me any other kind of output file that would eliminate those characters, so I have to continue as-is for now. When you look at the file in an editor they look like shaded boxes and non-english characters such as the 'o' with a tilde over it. These characters are not always the same thing (I assume because the length of that column differs.) They LOOK like extended-ASCII characters, yet I am unable to do a search and replace on them, nor am I able to use them as column delimiters or as a prefix to exclude for the next column. Using the file as it currently exists, when I try to bulk insert the file into the SQL database, it works fine, except for the small fact that it excludes everything after those special characters (inserting NULL into the remaining fields)! D'oh! If I try using bcp instead, it errors out with "invalid character value for cast specification" problems, which magically disappear if I MANUALLY delete those from the file beforehand. Obviously this isn't a solution, but it proves that they are causing the problem. If I set up the format file to a prefix of '1' for the column that follows those characters it allows SOME of the rows to import correctly, but not all. I AM able to select the data directly from the AS/400 using a linked server (using Client Access ODBC), but that solution is VERY slow, which is the reason we are trying to find a bulk insert solution in the first place. Any ideas? Do I need to give more details? Thanks for any and all suggestions!