Ask Your Question
1

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

asked 2014-09-17 16:12:30 +0200

kwoid gravatar image

updated 2016-03-03 22:56:17 +0200

Alex Kemp gravatar image

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?

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 2016-03-03 22:56:26.429615

2 Answers

Sort by » oldest newest most voted
1

answered 2014-09-23 11:57:23 +0200

kwoid gravatar image

updated 2017-08-21 10:08:11 +0200

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.

edit flag offensive delete link more
0

answered 2014-09-18 05:17:22 +0200

updated 2014-09-18 20:11:00 +0200

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: image description

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: image description

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

edit flag offensive delete link more

Comments

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.
kwoid gravatar imagekwoid ( 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

Ray Chin gravatar imageRay Chin ( 2017-11-13 09:28:31 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-09-17 16:12:30 +0200

Seen: 3,669 times

Last updated: Aug 21 '17