Ask Your Question
1

Export to CSV instead of SaveAs [closed]

asked 2012-06-18 17:42:15 +0200

qholmes gravatar image

updated 2013-05-03 16:58:10 +0200

manj_k gravatar image

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?

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 2015-11-16 18:06:49.695953

3 Answers

Sort by » oldest newest most voted
4

answered 2012-06-18 19:58:12 +0200

JohnSUN gravatar image

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 Sub
edit flag offensive delete link more

Comments

Ok this looks very promising.. what do i do with it?

qholmes gravatar imageqholmes ( 2012-06-18 20:55:32 +0200 )edit

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

qholmes gravatar imageqholmes ( 2012-06-18 21:14:38 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2012-06-18 21:44:12 +0200 )edit

Ahhhhh any way to make that just default to the current sheet that i have open?

qholmes gravatar imageqholmes ( 2012-06-18 21:56:27 +0200 )edit

Or maybe come up with a dialogue?

qholmes gravatar imageqholmes ( 2012-06-18 21:59:16 +0200 )edit

I have figured out how to get the current sheet but i also need to set a different delimiter and font..

qholmes gravatar imageqholmes ( 2012-06-18 22:29:23 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2012-06-19 06:51:02 +0200 )edit

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.

qholmes gravatar imageqholmes ( 2012-06-19 16:39:03 +0200 )edit

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!!!

jjdoc gravatar imagejjdoc ( 2017-05-15 20:24:54 +0200 )edit
0

answered 2013-12-23 05:55:28 +0200

Tomincnj gravatar image

updated 2013-12-23 05:57:17 +0200

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.

edit flag offensive delete link more
0

answered 2012-08-31 21:19:32 +0200

dag gravatar image

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 !

edit flag offensive delete link more

Comments

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?

Jan-Philip Gehrcke gravatar imageJan-Philip Gehrcke ( 2013-12-18 18:22:17 +0200 )edit

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!

Todd Wilson gravatar imageTodd Wilson ( 2014-02-28 15:27:22 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2012-06-18 17:42:15 +0200

Seen: 17,258 times

Last updated: Dec 23 '13