OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Convert SXC to CSV from commandline
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
ThomasB
General User
General User


Joined: 19 Mar 2004
Posts: 5

PostPosted: Sat Mar 20, 2004 12:47 pm    Post subject: Convert SXC to CSV from commandline Reply with quote

Basically I have some openoffice spreadsheets and I need to convert them to CSV in
order for another program to parse them and extract some data from them. The
thing is I need the delimiter to be the "@" and not the comma",". I have the code
that was posted forum in the following post:

http://www.oooforum.org/forum/viewtopic.php?p=14180#14180

I have the code working from the commandline (sort of ) . When I call the macro
it creates a file called "<filename>.csv" but it contains no data. I thought the code
was not working until I changed the filter to HTML. When the filter is set to HTML
it outputs a nice HTML table of the sxc file. I first set the filter to "Test - txt - csv (StarCalc)"
with no FilterOptions ( thinking I could just get it to default to the comma delimiter and
work with it from there) but the code just spits out a blank file of the right name.
I have tried to give the filter a FilterOptions statement but the same thing happens.
I have been running the macro on a small file. Hoping to get the following output
which is what I get when I convert the document inside OpenOffice.

"Name:"@"Bob"@"Tom"@"Mike"
"Age:"@23@24@45
"Dues:"@12.45@15.54@16.24
"Experience:"@12@15@30
"Car:"@"Blue"@"Red"@"Silver"



Any ideas?
Thanks in advance.
Thomas
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Mar 20, 2004 2:17 pm    Post subject: Reply with quote

I have tried the following code
Example 0:
Code:
Sub Main
   oDoc = ThisComponent
   oDoc.storeToURL( _
      ConvertToURL( "/home/danny/Desktop/test.csv" ),_
      Array( _
         MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ),_
         MakePropertyValue( "FilterOptions", _
            CStr(Asc("@"))+","+CStr(Asc(""""))+",0,1,1/1" ) ) )
End Sub


Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
   oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" )
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   EndIf
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   EndIf
   MakePropertyValue() = oPropertyValue
End Function


in a spreadsheet with the necessary values in its cells, here is what I get as the generated CSV file.
Output from Example 0:
Code:
"Name:"@"Bob"@"Tom"@"Mike"
"Age:"@23@24@45
"Dues:"@12.45@15.54@16.24
"Experience:"@12@15@30
"Car:"@"Blue"@"Red"@"Silver"


In order to write the FilterOptions string, I referred to this part of the Developer's Guide...
http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.htm#1+2+2+3+Filter+Options

Next, I tried this example which creates a completely new empty spreadsheet, fills it with values, and then saves it to CSV.
Example 1:
Code:
Sub Test2
   oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() )
   oSheet = oDoc.getSheets().getByIndex( 0 )

   ' Fill in the values.
   oSheet.getCellRangeByName( "A1" ).setString( "Name:" )
      oSheet.getCellRangeByName( "B1" ).setString( "Bob" )
      oSheet.getCellRangeByName( "C1" ).setString( "Tom" )
      oSheet.getCellRangeByName( "D1" ).setString( "Mike" )
   oSheet.getCellRangeByName( "A2" ).setString( "Age:" )
      oSheet.getCellRangeByName( "B2" ).setValue( 23 )
      oSheet.getCellRangeByName( "C2" ).setValue( 24 )
      oSheet.getCellRangeByName( "D2" ).setValue( 45 )
   oSheet.getCellRangeByName( "A3" ).setString( "Dues:" )
      oSheet.getCellRangeByName( "B3" ).setValue( 12.45 )
      oSheet.getCellRangeByName( "C3" ).setValue( 15.54 )
      oSheet.getCellRangeByName( "D3" ).setValue( 16.24 )
   oSheet.getCellRangeByName( "A4" ).setString( "Experience:" )
      oSheet.getCellRangeByName( "B4" ).setValue( 12 )
      oSheet.getCellRangeByName( "C4" ).setValue( 15 )
      oSheet.getCellRangeByName( "D4" ).setValue( 30 )
   oSheet.getCellRangeByName( "A5" ).setString( "Car:" )
      oSheet.getCellRangeByName( "B5" ).setString( "Blue" )
      oSheet.getCellRangeByName( "C5" ).setString( "Red" )
      oSheet.getCellRangeByName( "D5" ).setString( "Silver" )

   ' Export it to CSV.
   oDoc.storeToURL( _
      ConvertToURL( "/home/danny/Desktop/test.csv" ),_
      Array( _
         MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ),_
         MakePropertyValue( "FilterOptions", _
            CStr(Asc("@"))+","+CStr(Asc(""""))+",0,1,1/1" ) ) )
End Sub

The code of Example 1 requires the MakePropertyValue() function from Example 0, or available here...
MakePropertyValue function
http://www.oooforum.org/forum/viewtopic.php?t=5108

The Example 1 program generates a CSV file like this.
Output of Example 1:
Code:
"Name:"@"Bob"@"Tom"@"Mike"
"Age:"@23@24@45
"Dues:"@12.45@15.54@16.24
"Experience:"@12@15@30
"Car:"@"Blue"@"Red"@"Silver"



Can you get Example 1 to work?

Also, I'm moving this over to the Macros and API section which is (1) more relevant, and (2) you might get some answers from people who are more expert than I at using Calc and the knyptions that might be required in order to use the CSV export filter.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
ThomasB
General User
General User


Joined: 19 Mar 2004
Posts: 5

PostPosted: Sat Mar 20, 2004 8:11 pm    Post subject: Reply with quote

I created the Example1 that creates the spreadsheet and then exports it and it does
produce the correct CSV file. The code that I was working on follows:

Sub ConvertSheet2CSV( cFile )
cURL = ConvertToURL( cFile )
oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0,_
Array( MakePropertyValue( "Hidden", True )))
cFile = Left( cFile, Len( cFile ) - 4 ) + ".csv"
cURL = ConvertToURL( cFile )

oDoc.storeToURL( cURL,_
Array(_
MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ),_
MakePropertyValue( "FilterOptions", _
CStr(Asc("@"))+","+CStr(Asc(""""))+",0,1,1/1" ) ) )

oDoc.close( True )
End Sub

Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" )
If Not IsMissing( cName ) Then
oPropertyValue.Name = cName
EndIf
If Not IsMissing( uValue ) Then
oPropertyValue.Value = uValue
EndIf
MakePropertyValue() = oPropertyValue
End Function

The commandline that I'm using is:
soffice -invisible "macro:///MyLibrary.Conversion.ConvertSheet2CSV(/home/thomas/Test.sxc)"


I thought I had figured it out but it still doesn't work. I'm a bit confused with the CStr
commands. Why are there 4 double quotes for the Text Delimiter portion?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Mar 20, 2004 8:47 pm    Post subject: Reply with quote

Normally, you enclose a "string" in double quotes. If you need to put a double quote into a string, you need two double quotes...

Print "She said: ""Hello World"""

When printed, this would produce....

She said: "Hello World"

The CStr() function takes any numeric value, such as 64, and creates a string like "64". The Asc() function takes any character such as "@" and returns its ascii numeric value, such as 64. So CStr( Asc( "@" ) ) is a two character string "64".

So the entire string being formed is:

"64,34,0,1,1/1"

The way you are launching a macro should work. Here are some experiments to try.

First and foremost, do not use the -invisible option. It is an ugly hack. (I read this somewhere on the developer's mailing list.) If you are opening the document with the hidden property, it should not be necessary to use an ugly hack such as -invisible.

Second, try putting a MsgBox( "|" + cFile + "|" ) at the front of your macro. This stops with a MsgBox showing whatever parameter value is received in cFile, enclosed in two vertical bars, helping to make it obvious if the cFile is preceeded by or followed by extra spaces. It may be necessary to trim extra leading/trailing blanks from cFile before calling ConvertToURL. You will also be able to tell, based on the appearance of the MsgBox, that your macro is actually being called, and what parameter value it is receiving.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
ThomasB
General User
General User


Joined: 19 Mar 2004
Posts: 5

PostPosted: Sat Mar 20, 2004 9:22 pm    Post subject: Reply with quote

Thats a good idea. The thing that gets me is if I change the output filter
the macro works. This made me believe that I had the options for the
CSV filter wrong. However, the code you gave me that creates the
spreadsheet in the macro uses the same output command and that
macro creates a correct CSV file. So now I am at a total loss.
I'll try some debug with the message box command. By the way
do you get the correct output with the DocConverter that you
have posted?

Thank you very much for your help and if you think of anything
let me know.
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Mar 20, 2004 9:35 pm    Post subject: Reply with quote

The DocConverter may list CSV as an export option, but if it does, then it is in error, as I don't provide any FilterOptions string for it.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
ThomasB
General User
General User


Joined: 19 Mar 2004
Posts: 5

PostPosted: Sat Mar 20, 2004 11:05 pm    Post subject: Reply with quote

Well I lost a good bit of hair but I finally figured it out. It appears that the
hidden attribute added to the loading of the file is the problem. When I load
the file without the "MakePropertyValue( "Hidden", True)" option the
CSV file is created correctly. I'm planning on scripting the conversion of
many files (> 200) so the window opening is a nuisance but one that I can
live with. If you have any ideas of how to keep this from happening I would
appreciate it. Once again many thanks as I couldn't have gotten this far
without help.

Thomas
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sun Mar 21, 2004 8:58 am    Post subject: Reply with quote

Maybe it would be better to script the conversion in OOo Basic rather than calling each individual conversion from the command line?
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
ThomasB
General User
General User


Joined: 19 Mar 2004
Posts: 5

PostPosted: Sun Mar 21, 2004 7:31 pm    Post subject: Reply with quote

I am trying to script the entire conversion in OOo Basic as you suggested.
I would like the make my code rather generic and be able to pass the macro
more than one variable. I created the Sub so that it will accept more than
one variable but I can't seem to get the commandline right. Do you know
the commandline to pass multiple variables. I have tried the following:

soffice "macro:///MyLibrary.Conversion.ConvertDir(/home/thomas/temp sxc)"
soffice "macro:///MyLibrary.Conversion.ConvertDir(/home/thomas/temp) (sxc)"
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Mon Mar 22, 2004 8:33 am    Post subject: Reply with quote

See this....

http://www.oooforum.org/forum/viewtopic.php?p=13057#13057




Here are others for reference.....

Command line arguments
======================
http://www.oooforum.org/forum/viewtopic.php?t=4899
http://fr.openoffice.org/Documentation/How-to/Basic/command%20parameters.htm
http://www.oooforum.org/forum/viewtopic.php?p=11058#11058
http://www.oooforum.org/forum/viewtopic.php?p=10972#10972
http://www.oooforum.org/forum/viewtopic.php?t=3548

See "The Office Headless Mode" at this site...
http://framework.openoffice.org/servlets/ProjectDocumentList

Passing array of arguments to macro
http://www.oooforum.org/forum/viewtopic.php?t=3745

Use .uno:Open to display open dialog
http://www.oooforum.org/forum/viewtopic.php?t=4224

Command-line options of StarOffice
http://www.oooforum.org/forum/viewtopic.php?t=1789

http://framework.openoffice.org/files/documents/25/806/Command_line_arguments_in_OpenOffice.html

Run Impress presentation from command line
http://www.oooforum.org/forum/viewtopic.php?t=1051



Executing Macros via. URL or command line
or via. dispatcher
=========================================

Use a macro:/// url via. the Dispatcher
http://www.oooforum.org/forum/viewtopic.php?t=6079


http://www.oooforum.org/forum/viewtopic.php?t=4071
http://www.oooforum.org/forum/viewtopic.php?p=15589#15589
http://www.oooforum.org/forum/viewtopic.php?p=9861#9861
http://www.oooforum.org/forum/viewtopic.php?p=17377#17377
http://www.oooforum.org/forum/viewtopic.php?t=3196
http://www.oooforum.org/forum/viewtopic.php?t=2619
http://www.oooforum.org/forum/viewtopic.php?p=11794#11794

http://www.oooforum.org/forum/viewtopic.php?p=13053#13053

http://www.oooforum.org/forum/viewtopic.php?t=4285

http://www.oooforum.org/forum/viewtopic.php?t=3772

http://www.oooforum.org/forum/viewtopic.php?t=4163

http://www.oooforum.org/forum/viewtopic.php?t=2688

Calling macro in different document, and pass parameter...
http://www.oooforum.org/forum/viewtopic.php?t=5564
Shorter technique, and also on a saved or Untitled document....
http://www.oooforum.org/forum/viewtopic.php?p=25232#25232
use a dot as document name to refer to current document
http://www.oooforum.org/forum/viewtopic.php?t=6808

Call macro in different document from VB and pass parameter...
http://www.oooforum.org/forum/viewtopic.php?t=6221



Dispatcher
==========
http://www.oooforum.org/forum/viewtopic.php?p=11794#11794
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Guest






PostPosted: Wed Jun 02, 2004 11:12 pm    Post subject: Follow-up Reply with quote

Wanted to say thanks for the postings in here; got me headed in the right track for a similar task. What I couldn't find /anywhere/ on the web was a lookup table for the character encoding "indexes" mentioned in the spreadsheet API documentation. After quite a bit of research... it turns out "0" is NOT the "System default encoding", per se. Actually... 9 is the system default. 0 is specified as "Unknown".
A complete listing of encoding indexes can be found in:

$OPENOFFICE_SRC_HOME/sal/inc/rtl/textinc.h

I'm posting this here in hopes that somebody somewhere who needs to programmatically do something that ought to be simple (like specify that the csv file should be ascii encoded; ascii is index 11, incidentally) won't have to waste time looking for the answer. I've taken the time to put together a table in html for those interested. It is accessible at:
http://equalitylearning.org/~robertz/oo_encoding_indexes.html
Back to top
Guest






PostPosted: Tue Jun 08, 2004 11:57 am    Post subject: Reply with quote

Hi, I'm using the script to convert a Calc file to Csv , the problem is that it only converts the active sheet, is there a way to pass the number of the sheet to convert? in order to get multiple csv file of the same multiple sheet sxc?
Back to top
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Tue Jun 08, 2004 12:17 pm    Post subject: Reply with quote

Quote:
I'm using the script to convert a Calc file to Csv , the problem is that it only converts the active sheet, is there a way to pass the number of the sheet to convert? in order to get multiple csv file of the same multiple sheet sxc?


What about an approach like this....
1. Open spreadsheet
2. Delete all sheets except the one you want to convert
3. Call storeToURL, saving the exported CSV document
4. Close the document without saving
5. Repeat steps 1-4 for each other sheet you want to export.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Guest






PostPosted: Tue Jun 08, 2004 12:29 pm    Post subject: Reply with quote

how can I do that via command line? the idea is not to open OpenOffice....

which are the functions that I need to use in the macro in order to do that?

thanx for you help
Back to top
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Tue Jun 08, 2004 1:49 pm    Post subject: Reply with quote

I might not have been clear.

The idea would be to write a macro in OOo which does the multi-step procedure I outlined.

Then call that macro from the command line, as previously discussed in this thread. The main thing that would change is the macro in OOo. From the command line you would call something like....

soffice macro:///Standard.Module1.Sxc2Csv( file:///C:/Some/File.sxc )

and the macro would produce....

C:\Some\File.01.csv
C:\Some\File.02.csv
....etc....
C:\Some\File.57.csv
for each of the fifty seven sheets in the document.

Unfortunantly, I don't have time to write the macro at the moment.

If you want to take a crack at it, look at the Calc Examples thread in the Code Snippets section. There are also numerous other useful sources of information here on OOoForum.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group