Change text encoding for reading from text file (Macro)

[Guilty_Spark_343]

Hi,

i have written a macro that reads line after line of a text file into a string variable:

open file_name for input as file_number
line input #file_number, string_variable

In order to be imported correctly, my text file has to be interpreted as ANSI encoded.
However Libre Office seems to interpret it as UTF-8 encoded.
This causes certain special characters (e.g. ä, ß …) to be incorrectly displayed as some strange (?) symbol.

How can i make Libre Office interpret my text file as ANSI encoded?

EDIT:
I realise that i have to use code that looks somewhat like this:

Dim MyDoc As Object
Dim props(1) As New com.sun.star.beans.PropertyValue
Dim File_Number As Integer
Dim String_Variable As String

props(0).Name = "FilterName"
props(0).Value = "Text - txt - csv (StarCalc)"
props(1).Name = "FilterOptions"
props(1).Value = "44,34,76,1,,1031,true,true" 
MyDoc = StarDesktop.LoadComponentFromURL(ConvertToURL("file_name.txt"), "_blank", 0, props() )

FileNo = FreeFile
Open Mydoc For Input As File_Number
Line Input #File_Number, String_Variable

Unfortunately this codes fails at “Open Mydoc For Input As File_Number”. The error Message is: “BASIC runtime error. Object variable not set”

Any Ideas?

Best Regards,
Sebastian

[Regina]

There is a macro in Calc: open and save csv-file with given filter options only a line dim oDlg as variant is missing there.

Value 76 means UTF-8. For the meaning of the parameters see Filter Options - Apache OpenOffice Wiki. You need to know the encoding exactly to get the correct number from the table “Character set”.

This is for import in Calc. For Writer you need a different filter.

If your source is not a csv file, but you only want to read a file line by line, then you can use these macros.

For to get the path to the file:

Function GetPath as string
Dim oFileDialog as variant
Dim listAny(0) as Long
Dim sPath as string: sPath=""
oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
oFileDialog.Initialize(ListAny())
Dim iAccept as integer
iAccept = oFiledialog.Execute()
if iAccept = 1 then
    sPath = oFileDialog.Files(0)
end if
oFileDialog.Dispose()
GetPath = sPath
end function

And then for to read the lines

sub LineRead_Win1252
dim sPath as string: sPath=""
dim oUcb as variant 'for css.ucb.SimpleFileAccess
dim oInputStream as variant
dim sInputLine as string
sPath = GetPath()
if sPath <>"" then
    oUcb = createUnoService("com.sun.star.ucb.SimpleFileAccess")
    if oUcb.Exists(sPath) then
	    oInputStream = createUnoService("com.sun.star.io.TextInputStream")   
	    oInputStream.setInputStream(oUcb.OpenFileRead(sPath))
	    oInputStream.setEncoding("windows-1252")	
	    do while NOT oInputStream.isEOF() 
              sInputLine = oInputStream.readLine()
              rem do something with the text line
              msgbox(sInputLine)
        loop	  
        oInputStream.closeInput()
     else
         msgbox "File does not exist"
     end if
else
    msgbox "No Filename"
end if		
msgbox "Finished"
end sub

Find the strings for the encoding in column 2 in Character Sets

[Guilty_Spark_343]

Hi Regina,

big thanks again! This code works for me and solved my problem!

You asked what i want to do with the lines i read from the file: i need to extract some numbers from specific positions in specific lines of the file and perform calculations with them.

By the way: your assumption that it’s not a CSV-File i’m trying to read was correct. It is a plain text file.

Next time i’ll provide such information when i have a question.

Also, next time I’ll try to use API-Methods from the start.

[Regina]

There are no examples in the API. Some examples are in the SDK. To learn more about Basic in LibreOffice, you can start with the book http://www.pitonyak.org/OOME_3_0.pdf by Andrew Pitonyak. To examine an object in your code use Xray. Or you set a break point and use the Watch-window. You find the methods in Types->Types(n)->Methods->Methods(k): ReturnType,ParameterTypes,ParameterInfos. Use SupportedServicesNames to know where to look in the API.

[Guilty_Spark_343]

Thank you Regina,

we are getting closer!

i determined the FilterOptions i need for my text file by manually opening it and choosing the needed import settings trough the GUI. Then i ran this code i found on the internet on the imported file:

Sub showFilterOptions
Dim args(),i%
args() = thisComponent.getArgs
for i = 0 to uBound(Args())
    if args(i).Name = "FilterOptions" then inputbox "","",args(i).value
next
End Sub

This results in a window popping up that shows the FilterOptions i have to use in my macro to get the same results. "FIX,34,1,1,0/1/2147483647/9,1031,false,false"

Further on i found one reason why my code didn’t run. For some reason i need to provide the complete path to the file for the StarDekstop.loadComponentFromURL to work, although the text file lies in the same folder as the file running the macro.

The improved - but still buggy - code is:

Dim MyDoc As Object
Dim props(1) As New com.sun.star.beans.PropertyValue
Dim File_Number As Integer
Dim String_Variable As String

props(0).Name = "FilterName"
props(0).Value = "Text - txt - csv (StarCalc)"
props(1).Name = "FilterOptions"
props(1).Value = "FIX,34,1,1,0/1/2147483647/9,1031,false,false"
MyDoc = StarDesktop.LoadComponentFromURL("file:///<path>/<file_name>", "_blank", 0, props() )

File_Number = FreeFile
Open Mydoc For Input As File_Number
Line Input #File_Number, String_Variable

When the MyDoc = StarDesktop.LoadComponentFromURL is executed a new LibreOffice Window shows up displaying the contents of the imported file. The encoding is interpreted correctly - yay!

Unfortunately I do not want the user to see a new window appear and i want the code to have high performance.

I think the best way to achieve this is to read line after line of the imported file using code like Open MyDoc For Input As File_Number. How can i do this with correct text encoding?

Alternatively i could read line after line in the newly opened LibreOffice window with the imported file and then close it. How can i hide this window to avoid the user seeing appearing and disappearing windows?

Best Regards,
Sebastian

[Regina]

What do you want to do with the read lines? If you do not want a document, then the service cum.sun.star.io.TextInputStream might work better for you. It has methods readLine() and setEncoding() in interface XTextInputStream. LibreOffice: TextInputStream Service Reference Do not use the legacy Basic file access methods, but always use the API methods.