How to set up FilterOptions to open/import a CSV file?

[FelipeAle]

I have a CSV file that I want to open and manipulate using a macro.
I set some properties though. However, it seems that the FilterOptions is not working properly, I don’t know why.
When I use the Code Format to each table column, it does not open in the pattern I want.

It will be used in Windows 10.

To understand what I’m saying access the link: Filter Options - Apache OpenOffice Wiki

Macro:

Sub AbrirManipular()
'Defini as variáveis para trabalhar com o documento
Dim oDoc as Object : oDoc = ThisComponent
Dim oSheet as Object : oSheet = oDoc.Sheets
rem-----------------------------------------------------------------------------------------------------
'Defini variáveis que serão utilizadas para abrir o arquivo CSV (DADOSMATRICULA)
Dim UrlMatr as String, CsvMatr as Object, PropsMatr(2) as New com.sun.star.beans.PropertyValue
'PropsMatr(0).Name = “Hidden”
'PropsMatr(0).Value = true
PropsMatr(1).Name = “FilterName”
PropsMatr(1).Value = “Text - txt - csv (StarCalc)”
'OpçõesFiltro = Op_Separadores(Tabela ASCII), Delimitadores de texto, Conj. de 'Caracteres(padrão=0), n. da 1ª linha(padrão=1), FormatoColunas (coluna/CodFormato:padrão=1), 'idioma(padrão="" ou 0), OutrasOp: FormataComoCampoTexto(padrão=false), Detecta n. 'Especial(padrão=false)
PropsMatr(2).Name = “FilterOptions”
PropsMatr(2).Value = “59/9, 34, 9, 1, 1/10, 2/4, 3/9, 4/1, 5/1, 6/9, 7/1, 8/1, 9/1, 0, false, false”
'Caminho do arquivo CSV
UrlMatr = ConvertToUrl (“C:\Users\felipe\Downloads\ParaTeste.CSV”) 'Windows
'Abre o arquivo CSV
CsvMatr = StarDesktop.loadComponentFromUrl (UrlMatr, “default”, 0, PropsMatr() )
End Sub

File with the macro:
MacroFiltroCSV.ods (9,3,KB)

File to open with the macro. Please, after downloading change the file extension for .csv:
ParaTeste.ods (16,6,KB)

I already ask for help in another topic in Portuguese, but nobody showed up to help me.

EDIT

The macro below was very helpful for working on both Linux and Windows OS. Therefore, I will change the solution to facilitate the search of other users. Thank you very much @Villeroy!

[erAck]

@FelipeAle

PropsMatr(2).Value = “59/9, 34, 9, 1, 1/10, 2/4, 3/9, 4/1, 5/1, 6/9, 7/1, 8/1, 9/1, 0, false, false”

Coming across this again, additionally to what was said already, do not add spaces to the arguments!

[FelipeAle]

Yep!

It was my lack of attention. Thanks!

[Lupp]

@FelipeAle

PropsMatr(2).Name = “FilterOptions”
PropsMatr(2).Value = “59/9, 34, 9, 1, 1/10, 2/4, 3/9, 4/1, 5/1, 6/9, 7/1, 8/1, 9/1, 0, false, false”

Your ‘Value’ is containing 16 tokens while there are “only” 5 I know of .
The first token 59/9 qualifies the semicolon and the HT as the field (column) separators used.
The second token 34 defines the usual doublequote as the text delimiter.
The third token (9 in your case) should describe the character set. I would suggest you better use 0 (‘system’). The supposed meaning of 9 I don’t know. Typo?
Fourth token 1 is OK if you want to start the import with the first line.
Fifth token is rotten. You may mean 1/10/2/4/3/9/4/1/5/1/6/9/7/1/8/1/9/1 where I don’t understand for what reason you use 1/10 (first column: override locale by US), and want to ask if you actually want to NOT import the third and the sixth column. (The fifth column is a lot of html. Do you want that?
The tail , 0, false, false I don’t understand at all. It doesn’t relate to any setting I would know of, but there may have been enhancements I missed.
Cf. Subchapter 12.4.6. Loading and saving documents of Andrew Pitonyak’s “OpenOffice.org Macros Explained”.

[FelipeAle]

I’m still learning how to program in Basic.

Now I see that it was a newbie’s mistake. hahahaha

Actually, I used this macro just for study. I intend to integrate this macro in another one.

The valeu 9 in the third token was because in the Character Set Table, the index correspond to default system.

The first column in the fifth token would be because the first column correspond to numbers. However, with you asking, I understood that I can put 1/1, due to the seventh token.

The tail 0, false, false,
matches with language (token 6), quoted as text field (token 7), and detect special numbers (token 8), respectively.

I’ll use it to import a file and format it.

Thanks for the help @Lupp !

[FelipeAle]

I’m sorry, I almost forgot.
The Csv file which I’ll work has HTML tags in the information described, in the fifth column, to facilitate visualization of important information, and make reading more agile to the end user on the system.

[joshua4]

OpenOffice.org accidentally links to the Open Office main site–Lupp never intending for it to be a site at all. The book referred to by @Lupp is at https://www.pitonyak.org/OOME_3_0.pdf.

[FelipeAle]

Thank you!

I understand the note just now. I didn’t use a translator at the time and I got lost in the conversation. My English is pretty rusty.
Sorry!

[Villeroy]

Import your csv manually with all required options correctly set.
Run the following macro and copy the filter options from a input box.

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

[Lupp]

What do you know about the tail ,0,false,true,true,false,false I get the described way?

[Ratslinger]

@Lupp

What do you know about the tail

Possibly → Filter Options

[Lupp]

Thanks! That’s a source I miss to think of much too often. Age?

However: MS_Works_Calc is missing. The import filter still is working flawlessly for me in LibO 7.2.3.2 and also in AOO 4.1.7. It even asks for the character set with the reasonable suggestion (no FilterOptions there). Just tested again with a file from 1994.
Things are strange, and their documentation must therefore also be?
Should I try if the mentoined filter also is usable for export?

[erAck]

Meanwhile Calc CSV import/export knows 13 parameters. Continuing 1-based:

• token 10: Save cell formulas, export; true if so, default false
• token 11: Trim spaces, import; true if so, default false
• token 12: Sheet to export, export; 0 for current or if not present, -1 for all, or 1-based sheet number
• token 13: Evaluate formulas, import; true if so or if not present, if present default false

For token 12 see 7.2 release notes.
For token 13 see 7.3 release notes.

[Lupp]

Is there an updated specification somewhere?

[Villeroy]

Specifications Pissifications Nullifications. This is about free software in a meritocratic project! Anybody contributing anything is right.

[Lupp]

StarOffice 8 Programmierhandbuch (German edition, SUN 2005 - very professional, “Teilenr.: 819–1326–01”) mentions FilterOptions, but doesn’t specify or exemplfy a single one.

[Villeroy]

Even if properly documented, the FilterOption string is so complicated that you may waste time and mental health until you get it right. My little macro does that job for you after you did your job on the user interface (whih is difficult enough).

@erAck

Meanwhile Calc CSV import/export knows 13 parameters. Continuing 1-based:

IMHO, more options won’t help. If “detect special numbers” were the default, most questions on wrong text import would not arise. Before “special numbers” (OOo 2?) people became upset because strings like “2/4” were interpreted as dates. This could be fixed easily importing the offending column as text.

Then someone introduced “detect special numbers” (default: no). Since then the majority of new users imports wrong text because the interpretation of numeric strings would be correct in the vast majority of practical use cases.

[erAck]

The macro does a neat job.

For the defaults, new FilterOptions parameters added preserve the behaviour of existing macros that do not know the new options. What is made default in the UI’s import dialog can and may be a different thing.

And yes, documentation should gather all options in one place, preferably the help system.

[erAck]

Which was done for the 7.3 help thanks to Mike and Olivier and tdf#140779.

[Lupp]

Thanks a lot to the authors!