Batch conversion of .csv to .xls
Batch conversion of .csv to .xls
I'm looking for a quick method for converting about 12 .csv files to .xls. I do this monthly, currently opening each file in LibreOffice and saving as .xls. It's a boring, repetitive process. I need a local solution - the online converters I've found so far don't make the task any quicker.
Any ideas?
Any ideas?
Gigabyte B550I Aorus Pro AX, Ryzen 5 5600G, 16GB, 250GB Samsung SSD (GPT), 2x1TB HDD (MBR), MX-21-AHS
Lenovo Thinkpad X220, dual-core i5, 4MB, 120GB Samsung SSD (GPT), MX-21
Re: Batch conversion of .csv to .xls
This looks promising:
https://sourceforge.net/projects/csv2odf/
If it's a simple data file, I usually just open a terminal in the folder the files are in and do this:Seems to work fine.
https://sourceforge.net/projects/csv2odf/
If it's a simple data file, I usually just open a terminal in the folder the files are in and do this:
Code: Select all
mv *.csv *.xls
Production: 5.10, MX-23 Xfce, AMD FX-4130 Quad-Core, GeForce GT 630/PCIe/SSE2, 16 GB, SSD 120 GB, Data 1TB
Personal: Lenovo X1 Carbon with MX-23 Fluxbox and Windows 10
Other: Raspberry Pi 5 with MX-23 Xfce Raspberry Pi Respin
Personal: Lenovo X1 Carbon with MX-23 Fluxbox and Windows 10
Other: Raspberry Pi 5 with MX-23 Xfce Raspberry Pi Respin
Re: Batch conversion of .csv to .xls
Thanks, Jerry. csv2odf certainly looks a good and simple possibility. The description doesn't include .xls as an output format, only .xlsx, which Filemaker rejects. However, the examples in the documentation shows an output template for .xls, so it may be OK. It does mean that I have to create templates for the 3 different tables involved. I don't have time to do that this month but I'll have a go before next month.
The simple mv command doesn't work. Filemaker doesn't recognise the separate fields in the .xls file so created.
Phil
The simple mv command doesn't work. Filemaker doesn't recognise the separate fields in the .xls file so created.
Phil
Gigabyte B550I Aorus Pro AX, Ryzen 5 5600G, 16GB, 250GB Samsung SSD (GPT), 2x1TB HDD (MBR), MX-21-AHS
Lenovo Thinkpad X220, dual-core i5, 4MB, 120GB Samsung SSD (GPT), MX-21
Re: Batch conversion of .csv to .xls
This sounds like a fairly easy Excel macro using VBA (Visual Basic for Applications that comes with Excel).
Libreoffice also has a basic macro language called Libreoffice Basic.
Do you have Excel available to use for this or do you need a linux-only solution?
I did a lot of work using Excel VBA as a front-end to our budgeting system using IBM Cognos TM1 and it probably wouldn't take me long to create one for Excel.
Libreoffice also has a basic macro language called Libreoffice Basic.
Do you have Excel available to use for this or do you need a linux-only solution?
I did a lot of work using Excel VBA as a front-end to our budgeting system using IBM Cognos TM1 and it probably wouldn't take me long to create one for Excel.
Zotac ZBOX-ID91-U (desktop system)
SanDisk Extreme Pro 240GB SATA III SSD
G.SKILL 8GB DDR3 1600 (PC3 12800)
ZOTAC ZBOX nano ID62-U (media streamer)
Silicon Power Slim S60 120GB SATA III SSD
G.SKILL Ripjaws 4GB DDR3L 1600 (PC3L 12800)
SanDisk Extreme Pro 240GB SATA III SSD
G.SKILL 8GB DDR3 1600 (PC3 12800)
ZOTAC ZBOX nano ID62-U (media streamer)
Silicon Power Slim S60 120GB SATA III SSD
G.SKILL Ripjaws 4GB DDR3L 1600 (PC3L 12800)
Re: Batch conversion of .csv to .xls
I should have mentioned that a web search on the subject of this thread shows lots of good solutions.
Production: 5.10, MX-23 Xfce, AMD FX-4130 Quad-Core, GeForce GT 630/PCIe/SSE2, 16 GB, SSD 120 GB, Data 1TB
Personal: Lenovo X1 Carbon with MX-23 Fluxbox and Windows 10
Other: Raspberry Pi 5 with MX-23 Xfce Raspberry Pi Respin
Personal: Lenovo X1 Carbon with MX-23 Fluxbox and Windows 10
Other: Raspberry Pi 5 with MX-23 Xfce Raspberry Pi Respin
Re: Batch conversion of .csv to .xls
Sample code here from a google search:
Is it possible to batch convert csv to xls using a macro?
Haven't tried it but it looks like it should work. It is a windows/excel solution. You would just need to replace the file folder to work with.
It would be interesting to learn how to use Libreoffice Basic to do this.
Is it possible to batch convert csv to xls using a macro?
Code: Select all
Sub CSV_to_XLSX()
Dim wb As Workbook
Dim strFile As String, strDir As String
strDir = "C:\Users\acer\OneDrive\Doctorado\Study 1\data\Retest Bkp\Day 1\Sart\"
strFile = Dir(strDir & "*.csv")
Do While strFile <> ""
Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
With wb
.SaveAs Replace(wb.FullName, ".csv", ".xlsx"), 51
.Close True
End With
Set wb = Nothing
strFile = Dir
Loop
End Sub
It would be interesting to learn how to use Libreoffice Basic to do this.
Zotac ZBOX-ID91-U (desktop system)
SanDisk Extreme Pro 240GB SATA III SSD
G.SKILL 8GB DDR3 1600 (PC3 12800)
ZOTAC ZBOX nano ID62-U (media streamer)
Silicon Power Slim S60 120GB SATA III SSD
G.SKILL Ripjaws 4GB DDR3L 1600 (PC3L 12800)
SanDisk Extreme Pro 240GB SATA III SSD
G.SKILL 8GB DDR3 1600 (PC3 12800)
ZOTAC ZBOX nano ID62-U (media streamer)
Silicon Power Slim S60 120GB SATA III SSD
G.SKILL Ripjaws 4GB DDR3L 1600 (PC3L 12800)
Re: Batch conversion of .csv to .xls
Thanks. I don't have Excel (and have never used any Microsoft apps), but I would think the same thing is possible with a LO macro. That's probably a big learning curve for me - my experience with scripting is Lotus 123 (DOS), Lotus Approach and Filemaker. All slightly different and full of traps when switching from one to the other.
As far as I've found already, I need to go through LO and produce .xls files. Filemaker rejects .xlsx.
I'm away for the next week with no internet (although rumour has it that a new mobile mast is about to be commissioned, so that may change).
As far as I've found already, I need to go through LO and produce .xls files. Filemaker rejects .xlsx.
I'm away for the next week with no internet (although rumour has it that a new mobile mast is about to be commissioned, so that may change).
Gigabyte B550I Aorus Pro AX, Ryzen 5 5600G, 16GB, 250GB Samsung SSD (GPT), 2x1TB HDD (MBR), MX-21-AHS
Lenovo Thinkpad X220, dual-core i5, 4MB, 120GB Samsung SSD (GPT), MX-21
Re: Batch conversion of .csv to .xls
Yes, I would do this with gnumeric's ssconvert.Gaer Boy wrote: ↑Sun Aug 05, 2018 5:24 am I'm looking for a quick method for converting about 12 .csv files to .xls. I do this monthly, currently opening each file in LibreOffice and saving as .xls. It's a boring, repetitive process. I need a local solution - the online converters I've found so far don't make the task any quicker.
Any ideas?
Like this:
install gnumeric
Code: Select all
apt-get install gnumeric
Run this command
Code: Select all
for CSV in $(ls *.csv); do ssconvert $CSV ${CSV%.csv}.xls; done
Code: Select all
ssconvert mycsvdata.csv mycsvdata.xls
ssconvert SPR1.csv SPR1.xls
Code: Select all
$ cat mycsvdata.csv
"Col_A","Col_B","Col_C","Col_D","Cold_E"
"A_1","B_1","C_1","Text 1",1
"A_2","B_2","C_2","Text 2",2
"A_3","B_3","C_3","Text 3",3
"A_4","B_4","C_4","Text 4",4
"A_5","B_5","C_5","Text 5",5
"A_6","B_6","C_6","Text 6",6
"A_7","B_7","C_7","Text 7",7
"A_8","B_8","C_8","Text 8",8
"A_9","B_9","C_9","Text 9",9
"A_10","B_10","C_10","Text 10",10
"A_11","B_11","C_11","Text 11",11
"A_12","B_12","C_12","Text 12",12
with the table-name as the name of csv-file and shown as such:
You do not have the required permissions to view the files attached to this post.
Gigabyte Z77M-D3H, Intel Xeon E3-1240 V2 (Quad core), 32GB RAM,
GeForce GTX 770, Samsung SSD 850 EVO 500GB, Seagate Barracuda 4TB
GeForce GTX 770, Samsung SSD 850 EVO 500GB, Seagate Barracuda 4TB
-
- Posts: 136
- Joined: Sat May 02, 2015 4:35 pm
Re: Batch conversion of .csv to .xls
+1
I can read and write .xls and .xlsx files in java. But it would be instructive to better learn to use Libreoffice in a new way.
<OP>
A solution you may not have considered: You can use CloudConvert to convert a ton of file formats.
There are many cloud converters. I believe these are the spreadsheet formats you're looking for.
https://cloudconvert.com/formats#spreadsheet
You could write a simple script to upload the .csv files and download them as .xls.
Just an alternate idea.
Son, someday all this will belong to your ex wife.
Re: Batch conversion of .csv to .xls
Alternativly if you do not have/want gnumeric to be installed.
You can do this with some perl lib's like:
Copy/Symlink csv2xls.pl into your path or into the directory of you csv-file:
and run as the example above to convert like this
This method actually allows you to create one spreadsheet with holding multiple csv-tables.
EDIT ... with some further tweaking to create one spreadsheet out of multiple csv's.
You can do this with some perl lib's like:
Code: Select all
apt-get install libtext-csv-perl libtext-csv-xs-perl libspreadsheet-writeexcel-perl
Code: Select all
cp /usr/share/doc/libspreadsheet-writeexcel-perl/examples/csv2xls.pl .
# or symlink
ln -s /usr/share/doc/libspreadsheet-writeexcel-perl/examples/csv2xls.pl .
Code: Select all
for CSV in $(ls *.csv); do perl csv2xls.pl $CSV ${CSV%.csv}.xls; done
EDIT ... with some further tweaking to create one spreadsheet out of multiple csv's.
Gigabyte Z77M-D3H, Intel Xeon E3-1240 V2 (Quad core), 32GB RAM,
GeForce GTX 770, Samsung SSD 850 EVO 500GB, Seagate Barracuda 4TB
GeForce GTX 770, Samsung SSD 850 EVO 500GB, Seagate Barracuda 4TB