What are the odds of having an export filter for CSV instead of a SaveAs… I hate the SaveAs because it changes the current document to the CSV file. I think it is a stupid way to do it… I am creating data files for a realtime graphics app and so i send out several pages of CSV files for testing and then adjust and do it again etc… every time i send one out i have to exit everything and reload the original file etc… just seems silly… Why not an Export that does the same thing but does not change the current document?
Perhaps the easiest way to solve your problem - use short macro. The Calc document has two methods: storeAsURL (makes this URL the new location of the object) and storeToURL (continues to be a representation of the old URL).
REM Listings in the comments - http://www.pitonyak.org/oo.php
Sub exportToCSV()
Dim sURL$ As String ' URL of current workbook
Dim FileN As String ' URL of target CSV-file
Dim oCurrentController As Object ' Before save - activate sheet sSheetName
Dim storeParms(2) as new com.sun.star.beans.PropertyValue
Const sSheetName = "To CSV"
GlobalScope.BasicLibraries.LoadLibrary("Tools") ' Only for GetFileName
sURL = thisComponent.getURL()
FileN = GetFileNameWithoutExtension(sURL) & ".csv"
REM Options to StoreTo:
storeParms(0).Name = "FilterName"
storeParms(0).Value = "Text - txt - csv (StarCalc)"
REM See name of your filter vs Listing 5.45: Enumerate all supported filter names.
storeParms(1).Name = "FilterOptions"
storeParms(1).Value = "44,34,76,1,,0,true,true,true"
REM About this string see 12.4.6.Loading and saving documents in "OOME_3_0"
storeParms(2).Name = "Overwrite"
storeParms(2).Value = True
REM Activate sheet for export - select "To CSV"
thisComponent.getCurrentController().setActiveSheet(thisComponent.getSheets().getByName(sSheetName))
REM storeToURL can raises com.sun.star.io.IOException! Only now:
On Error GoTo Errorhandle
REM Export
thisComponent.storeToURL(FileN,storeParms())
MsgBox ("No Error Found,Upload file is saved : """ + ConvertFromUrl(FileN) + """.")
Exit Sub
Errorhandle:
MsgBox ("Modifications Are Not Saved,Upload File Not Generated" & chr(13) _
& "May be table " & ConvertFromUrl(FileN) & " is open in another window?")
Exit Sub
Resume
End SubOk this looks very promising… what do i do with it?
So i did some research and opened up the macro organizer and i think i opened a new macro and pasted the text into it… when i run it i get an error… Type:com.sun.star.container.NoSuchElementException Message : . it seems to be from the line for Selecting the sheet for export
Please look at this line: Const sSheetName = “To CSV”
What is the name of the worksheet that you want to save as CSV? Write it in place To CSV
Ahhhhh any way to make that just default to the current sheet that i have open?
Or maybe come up with a dialogue?
I have figured out how to get the current sheet but i also need to set a different delimiter and font…
@qholmes Download a book by Andrew Pitonyaka OOME_3_0 and see chapter “12.4.6. Loading and saving documents”. Or ask for assistance on any forum. As described in the Help: “This site…not for open-ended discussions”
Sure ok… I have that doc open… found the ref from your script and that is how i have been learning how to modify it. Thanks will carry on.
I signed up for an account just to say thank you. Been trying to figure this out all day. I just made multiple macros with this code, one for each sheet, then one main macro to run them all. Got all my sheets exported as csv. Thank you so much!!!
You can use unoconv to do the conversion to CSV from the command line after saving the most recent version of the file. In case you have to perform an “export” of the same file a lot of times, you could create a shortcut icon or something similar on your desktop/panel to do this conversion when pressed.
The command is:
unoconv -f csv some-file.ods
In case you want to specify more options during the CVS export, you can do this with unoconv (v0.6 and newer):
unoconv -f csv -e FilterOptions=44,34,76 some-file.ods
There are various other options, however most filters don’t have documentation listing these options. The one for CSV is available from: http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options
See the unoconv manual page for more information !
Good idea. Unfortunately, it is not straight-forward to run LibreOffice and unoconv at the same time, which is the whole point of this question. Error: 'Error: Unable to connect or start own listener. Aborting." Recommendation for solving this error: terminate all soffice.bin processes. Also, selecting the CSV filter options is a bit tedious. What to do if I want no text delimiter?
But it is straight-forward. Just start a separate listener process first:
unoconv -l
If you want no text delimiter, just leave an empty field in the FilterOptions
unoconv -f csv -e FilterOptions=44,,76 some-file.ods
Once you find the command you like, you can make a shell alias or shortcut and never be bothered with remembering the command-line switches again!
I use Calc to create and HTML format massive amounts raw data (upwards of 1 to 5K) Q&A for Electronic Flashcard Programs. Flashcard Data Fields must be ‘Character Delimited’ [using [CVS], [TAB],[other(~)] … what works best for me … is copy from Calc and paste to Notepad++. If necessary, I then use "Replace’ to change from "Tab Delimited’ to another delimiting character(Comma or Tilde). Then the Notepad++ file is named and saved as(name.csv) or (name.txt) … my online app is opened and the file is ‘Imported’ and mapped. The whole process takes less than 2 minutes.
I’ve been doing this for years now and I’ve never en-counted a problem uploading/importing/mapping or preserving my original/raw data file [.ods], [.xls], [.xlsx]. I also used the reverse process to scrub and repair other app user files that are not readable by the Electronic Flashcard Program.
Hope this gives you some ideas.