Ask Your Question

Change text encoding for reading from text file (Macro) [closed]

asked 2017-08-01 15:10:11 +0100

Guilty_Spark_343 gravatar image

updated 2017-08-02 09:59:33 +0100


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
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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-12 00:23:03.429830

2 Answers

Sort by » oldest newest most voted

answered 2017-08-02 14:18:46 +0100

Regina gravatar image

updated 2017-08-02 23:30:12 +0100

There is a macro in only a line dim oDlg as variant is missing there.

Value 76 means UTF-8. For the meaning of the parameters see 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("")
Dim iAccept as integer
iAccept = oFiledialog.Execute()
if iAccept = 1 then
    sPath = oFileDialog.Files(0)
end if
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("")
    if oUcb.Exists(sPath) then
        oInputStream = createUnoService("")   
        do while NOT oInputStream.isEOF() 
              sInputLine = oInputStream.readLine()
              rem do something with the text line
         msgbox "File does not exist"
     end if
    msgbox "No Filename"
end if      
msgbox "Finished"
end sub

Find the strings for the encoding in column 2 in

edit flag offensive delete link more


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.

Guilty_Spark_343 gravatar imageGuilty_Spark_343 ( 2017-08-04 10:15:16 +0100 )edit

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 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.

Regina gravatar imageRegina ( 2017-08-04 14:12:18 +0100 )edit

answered 2017-08-02 16:07:39 +0100

Guilty_Spark_343 gravatar image

updated 2017-08-02 16:17:41 +0100

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
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
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

edit flag offensive delete link more


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

Regina gravatar imageRegina ( 2017-08-02 19:47:07 +0100 )edit

Question Tools

1 follower


Asked: 2017-08-01 15:10:11 +0100

Seen: 4,728 times

Last updated: Aug 02 '17