Encoding query - Ansi

[George J]

I am trying to force a text file to be saved with Ansi encoding.
I have many text files that i am trying to import into a worksheet - one
file per cell (they are quite small), but as these text files have been
produced via an OLE, they are unicode files. When i put the text into the
cells, it still has extra characters that i don't want so i think the only
way i can do this is to resave the text files with ansi encoding.

I had tried StrConv(temp, vbfromUnicode) but there are still extra
characters there.

I had tried using
Dim xsystem, contents, temp, newtemp
Set xsystem = CreateObject("Scripting.FileSystemObject")
Set temp = xsystem.OpenTextFile(fn)
contents = temp.readall()
temp.Close
Set newtemp = xsystem.CreateTextFile(strFilePath & "\" & "gj-" &
strFilename, TristateUseDefault)
newtemp.WriteLine (contents)
newtemp.Close

But this still saves as unicode. Is there any way to force it to save as
ANSI?

I had found this code on the net, but it gives me chinese or something.
Dim InputFile As String, OutputFile As String, TextChar As String
InputFile = fn ' replace with actual path to input file
OutputFile = strFilePath & "\" & "gjj-" & strFilename ' replace with output
path
Open InputFile For Input As #1 ' Open file.
Open OutputFile For Output As #2 ' Open the output file: will either create
it or overwrite existing file
Do While Not EOF(1) ' Loop until end of file.
TextChar = Input(1, #1) ' Get one character.
' Perform any desired conversion here
Print #2, TextChar; ' Wirte to output file
Loop
Close #1 ' Close files
Close #2

Result: ഠ倊牥桴à¨à´ åŒŠç¥ãˆ ã€°à´·äœŠæ½²ç‘·â¡¨â¤¥â€ºà¨ã¤±ã€®à´¶à´Š

I know you can save as Ansi in notepad, but due to the number of files, i
would really like to be able to do this in excel. I can get the text file
contents into the cells, it's just getting the text to be "normal" text.

I found some information in a VB forum:
My.Computer.FileSystem.WriteAllText("c:\x.txt", ControlChars.NewLine &
"ã‚ã ï½“ã ï½“ｄ", True, System.Text.Encoding.Default)

Third parameter "True" specifies appending and fourth parameter specifies
ANSI depending upon ur systems ANSI code page.

But have no idea if this is of any use for VBA.

Any tips or advice most welcome.
George

 

[joel]

from
Set temp = xsystem.OpenTextFile(fn)

to
TristateFalse = 0
Set temp = xsystem.OpenTextFile(filename:=fn,format:=TristateFalse)


TristateFalse

 

[George J]

Thanks Joel, but the new workbook is still saved as unicode - not Ansi.

Is there something i need to do to
Set newtemp = xsystem.CreateTextFile(strFilePath & "\" & "gj-" &
strFilename, TristateUseDefault)

thanks
George

 

[joel]

Saving as text won't remove the control characters. Unicode is really 16
bit data and ascii is 8 bit data. Saving as text just changes the byte
structure of the data and doesn't eliminate the data


Unicode

1234
5678
9ABC


Ascii

12
34
56
78
9A
BC

 

[George J]

Hmmm

When i look at the text from the file in the VB Editor, it looks like:
http://img412.imageshack.us/my.php?image=new1f.jpg
and displays in the cell as:
ÿþM

But in the text file it is displayed as "Mar 2003".
When i change the encoding manually for that file from unicode to ANSI, it
appears in both the VBE and in the cell as "Mar 2003".
This is the file:
http://www.filefactory.com/dlf/f/ag...c83fe843e5bea09c79171a53b8ba/j/0/n/text20_txt

I thought i might be able to save the files that are currently there only
with ANSI encoding. Would i need to try doing this using sendkeys and
notepad?

 

[joel]

I don't download files from these two sites because they create Ad-Ware on my
PC. Use something like Savefile.com instead.

There are some white characters ( control characters) that you don't see
that is causing the problems.

 

[George J]

It appears to be a ? that when replaced removes all the text from the cell.

I uploaded the small file i was using as an example, one of the larger files
and an image of what is being seen in the VBEwhen trying to get the text into
a cell.

http://www.savefile.com/projects/808757320
Thanks for lookign at this.

 

[joel]

I had a similar problem a couple of weeks ago. Modify my old code below.
the 1st macro will put on the worksheet what the data actually looks like.
column A is the character and column B the Ascii code. the second program
will extract the asci data from the unicode only keepin the odd characters.

Sub test()

Const ForReading = 1, ForWriting = -2, _
ForAppending = 3

Dim ReadData

TABChar = 9


ReadFile = Application _
.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Read File")
If ReadFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

Open ReadFile For Binary As #1


'Set fs = CreateObject("Scripting.FileSystemObject")
'Set fin = fs.OpenTextFile(ReadFile, _
' ForReading, TristateTrue)

ActiveSheet.Cells.ClearContents

RowCount = 1
Do While Not EOF(1)
ReadData = Input(1, #1)
' ReadData = fin.Read(1)
If Not EOF(1) Then
Range("A" & RowCount) = ReadData
Range("B" & RowCount) = Asc(ReadData)
RowCount = RowCount + 1
End If
Loop
Exit Sub

fin.Close

End Sub

Sub GetText()
Const ForReading = 1, ForWriting = -2, _
ForAppending = 3


LFChar = 10


ReadFile = Application _
.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Read File")
If ReadFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If


Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, _
ForReading, TristateTrue)

ActiveSheet.Cells.ClearContents

'Only Read Odd bytes, data i s unicode
Odd = True

ReadLine = ""

RowCount = 1
FirstRow = True
Do While fin.AtEndOfStream <> True
ReadData = fin.Read(1)

If Odd = True Then
a = Asc(ReadData)
Select Case Asc(ReadData)
'ignore UniCode
Case Is >= 128
'skip

Case Else
ReadLine = ReadLine & ReadData
If Asc(ReadData) = LFChar Then
Range("A" & RowCount) = ReadLine
ReadLine = ""
RowCount = RowCount + 1
End If
End Select

Odd = False
Else
Odd = True
End If

Loop

fin.Close


End Sub

 

[George J]

Many thanks Joel.
I really appreciate you taking the time to look at this.

I'll try the code tomorrow when i'm back in work. Got my 4yo neice sleeping
over so don't have access to my home computer for a while.

Right now i'm just glad it wasn't me missing something obvious in getting
the text from the file. From looking at those macros, i can safely say i
would not have even got close.

 

[joel]

If youimport the data using the menu you get the same results as my code

Data - Import External Data - Import Data

Choose text file and browse for the folder.

 

[George J]

Thanks Joel

Had a little play around with your code (it told me the file was already
open, but was quite easy to bypass this). Both of those macros are fantastic.

As i have hundreds of text files that will be going into the workbook i was
really hoping this could be automated and thanks to you i will now be able to
do this. It's good to know that the unicode text files won't need to be
converted too. The 2nd macro putting each line of text in its own cell will
make this much easier for me to manipulate. All i need to do is remove the
char(10+13) and i'll be able to manouver the data as required.

I can't tell you how grateful i am for your patience and efforts in helping.
This is going to be a huge help to use and our client.

Many thanks again
George