# calc: open and save csv-file with given filter options [closed]

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

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-03-03 22:56:26.429615

Sort by » oldest newest most voted

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.

more

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

more

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.
( 2014-09-18 08:55:02 +0200 )edit

I tried following the same exact way you have mentioned here but still not able to get the additional box minimized. I have a project deadline wherein in need to handover the setup to client but this is the last problem I am facing. Can someone provide me a thread where this issue has been resolved

( 2017-11-13 09:28:31 +0200 )edit

thanks for sharing with our kind of information. I try to do this but facing some error. ca

( 2019-12-18 11:18:45 +0200 )edit