Solved

# visual basic converting Hex to ascii character

Posted on 2004-03-26
3,664 Views
I have line that looks like this

C4001FC9D5F7F1F1F0F0F0F0F64040404040404040404040

how can I covert this to be what it truly is in ascii characters.

I have a file that looks like this, but I know it is coming in as hex and the line above
should look something close to this:

D TN71100006

not sure though

0
Question by:bear23
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 41
• 34

LVL 6

Expert Comment

ID: 10691582
Here is a link to a hex / bin /dec converter

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=46954&lngWId=1

not to sure if this is what you after but it may help.

0

LVL 22

Expert Comment

ID: 10692948
That's not the hex representation of ASCII, it's the hex representation of EBCDIC. (the 'T' should be 'I').
0

LVL 22

Expert Comment

ID: 10693017
Here's a table for EBCDIC:
http://www.natural-innovations.com/computing/asciiebcdic.html

Make a string, say EStr, of length 256 (subscripts running from 0 to 255) in which the i'th entry is the ASCII character corresponding to i'th EBCDIC character.  For example:

Option Base 0
Dim Estr As String
...
Mid(Estr,108,1)="%"

For those entries with no displayable character, you can use whatever you want, say tilde:
Mid(Estr,98,1)="~"

To do the conversion, read a line of hex from the file into some string variable, say HexLine, then:

Dim Xlate As String
Dim k As Integer
Dim OffSet As Integer
Dim AStr As String

AStr=""
Xlate="0123456789ABCDEF"
For k=0 to (Len(HexLine)/2)-1 Step 2
OffSet=16*(InStr(Xlate,mid(HexLine,k,1))-1) + (InStr(Xlate,mid(HexLine,k+1,1))-1)
AStr=AStr + EStr(OffSet)
next k

0

Author Comment

ID: 10695882
first I am getting an expected arrary error for EStr(OffSet)

and second I am really confussed.

where would I enter the line

C4001FC9D5F7F1F1F0F0F0F0F64040404040404040404040

at and are you saying that it will convert it to look correctly.

Please go step by step I am new to this.

Once again this is visual basic 6.0

0

Author Comment

ID: 10695895
you are correct the T should be an I

0

Author Comment

ID: 10695899
please show the code in how it will work with my line I listed.

Thanks
0

LVL 22

Expert Comment

ID: 10696242
Dim HexLine As String
Dim Xlate As String
Dim k As Integer
Dim OffSet As Integer
Dim AStr As String

'  I presume this would normaly be read from a file
HexLine="C4001FC9D5F7F1F1F0F0F0F0F64040404040404040404040"
AStr=""
Xlate="0123456789ABCDEF"
For k=0 to (Len(HexLine)/2)-1 Step 2
OffSet=16*(InStr(Xlate,mid(HexLine,k,1))-1) + (InStr(Xlate,mid(HexLine,k+1,1))-1)
AStr=AStr + EStr(OffSet)
next k
0

Author Comment

ID: 10696531
compile error:

sub or function not defined

it is highlighting the word

from the line

AStr=AStr + EStr(OffSet)
0

Author Comment

ID: 10696546
then if I do this

AStr=AStr + (OffSet)
I get another error

Runtime error 5

invalid procedure call or argument

on line

OffSet=16*(InStr(Xlate,mid(HexLine,k,1))-1) + (InStr(Xlate,mid(HexLine,k+1,1))-1)

0

LVL 22

Expert Comment

ID: 10696600
I messed up,

EStr(OffSet)

should have been

mid(estr,offset,1)

Also, it appears Mid() doesn't like a zero index, so there's lots of changes.  I'll post some tested code in a few hours.
0

LVL 22

Expert Comment

ID: 10696661
Option Base 1

Dim EStr As String
Dim HexLine As String
Dim Xlate As String
Dim k As Integer
Dim OffSet As Integer
Dim AStr As String
Dim c1 As String
Dim c2 As String

' Fill in with all ~ so we define only the ones we want
EStr = String(256, "~")

' Mid() doesn't like a 0 index, so this table is shifted up one.
' Also, I didn't include everything - you may want to fill in the missing parts (refer to link above)
' I chose this way of initializing EStr to make it easier to understand and to modify.
Mid(EStr, 65) = " "
Mid(EStr, 76) = "."
Mid(EStr, 77) = "<"
Mid(EStr, 78) = "("
Mid(EStr, 79) = "+"
Mid(EStr, 80) = "|"
Mid(EStr, 81) = "&"
Mid(EStr, 91) = "!"
Mid(EStr, 92) = "$" Mid(EStr, 93) = "\" Mid(EStr, 94) = ")" Mid(EStr, 95) = "^" Mid(EStr, 96) = "¬" Mid(EStr, 97) = "" Mid(EStr, 98) = "/" Mid(EStr, 108) = "," Mid(EStr, 109) = "%" Mid(EStr, 110) = "_" Mid(EStr, 111) = ">" Mid(EStr, 112) = "?" Mid(EStr, 123) = ":" Mid(EStr, 130) = "a" Mid(EStr, 131) = "b" Mid(EStr, 132) = "c" Mid(EStr, 133) = "d" Mid(EStr, 134) = "e" Mid(EStr, 135) = "f" Mid(EStr, 136) = "g" Mid(EStr, 137) = "h" Mid(EStr, 138) = "i" Mid(EStr, 146) = "j" Mid(EStr, 147) = "k" Mid(EStr, 148) = "l" Mid(EStr, 149) = "m" Mid(EStr, 150) = "n" Mid(EStr, 151) = "o" Mid(EStr, 152) = "p" Mid(EStr, 153) = "q" Mid(EStr, 154) = "r" Mid(EStr, 163) = "s" Mid(EStr, 164) = "t" Mid(EStr, 165) = "u" Mid(EStr, 166) = "v" Mid(EStr, 167) = "w" Mid(EStr, 168) = "x" Mid(EStr, 169) = "y" Mid(EStr, 170) = "z" Mid(EStr, 186) = "t" Mid(EStr, 194) = "A" Mid(EStr, 195) = "B" Mid(EStr, 196) = "C" Mid(EStr, 197) = "D" Mid(EStr, 198) = "E" Mid(EStr, 199) = "F" Mid(EStr, 200) = "G" Mid(EStr, 201) = "H" Mid(EStr, 202) = "I" Mid(EStr, 210) = "J" Mid(EStr, 211) = "K" Mid(EStr, 212) = "L" Mid(EStr, 213) = "M" Mid(EStr, 214) = "N" Mid(EStr, 215) = "O" Mid(EStr, 216) = "P" Mid(EStr, 217) = "Q" Mid(EStr, 218) = "R" Mid(EStr, 227) = "S" Mid(EStr, 228) = "T" Mid(EStr, 229) = "U" Mid(EStr, 230) = "V" Mid(EStr, 231) = "W" Mid(EStr, 232) = "X" Mid(EStr, 233) = "Y" Mid(EStr, 234) = "Z" Mid(EStr, 241) = "0" Mid(EStr, 242) = "1" Mid(EStr, 243) = "2" Mid(EStr, 244) = "3" Mid(EStr, 245) = "4" Mid(EStr, 246) = "5" Mid(EStr, 247) = "6" Mid(EStr, 248) = "7" Mid(EStr, 249) = "8" Mid(EStr, 250) = "9" HexLine = "C4001FC9D5F7F1F1F0F0F0F0F64040404040404040404040" AStr = "" Xlate = "0123456789ABCDEF" For k = 1 To Len(HexLine) Step 2 c1 = Mid(HexLine, k, 1) c2 = Mid(HexLine, k + 1, 1) OffSet = 16 * (InStr(Xlate, c1) - 1) + InStr(Xlate, c2) AStr = AStr + Mid(EStr, OffSet, 1) Next k MsgBox ("<" + AStr + ">") End Sub 0 Author Comment ID: 10697322 that works perfectly.... hey since you seem to know that format. I have another file that is the same data but is coming in like this in notepad, but in access it wierder looking Ä and when I pasted it into here and in vb it looks like the following Ä??????? I think it is ebcdic. but not ebcdic hex I found a translater on microsoft web site but when I run it through it looks like this D should be the same D IN..... can you help with this issue. I will add more points if you wish. You have been the only one that was able to help. 0 LVL 22 Expert Comment ID: 10697463 About the only way to figure out the format of the file is with a hex dump. Later this afternoon, I'll post some code you can use to do that. 0 LVL 22 Expert Comment ID: 10699121 Here's a command line hex file dumper (no forms, just make the EXE) Run it against the file in question and post back the output Dim FileIn As String Dim FileOut As String Dim FileBuf As String Dim k As Integer Dim HexOut As String Dim CharsOut As String Dim ThisByte As Integer Sub Main() ' Get file to dump from the command line FileIn = Command() ' Output file is the same name with '.dmp' added FileOut = FileIn + ".dmp" Open FileIn For Binary As 1 Open FileOut For Output As 2 ' Read 16 bytes at a time FileBuf = "xxxxxxxxxxxxxxxx" While Not EOF(1) Get #1, , FileBuf HexOut = "" CharsOut = "" For k = 1 To Len(FileBuf) ' Get this char as an integer ThisByte = Asc(Mid(FileBuf, k, 1)) ' Make 2-digit hex equivalent HexOut = HexOut + Right("0" + Hex(ThisByte), 2) + " " ' Get ASCII equivalent, but don't dump control chars If ThisByte < 32 Then CharsOut = CharsOut + "." Else CharsOut = CharsOut + Chr(ThisByte) End If ' Go back for the next 16 bytes Next k ' DUmp out this 16 byte clump Print #2, HexOut + " | " + CharsOut Wend Close 2 Close 1 End Sub 0 Author Comment ID: 10699217 I am sorry, I am new to this. Where should I put the code and how should I run it. 0 LVL 22 Expert Comment ID: 10699368 No problem - everything is new to each of us at some time 1) make a new project 2) remove the default Form1 * Project Explorer * right click Form1 * select Remove 3) rename project to HexDump * Project Explorer * right click project * select project properties * enter new project namje 4) add a new module * Project/Add Module 5) copy in the above code to the new module 6) create EXE * File/Make For what it's worth, I save every project into its own directory. For example, I have this one in: c:\currentthings\ee\vbhexdump To run it: 1) go to a command prompt Start/Run/enter CMD 2) CD over to where the EXE is For me, that was: cd \currentthings\ee\vbhexdump 3) run program C:\CURRENTTHINGS\EE\VBHEXDUMP> hexdump filename where filename is the file to take the dump of If filename was c:\foo.bar, hexdump will create the dump as a text file in c:\foo.bar.dmp Open up foo.bar.dmp with notepad and post its output here. If it's a large file, 50 lines or so will probably be enough. 0 Author Comment ID: 10699510 I think I did it correctly. I just copied some of the output FF FE C4 00 2F F1 F7 F0 F0 F0 F0 F0 F3 F0 C5 C1 | ÿþÄ./ñ÷ðððððóðÅÁ 40 40 F0 F0 40 40 40 40 40 40 40 40 40 40 40 40 | @@ðð@@@@@@@@@@@@ 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 | @@@@@@@@@@@@@@@@ 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 | @@@@@@@@@@@@@@@@ 40 40 C4 00 2F F6 F3 F0 F0 F0 F0 F0 F9 F3 C5 C1 | @@Ä./öóðððððùóÅÁ C1 E2 F3 F0 C4 C1 40 40 40 40 40 40 40 40 40 40 | ÁâóðÄÁ@@@@@@@@@@ 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 | @@@@@@@@@@@@@@@@ 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 | @@@@@@@@@@@@@@@@ 40 40 C4 00 1F C9 D5 F7 F1 F1 F0 F0 F0 F1 F4 40 | @@Ä..ÉÕ÷ññðððñô@ 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 | @@@@@@@@@@@@@@@@ E5 D5 D3 F0 F0 F7 F5 F0 F0 F0 F0 F5 F3 F5 E6 40 | åÕÓðð÷õððððõóõæ@ 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 | @@@@@@@@@@@@@@@@ 40 40 C4 00 2F F1 F7 F0 F0 F0 F0 F0 F4 F0 C5 C1 | @@Ä./ñ÷ðððððôðÅÁ 40 40 F0 F0 40 40 40 40 40 40 40 40 40 40 40 40 | @@ðð@@@@@@@@@@@@ 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 | @@@@@@@@@@@@@@@@ 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 | @@@@@@@@@@@@@@@@ 0 LVL 22 Expert Comment ID: 10699622 These appear to be 64 byte records: * Fixed length records are common for mainframe compatible files. * Padding to the end of a recod with blanks is common for fixed length records. * All those '40's are blanks So, the file appears to be formatted thus: * 2-byte file header/mark/ID : FF FE I have no idea what this signifies. Unless the file is huge (~65k records or bytes), it's not a file length * Some number of 64-byte records with each record formatted as: 1-byte character 2-byte binary - either flag bits or a 16-bit integer 61-byte character, blank filled Without knowing the CPU the file is intended for, the 2-byte field can't be properly interpreted. For example, consider the 2-byte binary field in the forst record '00 2F'. Depending on the CPU, that could be either 47 or 12,032. I've got to do some outside chores right now, but I'll post some code in a few hours to dump this format. 0 Author Comment ID: 10699635 I don't know if this helps. But it is a iseries file. Legancy file, it is the same as I posted before expect not ebcdic hex. Same data if it can be converted. all I want to be able to do is convert it like the last file you did for me..... 0 LVL 22 Expert Comment ID: 10700414 This one is like HexDump, but I called it MFDump Usage: >mfdump filename In this case, the outout file is filename.r64 Option Base 1 Dim EStr As String Dim FileIn As String Dim FileOut As String Dim FileBuf As String Dim k As Integer Dim HexOut As String Dim CharsOut As String Dim ThisByte As Integer Dim RecType As String Dim Unknown As String Dim RecBody As String Dim c2, c3 As Integer Sub Main() ' Initialize EBCDIC to ASCII conversion table ' Fill in with all ~ so we define only the ones we want EStr = String(256, "~") ' Mid() doesn't like a 0 index, so this table is shifted up one. Mid(EStr, 65) = " " Mid(EStr, 76, 6) = ".<(+|&" Mid(EStr, 91, 8) = "!$\)^¬/"
Mid(EStr, 108, 5) = ",%_>?"
Mid(EStr, 123) = ":"
Mid(EStr, 130, 9) = "abcdefghi"
Mid(EStr, 146, 9) = "jklmnopqr"
Mid(EStr, 163, 8) = "stuvwxyz"
Mid(EStr, 186) = "`"
Mid(EStr, 194, 9) = "ABCDEFGHI"
Mid(EStr, 210, 9) = "JKLMNOPQR"
Mid(EStr, 227, 8) = "STUVWXYZ"
Mid(EStr, 241, 10) = "0123456789"

' Get file to dump from the command line
FileIn = Command()

' Output file is the same name with '.r64' added
FileOut = FileIn + ".r64"

Open FileIn For Binary As 1
Open FileOut For Output As 2

FileBuf = "xx"
Get #1, , FileBuf

' Now fetch the 64-byte records
FileBuf = String(64, "X")
While Not EOF(1)
Get #1, , FileBuf
RecType = Mid(EStr, 1 + Asc(Mid(FileBuf, 1, 1)), 1)
c2 = Asc(Mid(FileBuf, 2, 1))
c3 = Asc(Mid(FileBuf, 3, 1))
Unknown = Right("0" + Hex(c2), 2) + Right("0" + Hex(c3), 2)
RecBody = ""
For k = 4 To 64
RecBody = RecBody + Mid(EStr, 1 + Asc(Mid(FileBuf, k, 1)), 1)
Next k
Print #2, RecType + " " + Unknown + " " + RecBody
Wend
Close 2
Close 1
End Sub

0

Author Comment

ID: 10700542
do  you need me to post it. I am looking at it and this is actually almost what the file is suppose to look like.

D 001F IN

really is just

D IN

ok, how can I do this with in vb 6.

0

Author Comment

ID: 10700552
I was planning on have each line of the file in an access database and having the code translate it and put into another table.

I know how to do the database stuff. I just need to know how to have the code with vb.

0

Author Comment

ID: 10700626
I am going to try it with another file. This one doesn't look right....
0

LVL 22

Expert Comment

ID: 10704137
Sorry for the delay - the mother board in my main box went belly-up last night.

If a run looks odd, post the output from both HexDump and MFDump for the file. That way I'll be able to see if MFDump is screwing up.

0

Author Comment

ID: 10704429
ok, I finally figured out why it looked wierd.
What I did was copied all the data in the database to txt file. Example

the way the file was

Ä
Ä
Ä
Ä
Ä
Ä
Ä
Ä

well the format was weird because the orginal file that was tested looked like this

ÄÄÄÄÄ
Ä

so I will have to find a way to either put it into this format or ignore the crlf

also where is this coming from

D 001F IN

really is just

D IN

is there a way to make every line just look like this

D IN

meaning some are like this

002F

0

Author Comment

ID: 10707529
also found out something too.

in the database it looks like this

Ä

however when vb pulls from the database looks like this

Ä????????????????????????????

this is why the other file format is wierd.. Why is it doing this?????

I might just take the data from access dump to a txt file then convert and publish into new file, unless you have a fix for why vb is doing this to the data...

0

Author Comment

ID: 10710277
where did you go. any Ideas

0

LVL 22

Expert Comment

ID: 10713298
My development box at home is belly up until I get a new motherboard, and my other box at home is pretty much in constant use in the evenings by She Who Must Be Obeyed.

Right now I'm confused about where the file is coming from.  If you can email (addr in profile) me one of the files (<1mb)you want to read, I'll be able to look at it directly and test the reader against it.
0

Author Comment

ID: 10727559
that would be too hard to do. Being of the database file.

Question:
how can I make your above code read just any text I put into it. Either with a string result or if I even type in the wierd characters. Instead of using it for a textfile or the cmd.

Also the database I was talking about had 1 record at a time, really the reason why things were getting messed up on the formating is because what I did was copied the database table data and dumped it into textfile and ran it against your program. What happed was since each record is one line that threw off the format. In the orginal file everything is clumped together.

Can you please help me make you existing code work through a command button and with what ever the text of what I put in work....
0

LVL 22

Expert Comment

ID: 10729349
The ideal solution would be to create a flat ASCII text file from the db on the iseries box.

If all you have is a copy of the db on a PC and no access to software that reads the db format, there's little chance of reliably extracting all of the data correctly without a lot of manual effort.

Was the hex dump you posted above at 03/28/2004 03:07PM EST a dump of a file you constructed or of the actual file you're trying to read?
0

Author Comment

ID: 10731645
actually that is how one file looks. I also found out why the squares turns into ? marks.

In the database everything looks right. but when I pull the record into vb it has turned the squares into question marks and the reason is because vb is not reading it as unicode. The only way I figured that out was I copied the data into a notepad file then hit save, it then told me that the format would change if saved. I hit ok and then when I re-opened the data that was once squares were now question marks. So I did the same thing again and this time saved as format unicode. No message, reopend and the squares were there....
is there a way to make vb read in the data from the database unicode....
0

LVL 22

Expert Comment

ID: 10732746
If that dump was of one of the db files then they're definately ebcdic and not unicode, and MFDump should have produced a good ascii equivalent of the character data.  Notepad (and other unicode aware programs) will be fooled by the opening FF FE bytes, since that's the Window's way of identifying a text file as unicode.
0

Author Comment

ID: 10732891
so then how can I use your code to pull in data the exact way it looks in the database and translate it to the correct format that your last code does.

Because for what ever reason when I pull in the data from the database it makes the squares into ???.

0

Author Comment

ID: 10735129
also the code you wrote is that restricted the length of a line.

I am tsting in on another file and it seems like it is not formating some characters right and that
it is making some go to the next line, evern with my word wrap off.

and if you look the squares are exactly the same as the file with the IN in it.

the length of the line of this file is...

140

0

Author Comment

ID: 10745592
are you there
0

LVL 22

Expert Comment

ID: 10746258
Kinda.  I'm still without my development  box at home and She Who Must Be Obeyed is usually on the other one.
(I usually post on EE in the evening - little time at work during the day)

Let's get some focus back.
Was the hex dump you posted above on 03/28/2004 03:07PM EST a dump of the actual file you're trying to read?
0

Author Comment

ID: 10750533
no, because for some reason when I paste the squares into either vb or here, they turn into ?????

but in access it is ok.

also if I paste it into notepad and hit save it tells me that the format will be different and then when I reopen it the squares are ???

but if I save it as unicode it is ok.

also like I stated before it seemed like the one code you did would work, but it seemed like it couldn't go to the right anymore and that is why the format was missing.

If I could pull it in from access the way it looks and have some could decode it, that would be great. Then I could decode record at a time and add new text I need to. But I can not get it to read into vb correctly.

any ideas.
0

LVL 22

Expert Comment

ID: 10752094
That sounds a little odd in that the output of HexDump is 100% displayable ASCII.

In any case, given that the file(s) you need to access are database files in EBCDIC, it's unlikely you'll have much succes without at least creating a flat file on the iseries box.
0

Author Comment

ID: 10752432
the posting  03/28/2004 04:13PM PST,

code I think would work if it would go to the right more. The hex own you first posted is not going to be needed.

The wierd thing is that the one file works fine.

but the other one doesn't. if I look at the file on the as400 you can see that it goes to the right alot more then the first. this is why I keep asking about if there is a way to make it go to the right more.

I really need a solution fast. Can you help .

0

LVL 22

Expert Comment

ID: 10753092
"Go to the right"
I presume you mean make the extracted records longer.  But by how much?

The only way I can tell what MFDump is supposed to do is if I see the actual file you're trying to read.

Since emailing the file is not acceptable, the only other way is with a hex dump of the file.

However, since it appears you have access to the AS400, isn't there a way you can create a sequential, fixed-length, ASCII file there?

0

Author Comment

ID: 10754043
the actual file is has

188 character with spaces to the right.

I am not sure if that will help.

the wierd thing is this format is no different then the last file.
just alot more to the right this time. I can I modify to make more character go to the right. It seems like your code does a word wrap.

Also I am taken it there isn't a way to read this file into vb fro access.

Meaning way in the access database the squares and wierd symbols appear, but when I connect vb to it vb brings it in as ??????
0

LVL 22

Accepted Solution

cookre earned 100 total points
ID: 10756912
If it looks like they're 188 byte records, you can make these two changes near the end of MFDump:

' Now fetch the 64-byte records
FileBuf = String(64, "X") <-----------------------------------------------------------  change 64 to 188
While Not EOF(1)
Get #1, , FileBuf
RecType = Mid(EStr, 1 + Asc(Mid(FileBuf, 1, 1)), 1)
c2 = Asc(Mid(FileBuf, 2, 1))
c3 = Asc(Mid(FileBuf, 3, 1))
Unknown = Right("0" + Hex(c2), 2) + Right("0" + Hex(c3), 2)
RecBody = ""
For k = 4 To 64 <------------------------------------------------------------------- change 64 to 188
RecBody = RecBody + Mid(EStr, 1 + Asc(Mid(FileBuf, k, 1)), 1)
Next k
Print #2, RecType + " " + Unknown + " " + RecBody
Wend

0

Author Comment

ID: 10777891

I have a visual basic program linked into the as400 looking at a flat file.

when I pull in a line it is suppose to look like this:

Ä

however when visual basic pulls from the database looks like this

Ä????????????????????????????

then when I run it through your converter it does this:

D  ~~~~~~~~~~~~~~~

Why???
I have to link to the database on the system how can I make your code translate the ???????

0

LVL 22

Expert Comment

ID: 10778829
In MFDump I used the tilde to indicate a byte for which there was no displayable ASCII equivalent.

The reason The first two showed Ä and the converter showed D is because they presumed the file is ASCII when, in fact, the character encoding is EBCDIC. (byte code C4 is Ä in ASCII and D in EBCDIC.

As to why the ? and   characters are attempts at indicating a byte with no corresponding displayable character in the default ANSI coding scheme.

Without a hex dump of the data, about all I can conclude is that the file as read has more binary that charater data.

Have you tried the AS/400 Topic Area for help on moving files/data:
http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/
0

Author Comment

ID: 10780184
I can give you a sample of the hex version of the ebcdic would that help
0

Author Comment

ID: 10780314
also from my research it seems that it is binary data make sense???

like I stated before if I use the data transfer from client access and have the setting to No conversion the data comes in a squares, but for whatever reason if I link to the file(as400) vb pulls in in as ????

I am assuming that it is binary. Can you write something to test if it is????
0

LVL 22

Expert Comment

ID: 10780471
A hex dump is needed.  As long as enough of the data is dumped, that should be sufficient to identify the format.
0

Author Comment

ID: 10783166

this is the result

C8 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F  | È???????????????
3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F  | ????????????????
3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F  | ????????????????
3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F  | ????????????????
3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F  | ????????????????
3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F 3F  | ????????????????
3F 3F 3F 3F 00 00 00 00 00 00 00 00 00 00 00 00  | ????............

now what I did was copied the way vb brought in the file to a text file and then ran it through your hex program

0

Author Comment

ID: 10783440
ok, I am really trying to understand this. Now when I use the manual way and just dump the file to pc I get the

C8 00 1F F0 F4 F0 F4 F0 F1 E2 F1 F1 F2 F6 F3 F9  | È..ðôðôðñâññòöóù
F3 F6 F3 40 40 40 40 40 40 40 40 40 40 E2 C9 F1  | óöó@@@@@@@@@@âÉñ

this is what it is suppose to be, but when I link to as400 and pull into vb I am gtting the wierd symbols to ?????

Now if I dump into access I am getting it ok, but when I call to that access table the way it looks in access is not the way it looks in vb, it is taking the squares and characters and converting it into ?????

What can I do to prevent vb from doing that and just bring in the data as is...
0

LVL 22

Expert Comment

ID: 10783593
>>>What can I do to prevent vb from doing that and just bring in the data as is...

That I don't know - I have no idea where the data trashing is occuring, although it's likely that VB is doing it.   Actually, to be accurate, it looks less like trashing and more like VB saying "I don't know what to do with this, so I'll just use '?'"   Is the file being opened in BINARY mode?

The dump of the manual transfer looks good - we'd just like to see a bit more, say 40-50 lines worth.
0

Author Comment

ID: 10783609
the problem is that the manual way works, I need to find a a solution for what vb is doing.

0

Author Comment

ID: 10783617
if I could just get vb to read in the access data as is then this problem would be solved, but I can't and I have no other way to link to the as400......

0

LVL 22

Expert Comment

ID: 10786124
Could you post the VB code you use to fetch the file?
0

Author Comment

ID: 10786270
conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=as400datasource"
conn.Open

rs.ActiveConnection = conn

rs.Source = "Select * From as400table"
rs.Open

then what this does is uses an odbc connection to the as400 and pulls in the data from the file on the as400.

It almost seems like I need to do something with the sql command. Meaning
rs.Source = Asc("Select * From as400table")

something to maybe read the data in so that visual basic will recognize it and then have a code that translates it.

even If it reads it in Hex and then have a function that converts it from ebcdic hex to ascii

0

LVL 22

Expert Comment

ID: 10789183
I don't know SQL or AS/400, but I bet there's some translation or character code options the will fix things up.

I'll look into it to see what I can find.
0

LVL 22

Expert Comment

ID: 10791291
What I found was that I don't know enough to understand what I'm looking at.

What I'd do is post this question in bothe the AS/400
http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/

and MSSQL
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/

topic areas:

"How do I use SQL in VB to connect to an EBCDIC AS/400 database(give the name of the db vendor, e.g., Oracle) and receive ASCII on the PC?"
0

Author Comment

ID: 10791509
Can you decode this

ÿþÈ..ðôðôðñâññòö
óùóöó, please do not post the result. Some information is private.

if you can this is another format I am getting it into...

Please continue helping, i really appreciate it.
0

LVL 22

Expert Comment

ID: 10792731
The second, fourth, and fifth characters had no obvious interpretation, but all the others came out fine.
0

Author Comment

ID: 10795007
ok, I tried it with your code and it does this

reads some then ~~     ~~    ~~ and some more.

well the record length is 565 so I change that in your code, but the next line is not going there it is just adding it to the first line
example

this is a test ~~ ~~                ~~       great to be here

should be

this is a test
great to be here

some even does this

this is a test ~~               ~~          great to be h
~00ere

is this because i have the record length at 565.

is there a way to have the code just read the file and make it the way it is formatted?

I am going to raise this because you have been very patience and helping with me.

0

LVL 22

Expert Comment

ID: 10795337
If you could run HexDump against the file then post 80-90 lines here, that would be a big help.  Since the translarion table in MFDump only deals with displayable characters, I need to see how line breaks are dealt with, and also need to confirm the basic record format.
0

Author Comment

ID: 10808022
cookre, is there a way to translate a square symbol, this is ebcdic

I upgrade my client to 5.2 and It has an odbc translate check and it converts it into
the right stuff. but I am stuck with those squares, now in your hexidecimal converter the squares where subsituted for ~, because they show as what they should be meaning

c8023F8

notice the 023 that is what it is suppose to be and for whatever reason did not convert into hex so this is why your code made it a ~, well with this translater check it is briging it into as squares, now how can I make that how 023, etc...
0

LVL 22

Expert Comment

ID: 10808865
Unfortunately, the squares (or question marks, or tildes) don't arise from a single EBCDIC code.  Rather, it (they) are a generic place holder that says "I don't have anything specific to translate this particular code into, so I'll just plop a square here."

Bear in mind that any binary (int, packed decimal, float) fields encountered by a program looking only for displayable characters are going to have a fair percentage of their bytes replaced with 'I don't Know' characters, and the others will be meaningless.  The only way such fields can be dealt with is if the program reading them knows they're binary fields.

That's why I keep clamoring for a hex dump - so I can see the hex of those fields that didn't convert as characters.  For example, a 3-digit signed number stored as packed decimal would take two bytes:

byte 1, nibble 1 - leftmost digit
byte 1, nibble 2 - center digit
byte 2, nibble 1 - rightmost digit
byte 2, nibble 2 - sign (hex A, C, E, or F => positive, hex B or D => negative.

So, 023 would appear in hex as the two bytes  02 3x where x could be A, C, E, or F

For a program expecting displayable characters, the first byte, 02, not corresponding to any displayable character, will end up being represented with that program's default 'I don't know' character - a box, question mark, tilde, whatever.  The second byte, 3x, will end up displaying as a :, ;, <, =, >, or ?, depending on what 'x', the sign nibble, was.

If, on the other hand, that 023 number were stored as a 16-bit integer, it would appear in hex as 17 00, both bytes of which would show up as 'I don't Know' characters.
0

Author Comment

ID: 10862193
hello, I have a line like this

W 0234234234234          R45345345  34342342     545345

it should be W 001 0234234234234          R45345345  34342342     545345

do you have something that will convert this.

if you take this file and use your hex a dump you will see the hex output. Notice that in that hex. it comes out as 001

0

LVL 22

Expert Comment

ID: 10865369
Doing a hex dump of something posted here would just show the ansi code for a box.  Recall that the box could have come from any byte that had no displayable equivalent.

Since I don't have access to the file being dumped, I have to rely on you for the hex dump.  Since the field in question is two bytes, the hex dump would show something like '00 1F' - that would be the equivalent of 001 in packed decimal.

If that's the case, try replacing (in MFDump) the lines:

Unknown = Right("0" + Hex(c2), 2) + Right("0" + Hex(c3), 2)

dim n1,n2,n3 as integer
n1=c2\16 ' That's right, a backslash (integer divide)
n2=c2-n1*16
n3=c3\16
Unknown=right("000"+str(100*n1+10*n2+n3),3)

I'm presuming that all instances of this field will be positive - that's why I ignored the rightmost nibble of the rightmost byte.

ps: I just finished building a replacement for my development box at home (mobo died).  I don't have any compilers reloaded yet, so the above hasn't been tested.
0

Author Comment

ID: 10868674
well I guess this can't be done. I eliminated everything except for

I now have just a database with the following data in 1 record

W 0234234234234          R45345345  34342342     545345

this is what it looks exactly like. SO I guess translating the square is not possible because of it being in now ansi

so I will just have to live with it.

I really wanted and could have used the 001, etc

0

LVL 22

Expert Comment

ID: 10870598
What flavor database you're attaching to on the AS/400?  Oracle?  DB2/400?  Something else?

0

Author Comment

ID: 10871566
DB2/400, the odbc has an option to convert the data to ansi, if I take this off then I get a real mess.

0

LVL 22

Expert Comment

ID: 10873045
If DB2 is like Oracle, you can specify record layouts.
Or maybe SQL will let you specify field types.

If so, that two byte field is packed decimal.
0

Author Comment

ID: 10873235
what should I do then???
0

LVL 22

Expert Comment

ID: 10874498
Not knowing much about SQL, I'm pretty much worthless here.

See if these help:
http://www.midrangeserver.com/mpo/mpo032703-story01.html
Search in the page for 'packed' and read all that follows.

http://www.midrangeserver.com/guruo/guruo-10-19-2001.html

There are several hundred hits with the google search of:
sql field "packed decimal"  "select *" odbc
0

Author Comment

ID: 10878703
ok, I think I have a solution, but one question

I am reading from a database, when I read in the data it show a square which is a vbcr - carrige line

now the data I am pulling i am dumping to a text file how can I make that square show up. It isn't as of now.

once this is solved this case is finally closed........
0

LVL 22

Expert Comment

ID: 10880692
I'd be reluctant to presume that's a vbcr - it may have come from any number of codes with no equivalent.

In any case, if you know the location of that char in the string of text, say position 23, then

mid(TextStr,23,1)="WhateverCharacterYouWantToPutThere"

0

Author Comment

ID: 10881933
ok, bottom line is how can I make the square show up that means vbcr
0

LVL 22

Expert Comment

ID: 10884357
1) Is it always in the same position?  If so, what is that position?
2) Do you want a square, vbcr, or something else.
0

Author Comment

ID: 10888300
no it is not always in the same position, but it IS Always at The END of THE Sentence....

could you show me 1 with vbcr and 1 with the square

what's wierd is when I just read the record into vb from access the square shows, but when I dump the record into a textfile from vb the square is gone. Be nice to just have the dumping of the data to just retain whatever vb pulls in.

0

LVL 22

Expert Comment

ID: 10895419
If Option Base is 1:
Mid(TextLine,Length(TextLine),1)=vbcrlf

If Option Base is 0:
Mid(TextLine,Length(TextLine)-1,1)=vbcrlf
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an explanation of a simple data model to help parse a JSON feed
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Starting up a Project