Professional Web Applications Themes

Packed BCD to Binary - Microsoft SQL / MS SQL Server

How did you convert the data into SQL in the first place? Possibly the problem occurs in translating the EBCDIC character set into a VARCHAR column. I would suggest that the conversion is best done before the data reaches your database - convert the packed data to numerics as part of an ETL process. Different systems also implement variations of the packed decimal format - e.g. different ways of representing the sign nibble. Maybe you have one of these? I recommend you check out a standard ETL tool that will help you convert and validate this data. DataJunction (www.datajunction.com) does ...

  1. #1

    Default Re: Packed BCD to Binary

    How did you convert the data into SQL in the first place? Possibly the
    problem occurs in translating the EBCDIC character set into a VARCHAR
    column. I would suggest that the conversion is best done before the data
    reaches your database - convert the packed data to numerics as part of an
    ETL process.

    Different systems also implement variations of the packed decimal format -
    e.g. different ways of representing the sign nibble. Maybe you have one of
    these?

    I recommend you check out a standard ETL tool that will help you convert and
    validate this data. DataJunction (www.datajunction.com) does a decent job of
    handling EBCDIC data and there is a free evaluation available.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  2. #2

    Default Re: Packed BCD to Binary


    Thanks David,

    Actually, I have a text file was generated from COBOL. Some of the data
    in this file was packed. The data type of these packed data is
    Comp-3/C-3 in Cobol. These packed data appears as special characters or
    symbols in the text file. For example, the zip code 95762 is shown as
    'n....' in the file,
    some zip codes look like 'kh..<', 'j../' etc.

    I thought about to use the data junction program, but I need to write a
    program using C# & SQL to convert the packed data into readable
    characters, like 'n....' into 95762. I am having trouble
    converting/unpacking them.

    Maybe I should check out a ETL tool.

    --
    Posted via http://dbforums.com
    Art Guest

  3. #3

    Default Re: Packed BCD to Binary

    Art,

    I think it's more than likely that all the . characters in the
    file are there to represent non-printing characters, much as
    many hex editors will do when they present both hexadecimal
    and ASCII dumps of a file.

    I'll make one guess: Is 'j../' supposed to be 91617,
    and kh..< supposed to be 92884? A 5-digit zip should
    only take 3 bytes. 91617 would be 0x91617c, and the
    three bytes 0x91, 0x61, and 0x7c interpreted as EBCDIC
    characters are j/. All the characters you mention have
    EBCDIC representations using no A,B,C,D,E, or F, except
    for the final C, which is expected. 95762 might correspond
    to n??, since 0x95 is n, and 0x76 and 0x2c are not printable
    when interpreted as EBCDIC.

    Can you look at the binary values of each byte of your "text
    file"?

    If you have a _text_ file that is supposed to contain
    packed decimal data though, I suspect you're in trouble. I also
    don't see how it makes any sense for your "packed decimal"
    zip codes to require 5 characters each. The point of BCD is
    to use 1/2 byte for each decimal digit, plus a half-bit for the
    sign. your zip codes should only require 3 characters each.

    -- Steve Kass
    -- Drew University
    -- Ref: 491A9C14-13B1-4D48-9673-CC232965C607

    Art wrote:
     


    Steve Guest

  4. #4

    Default Re: Packed BCD to Binary

    > I thought about to use the data junction program, but I need to write a 

    DataJunction Integration Engine exposes COM objects for conversions so you
    can still invoke the conversion from C#.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Art" <com> wrote in message
    news:com... 


    David Guest

  5. #5

    Default Re: Packed BCD to Binary

    Use an ETL tool. It makes more sense to convert the packed data at import
    rather than try to implement an additional process in SQL. TSQL isn't ideal
    for this kind of thing and an ETL tool will have verification and
    error-handling built-in.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "msuchodo" <com> wrote in message
    news:com... 


    David Guest

  6. #6

    Default Re: Packed BCD to Binary


    David,

    My current process is done completely in SQL, so If possible I would
    like to stay in SQL. I have no problem in bringing data from
    mainframe, FTP it in ASCII the use DTS to split it to fields in the
    table. But I encounter a problem, I am getting files from mainframe
    that have fields packed or BCD or COMP3. Only way I can load these
    files in SQL is in ASCII format. I know I can run program on
    mainframe to unpacked before the conversion, but this is not a
    option. I hate to use external program if not necessary, so I was
    hoping that there will be function that will convert these funny
    looking characters "...`" into a value so it can be read by DB
    user. Any help will be greatly appreciated.



    Marcin


    --
    Posted via http://dbforums.com
    msuchodo Guest

Similar Threads

  1. Transparent packed C arrays
    By Ilya Zakharevich in forum PERL Modules
    Replies: 7
    Last Post: November 23rd, 02:25 AM
  2. [OT] Set packed path in tarball (using Gnu Tar)
    By Sisyphus in forum PERL Modules
    Replies: 1
    Last Post: August 26th, 02:19 PM
  3. converting packed to DEC
    By Estavao in forum IBM DB2
    Replies: 6
    Last Post: July 24th, 07:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139