| Author |
Topic |
|
vguptais
Starting Member
8 Posts |
 Posted - 2007-10-12 : 11:11:38
|
| Hello,I extracted some data using sql query, when i check this data in sql itself it is alright but when i transfer it to excel i get some strange digits. For example in SQL the data is like 73072910- 3917321039173210391732103917321039173210But when transported to excel it looks likeF7F3F0F7F2F9F1F04040F3F9F1F7F3F2F1F04040F3F9F1F7F3F2F1F04040F3F9F1F7F3F2F1F04040F3F9F1F7F3F2F1F04040F3F9F1F7F3F2F1F04040I wonder if somebody can help me in this problem.ThanksVikrant |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 11:18:30
|
| How are you "transferring" the data to Excel?Kristen |
 |
|
|
vguptais
Starting Member
8 Posts |
Posted - 2007-10-12 : 11:23:53
|
| Hi Kristen,I am using an excel tool, 'Transfer data from i series', this tool is available in excel itself.Vikrant |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-12 : 11:24:44
|
| That's the hex representation of the character data so looks lie something's doing a conversion.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vguptais
Starting Member
8 Posts |
Posted - 2007-10-12 : 11:26:16
|
| Hi Christine,I also tried 'client access' to transfer data from AS400 to excel but i am getting the same resultREgardsVikrant |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 11:27:25
|
"That's the hex representation of the data"It is? My school teacher told me that Hex was never longer than decimal equivalent  |
|
|
|
vguptais
Starting Member
8 Posts |
Posted - 2007-10-12 : 11:27:56
|
| Hi Christine,Do you know any way in SQL (or in excel) to conver this HEX expression to the normal character expression.Thanks for your helpVikrant |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-12 : 11:28:09
|
| What are you extracting data from?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 11:30:00
|
| "Do you know any way in SQL (or in excel) to conver this HEX expression to the normal character expression"Google.73072910 in HEX0xF7F3F0F7F2F9F1F04040 in DECIMALWindow calculator will do it too I expect.Kristen |
|
|
|
vguptais
Starting Member
8 Posts |
Posted - 2007-10-12 : 11:32:08
|
| Hi nr,I am extracting data from an sql table which resides on our AS400. when i see that data in sql itself by SELECT * FROM ....it shows me the right values but when i tranfer it to excel it gives me something bizarre expression (maybe hex i dont know)I am facing this problem with this table only, rest all are ok while transfering.Vikrant |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-12 : 11:32:47
|
| It's ebcdic data which makes it a bit more tricky but you can do it in a macro.One way to have a character string of all the characters, convert the hex value to a decimal and use that as an offset into the string and loop for each pair.If you do a search you'll probably find other way's.I would be a bit surprised if the tool you are using doesn't have an option to convert.Here's the character sethttp://www.natural-innovations.com/computing/asciiebcdic.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vguptais
Starting Member
8 Posts |
Posted - 2007-10-12 : 11:34:09
|
| hehehehe Kristen,I really dont know whether these expressions are HEX or something else. And trust me i am really not intrested to know if i get some solution to change it back to normal mode.:-)Vikrant |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 11:35:08
|
"It's ebcdic data"Ah!Google doesn't do that one  |
|
|
|
|
conversion problem