ASCII Stupid Question

#1
I have found a text file in the folders of my Mobile phone suite which lists the details and content of my saved text messages. I'd like to use this to export the time date and content of my texts to the Journal feature on MS Outlook.

When I open it in Notepad it looks like it should be straight forward but when I try to import it to Excel to make a table out of it the nice neat(ish) rows in the file suddenly jump about preventing me from using Text to Columns tool.

I have searched all the non-printable ASCII characters interspersed through the file to identify the rogue but can't seem to pin it down. I have used the fonts System and Terminal to make the differences between the non-printable characters more obvious but there doesn't seeem to be anything other than a space at the point the first difference between the notepad and excel presentations of the data vary.

I'm stumped for the momnet so I thought I'd throw it open to the floor. Any suggestions?
 
#2
CutLunchCommando said:
I have found a text file in the folders of my Mobile phone suite which lists the details and content of my saved text messages. I'd like to use this to export the time date and content of my texts to the Journal feature on MS Outlook.

When I open it in Notepad it looks like it should be straight forward but when I try to import it to Excel to make a table out of it the nice neat(ish) rows in the file suddenly jump about preventing me from using Text to Columns tool.

I have searched all the non-printable ASCII characters interspersed through the file to identify the rogue but can't seem to pin it down. I have used the fonts System and Terminal to make the differences between the non-printable characters more obvious but there doesn't seeem to be anything other than a space at the point the first difference between the notepad and excel presentations of the data vary.

I'm stumped for the momnet so I thought I'd throw it open to the floor. Any suggestions?
Have you tried simply copy/paste into excel? Works sometimes....
 
#4
CutLunchCommando said:
When I open it in Notepad it looks like it should be straight forward but when I try to import it to Excel to make a table out of it the nice neat(ish) rows in the file suddenly jump about preventing me from using Text to Columns tool.
Try the 'import' function, under 'File' in to Excel, rather than 'text to columns' and using a separator character. Try 'delimited' and either 'space' or 'tab' - you get a preview too. That should help.
 
#5
Idrach said:
CutLunchCommando said:
When I open it in Notepad it looks like it should be straight forward but when I try to import it to Excel to make a table out of it the nice neat(ish) rows in the file suddenly jump about preventing me from using Text to Columns tool.
Try the 'import' function, under 'File' in to Excel, rather than 'text to columns' and using a separator character. Try 'delimited' and either 'space' or 'tab' - you get a preview too. That should help.
Tried that as well Idrach. Some of the Non-Printables are readily identifiable so it would be straight forward to subtitute them with a delimiting character or a blank (to many other spaces strewn through the text to use "space" as a delimeter though, theres a 1024 characters in each line of note pad and nearly all of them are used by each message becuse of the number of spaces) before importing. There is still something lurking there that I can't identify though.
 
#6
It depends how important the information is or how keen you are but there is a possible way . It would require a knowledge of programming and a detailed knowledge of the ASCII table … the programming language " Basic " should be suitable …. free software with instruction is available on the Internet …. something along the lines detailed below could produce a solution …..

1 Establish the total number of characters as the text is imported … this should include all ASCII characters
2 Create a validation table of those ASCII characters to be allowed in the cleaned text
3 Create a routine to incrementally check each character against that list until every character has been checked and thus clean the list by removing none complyng characters
4 Import the cleaned list into Excel/Word etc if necessary using delimiters

Obviously the " Free Software " chosen must be capable of importing and exporting data .
Once created the developed program could be used repeatedly for any future transfers .


If nothing else some food for thought .

Edited .... minor addition .
 
#8
CutLunchCommando said:
Border_Reiver said:
2 Create a validation table of those ASCII characters to be allowed in the cleaned text
It's this bit I need help with. If I could hunt out the characters that aren't being exposed by using the "system" or "terminal" fonts I could do it all manually with find and replace in Notepad.

One option is to asses the ascii character value, e.g. if between 32 and 126 then it is a standard 'printable' character.




http://en.wikipedia.org/wiki/ASCII
 
#9
Use VI or VIM on the file - then at the command line

use
:set list
to display characters

use
:set nolist

to cancel it.
 
#10
tommikka said:
One option is to asses the ascii character value, e.g. if between 32 and 126 then it is a standard 'printable' character.

http://en.wikipedia.org/wiki/ASCII
I can do this with the characters that I identify by using the "System" or "Terminal" fonts. By the way I'm using those fonts to try and distinguish between the different NPC's because they show up as smiley faces, diamonds, clubs and spades and so on rather than indistinguishable little boxes as in other fonts. My problem is I need to identify an NPC that looks like a blank in a row of actual blanks that runs into the tens at least. This assumes that it is an actual ASCII character as we are refering to it and not something more exotic.
 
#12
CutLunchCommando said:
tommikka said:
One option is to asses the ascii character value, e.g. if between 32 and 126 then it is a standard 'printable' character.

http://en.wikipedia.org/wiki/ASCII
I can do this with the characters that I identify by using the "System" or "Terminal" fonts. By the way I'm using those fonts to try and distinguish between the different NPC's because they show up as smiley faces, diamonds, clubs and spades and so on rather than indistinguishable little boxes as in other fonts. My problem is I need to identify an NPC that looks like a blank in a row of actual blanks that runs into the tens at least. This assumes that it is an actual ASCII character as we are refering to it and not something more exotic.
By asess it I mean within code, and if operating in an ASCII environment then it is ASCII even when invisible or a control code.

The details in bold below can be run as an Excel macro, you need to amend ImportFile and ExportFile as required

This will read one text file, and save standard characters or a comma seperator if a non standard character is found


Sub Macro1()

Dim ImportFile, ExportFile, MyChar, CurrentRow
ImportFile = "c:\temp\import.txt"
ExportFile = "c:\temp\export.txt"

Open ImportFile For Input As #1
Open ExportFile For Output As #2
CurrentRow = ""

Do Until EOF(1) ' Loop until end of file.
MyChar = Input(1, 1) ' Get one character
If Asc(MyChar) < 32 Or Asc(MyChar) > 127 Then
'If it is before of after standard ASCII then just put in a comma
CurrentRow = CurrentRow + ","
Else
'If it is a standard character then add it to the current line
CurrentRow = CurrentRow + MyChar
End If
If (Asc(MyChar)) = 10 Then
'If its the return character then write the current line
Print #2, CurrentRow
CurrentRow = ""
End If
Loop
'As a double check due to the use of EOF then write the last line
Print #2, CurrentRow
Close 1
Close 2
End Sub
 
#13
tommikka

Your post to carry out a Character by Character check based on conditional tests within an ASCII range is similar in concept to my suggestion using Basic however your Macro writing skill in Excel would seem to offer a much neater solution .

As I said in my earlier post ... If nothing else my suggestion could prompt some food for thought .

Edited .... minor text mod .
 
#14
tommikka said:
CutLunchCommando said:
tommikka said:
One option is to asses the ascii character value, e.g. if between 32 and 126 then it is a standard 'printable' character.

http://en.wikipedia.org/wiki/ASCII
I can do this with the characters that I identify by using the "System" or "Terminal" fonts. By the way I'm using those fonts to try and distinguish between the different NPC's because they show up as smiley faces, diamonds, clubs and spades and so on rather than indistinguishable little boxes as in other fonts. My problem is I need to identify an NPC that looks like a blank in a row of actual blanks that runs into the tens at least. This assumes that it is an actual ASCII character as we are refering to it and not something more exotic.
3
By asess it I mean within code, and if operating in an ASCII environment then it is ASCII even when invisible or a control code.

The details in bold below can be run as an Excel macro, you need to amend ImportFile and ExportFile as required

This will read one text file, and save standard characters or a comma seperator if a non standard character is found


Sub Macro1()

Dim ImportFile, ExportFile, MyChar, CurrentRow
ImportFile = "c:\temp\import.txt"
ExportFile = "c:\temp\export.txt"

Open ImportFile For Input As #1
Open ExportFile For Output As #2
CurrentRow = ""

Do Until EOF(1) ' Loop until end of file.
MyChar = Input(1, 1) ' Get one character
If Asc(MyChar) < 32 Or Asc(MyChar) > 127 Then
'If it is before of after standard ASCII then just put in a comma
CurrentRow = CurrentRow + ","
Else
'If it is a standard character then add it to the current line
CurrentRow = CurrentRow + MyChar
End If
If (Asc(MyChar)) = 10 Then
'If its the return character then write the current line
Print #2, CurrentRow
CurrentRow = ""
End If
Loop
'As a double check due to the use of EOF then write the last line
Print #2, CurrentRow
Close 1
Close 2
End Sub
Fack Me.

Cheers mate I'll give that a go.
 
Thread starter Similar threads Forum Replies Date
canarybob Health and Fitness 25
JonnoJonno Health and Fitness 2
K Health and Fitness 15

Similar threads

New Posts

Latest Threads