| View previous topic :: View next topic |
| Author |
Message |
gizmo
OOo Advocate
Joined: 13 Oct 2004
Posts: 249
Location: Everywhere
|
 Posted: Sat Mar 31, 2007 9:56 am Post subject: Convert CSV to ODS? |
 |
|
Is there an easy way to convert CSV files to ODS?
Thanks! |
|
| Back to top |
|
 |
squenson
Super User
Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia
|
| Posted: Sat Mar 31, 2007 10:14 am Post subject: |
|
|
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 |
|
|
Mark B
Super User
Joined: 16 Feb 2007
Posts: 853
Location: Lincolnshire, UK
|
| Posted: Sat Mar 31, 2007 12:19 pm Post subject: |
|
|
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 |
|
|
yinhao
Newbie
Joined: 19 Apr 2007
Posts: 2
|
| Posted: Thu Apr 19, 2007 1:48 am Post subject: |
|
|
| 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 |
|
|
Mark B
Super User
Joined: 16 Feb 2007
Posts: 853
Location: Lincolnshire, UK
|
| Posted: Thu Apr 19, 2007 2:16 am Post subject: |
|
|
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 |
|
|
yinhao
Newbie
Joined: 19 Apr 2007
Posts: 2
|
| Posted: Thu Apr 19, 2007 7:10 pm Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
| Posted: Fri Apr 20, 2007 10:21 am Post subject: |
|
|
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
|
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06 |
|
| Back to top |
|
|
soliplaya
General User
Joined: 22 Apr 2007
Posts: 11
Location: Valencia, Spain
|
| Posted: Mon Apr 23, 2007 11:56 am Post subject: converting documents |
|
|
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 |
|
|
|
