visual basic converting Hex to ascii character

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  
       
bear23Asked:
Who is Participating?
 
cookreCommented:
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
 
parkerigCommented:
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
 
cookreCommented:
That's not the hex representation of ASCII, it's the hex representation of EBCDIC. (the 'T' should be 'I').
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
cookreCommented:
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
 
bear23Author Commented:
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


thanks alot for your help
0
 
bear23Author Commented:
you are correct the T should be an I

0
 
bear23Author Commented:
please show the code in how it will work with my line I listed.


Thanks
0
 
cookreCommented:
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
 
bear23Author Commented:
compile error:

sub or function not defined


it is highlighting the word

from the line

AStr=AStr + EStr(OffSet)
0
 
bear23Author Commented:
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
 
cookreCommented:
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
 
cookreCommented:
Option Base 1

Private Sub Form_Load()
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
 
bear23Author Commented:
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
 
cookreCommented:
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
 
cookreCommented:
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
 
bear23Author Commented:
I am sorry, I am new to this. Where should I put the code and how should I run it.


0
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
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

' Skip over 2-byte header
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
 
bear23Author Commented:
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
 
bear23Author Commented:
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
 
bear23Author Commented:
I am going to try it with another file. This one doesn't look right....
0
 
cookreCommented:
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
 
bear23Author Commented:
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
 
bear23Author Commented:
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
 
bear23Author Commented:
where did you go. any Ideas


0
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
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
 
bear23Author Commented:
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
 
bear23Author Commented:
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

please help. I know I am asking a lot but your the only one that seems to know what I am talking about..
0
 
bear23Author Commented:
are you there
0
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
"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
 
bear23Author Commented:
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
 
bear23Author Commented:
ok, please please please hellpppppppp


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
 
cookreCommented:
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
 
bear23Author Commented:
I can give you a sample of the hex version of the ebcdic would that help
0
 
bear23Author Commented:
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
 
cookreCommented:
A hex dump is needed.  As long as enough of the data is dumped, that should be sufficient to identify the format.
0
 
bear23Author Commented:
I used your hex dump


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
 
bear23Author Commented:
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
 
cookreCommented:
>>>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
 
bear23Author Commented:
the problem is that the manual way works, I need to find a a solution for what vb is doing.


0
 
bear23Author Commented:
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
 
cookreCommented:
Could you post the VB code you use to fetch the file?
0
 
bear23Author Commented:
conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=as400datasource"
conn.CursorLocation = adUseClient
conn.Open

rs.ActiveConnection = conn
rs.CursorLocation = adUseClient
rsCursorType = adOpenStatic
rs.LockType = adLockBatchOptimistic

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
 
cookreCommented:
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
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
The second, fourth, and fifth characters had no obvious interpretation, but all the others came out fine.
0
 
bear23Author Commented:
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
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
What flavor database you're attaching to on the AS/400?  Oracle?  DB2/400?  Something else?

0
 
bear23Author Commented:
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
 
cookreCommented:
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
 
bear23Author Commented:
what should I do then???
0
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
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
 
bear23Author Commented:
ok, bottom line is how can I make the square show up that means vbcr
0
 
cookreCommented:
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
 
bear23Author Commented:
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
 
cookreCommented:
If Option Base is 1:
Mid(TextLine,Length(TextLine),1)=vbcrlf

If Option Base is 0:
Mid(TextLine,Length(TextLine)-1,1)=vbcrlf
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.