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 CSV to ODS?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
gizmo
OOo Advocate
OOo Advocate


Joined: 13 Oct 2004
Posts: 248
Location: Everywhere

PostPosted: Sat Mar 31, 2007 9:56 am    Post subject: Convert CSV to ODS? Reply with quote

Is there an easy way to convert CSV files to ODS?
Deleted spam links - floris v, moderator


Last edited by gizmo on Tue Jan 31, 2012 8:25 am; edited 78 times in total
Back to top
View user's profile Send private message Visit poster's website
squenson
Super User
Super User


Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

PostPosted: Sat Mar 31, 2007 10:14 am    Post subject: Reply with quote

gizmo,

It depends what you mean by "easily".

If you have a few files, you can do it manually: open the file directly in Calc, review if the csv parameters are correct, then save it as OpenDocument SpreadSheet.

If you have a large number of files, if they have different separators, you will need to write a program for that.
_________________
Help us to help you: Add [Solved] to the title of the thread if you agree with the answer
>>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
Back to top
View user's profile Send private message
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Sat Mar 31, 2007 12:19 pm    Post subject: Reply with quote

Hi

Here's some code that will get you up and running:
Code:

Sub Main
batch_csv_to_ods("/tmp/file1.csv,/tmp/file2.csv,/tmp/file3.csv")
End Sub

Sub batch_csv_to_ods (input_files as string)
Dim file_list
file_list = split(input_files,",")
for i = 0 to ubound(file_list)
 single_csv_to_ods(file_list(i))
next i
End Sub

Sub single_csv_to_ods (csv_file as string)
dim oUrl as String, oNewUrl as String
Dim oDoc, oUrlConvert
Dim oPropertyValue(0) as New com.sun.star.beans.PropertyValue

'Open the CSV File
oUrl=convertToUrl(csv_file)
oPropertyValue(0).Name = "FilterOptions"
oPropertyValue(0).Value = "44"
oDoc = starDeskTop.loadComponentFromURL (oUrl, "_blank", 0, oPropertyValue())

'Convert file name
oUrlConvert = split (oUrl, ".")
oNewUrl = oUrlConvert(0) & ".ods"

'Save as ODS
oDoc.storeAsUrl (oNewUrl, Array())
oDoc.close(true)
End Sub


Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
yinhao
Newbie
Newbie


Joined: 19 Apr 2007
Posts: 2

PostPosted: Thu Apr 19, 2007 1:48 am    Post subject: Reply with quote

you have hardcoded the list of csv file in the program itself. How to get the file list from a text file which contain the csv file name (one filename per line)?
Back to top
View user's profile Send private message
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Thu Apr 19, 2007 2:16 am    Post subject: Reply with quote

Let's say that you've got a file /tmp/filelist.txt, and this contains:
/tmp/file1.csv
/tmp/file2.csv
/tmp/file3.csv

You can process this by adding a subroutine and modifying Main:
Code:

Sub Main
csv_to_ods_from_file("/tmp/filelist.txt")
End Sub

Sub csv_to_ods_from_file (listFile as String)
fNumber = Freefile()
Open listFile  For Input As fNumber
r = 0
While not eof(#fNumber)
 Line Input #fNumber, iLine
 single_csv_to_ods (iLIne)
wend
Close #fNumber
End Sub


Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
yinhao
Newbie
Newbie


Joined: 19 Apr 2007
Posts: 2

PostPosted: Thu Apr 19, 2007 7:10 pm    Post subject: Reply with quote

Great! it's working, I'm still learning how to write macro for OOo.
What if I would like to put all the files from csv into a single ods file (existing ods file) and each of the csv file I imported is placed as a new sheet and use the file name (without .csv) as the sheet name. (override the sheet if the sheet exists with the same name).

example, I have data1.csv and data2.csv and all_data.ods.
all_data.ods has already contain 2 sheet, named "summary", "data1". When running the script it will take the contain of data1.csv and replace whatever contain in data1 sheet. and create data2 sheet and place the contain of data2.csv into the data2 sheet.

Actually, I'm trying out the link to external file but it doesn;t allow me to do so on OOo 2.1. Not sure why the okay button is grey out and not allow me to click after select the file.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Apr 20, 2007 10:21 am    Post subject: Reply with quote

This should do half of the job. Sorry, it's in the wrong forum.
http://www.oooforum.org/forum/viewtopic.phtml?t=54584&start=15
It copies only the values of the csv into another document. Since a csv has nothing but values this should be OK in any case.
ShowFilterOptions is meant as a helper to show the complex filter-options of a successfully imported csv.
Another snippet:
Code:

oSheets = myDoc.Sheets
iPosition = oSheets.getCount()
If not oSheets.hasByName(someString) then
 oSheets.insertNewByName(someString, iPosition)
end if

_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
soliplaya
General User
General User


Joined: 22 Apr 2007
Posts: 11
Location: Valencia, Spain

PostPosted: Mon Apr 23, 2007 11:56 am    Post subject: converting documents Reply with quote

I also recommend that you have a look at JODConverter (search Google). The python script that is offered is easy to call from the command-line or to integrate in a script.
It will also convert Excel files directly, not only CSV files.
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
Page 1 of 1

 
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