Calc: open and save csv-file with given filter options

[kwoid]

I want to open (and save) CSV-Files in CALC with given filter settings (encoding, delimiter, quoting, etc.). It seems, that a macro could do the job and I found one for opening the file:

Sub importCSV(sURL$,sFilterOptions$)
   dim fileProps(1) as new com.sun.star.beans.PropertyValue
   ' sets the imported file properties
   fileProps(0).Name = "FilterName"
   fileProps(0).Value = "Text - txt - csv (StarCalc)"
   fileProps(1).Name = "FilterOptions"
   fileProps(1).Value = "44,34,76,1,,0,false,true,true,false"
   StarDesktop.loadComponentFromURL(sURL, "_blank", 0,fileProps())
End sub 

Unfortunately I receive an error, when executing this macro (“BASIC runtime error. Argument is not optional.”) with LibreOffice 4.3.04. There seems to be a problem with the line “StarDesktop…”.

If opening works, is there an solution how to save the active sheet with the same options using a macro to “configure” the file save dialog?

[kwoid]

Here is the code, which fits my needs: The first macro opens the file open dialog, where one can select an existing file. This file is then opened by the CSV-Filter with given defaults concerning encoding, delimiter etc. The next macro saves open the file save dialog, where one can select an existing file (which will be overwritten or specify a new file name). The active sheet is then exported as CSV with the same properties. It’s easy to get the properties specification, using the record macro feature and record a macro while a save-as-procedure.

Sub DDICSVopen
dim aUrl(), s$
Dim vDoc 'die geladene Komponente
Dim Arg()
dim fileProps(1) as new com.sun.star.beans.PropertyValue
fileProps(0).Name = "FilterName"
fileProps(0).Value = "Text - txt - csv (StarCalc)"
fileProps(1).Name = "FilterOptions"
fileProps(1).Value = "44,34,76,1,,0,false,true,true,false"
oDlg = createUnoService("com.sun.star.ui.dialogs.FilePicker")
oDlg.setMultiSelectionMode(false)
oDlg.execute
aUrl = oDlg.getFiles()
'Messagebox for debugging
's = "file:" & chr(10) & aUrl(0) & chr(10)
'msgbox s
vDoc = StarDesktop.loadComponentFromURL(aURL(0), "_blank", 0, fileProps())
End Sub


Sub DDICSVsave
dim aUrl(), s$
dim oDlg as variant
dim fileProps(1) as new com.sun.star.beans.PropertyValue
fileProps(0).Name = "FilterName"
fileProps(0).Value = "Text - txt - csv (StarCalc)"
fileProps(1).Name = "FilterOptions"
fileProps(1).Value = "44,34,76,1,,0,false,true,true,false"
oDlg = createUnoService("com.sun.star.ui.dialogs.FilePicker")
dim listAny(0) as variant
listAny(0) = com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_SIMPLE
oDlg.initialize(listAny()) ' Speichern unter
oDlg.setMultiSelectionMode(false)
oDlg.execute
aUrl = oDlg.getFiles()
thisComponent.storeAsURL(aURL(0), fileProps())
End Sub

This book helped:
Thomas Krumbein, 2007: Makros in OpenOffice.org – Basic/StarBasic, Galileo Press (http://www.worldcat.org/oclc/188235695)

Update: The updated version of DDICSVsave contains now an initialization which must not be missing in order to work with LibO 5.4. Credit goes to Regina Henschel who presented the solution here.

[arindam]

Ok. I think I have a solution for you. It took near 30 min playing around and worked finally! It tested in Ubuntu 14.04 and LO Version: 4.2.6.3.

Here it goes. I have created a simple csv file ‘test.csv’ with below content:

Now, I have edited some part of your code as below. Note in the URL section I have added “file://” in the path. Also commented out your “fileprops(1)”, I think it only accepts 1 occurrence.

Sub test()
  Dim objManager As Object
  sURL = "file:///home/arindam/Documents/test.csv"
  dim fileProps(0) as new com.sun.star.beans.PropertyValue
   fileProps(0).Name = "FilterName"
   fileProps(0).Value = "Text - txt - csv (StarCalc)"
   'fileProps(1).Name = "FilterOptions"
   'fileProps(1).Value = "44,34,76,1,,0,false,true,true,false"
   starDeskTop.loadComponentFromURL(sURL, "_blank", 0,fileProps())
end sub

After I ran - here is the output:

I will write a detailed blogpost later in my site. I gotta sleep now, its late. Let everyone know if it worked, Cheers.

[kwoid]

Thanks for your interest in my problem.

1. I would need a solution without a hard coded path and file name.
2. If the filter options are commented out, the character encoding can’t be fixed by the macro (I guess a default setting would be applied), which was the main reason for me to think about it.