Batch conversion of .csv to .xls

Post Reply
Message
Author
User avatar
Gaer Boy
Posts: 859
Joined: Sat Jun 06, 2009 6:06 am

Batch conversion of .csv to .xls

#1 Post by Gaer Boy »

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?

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

User avatar
Jerry3904
Administrator
Posts: 21881
Joined: Wed Jul 19, 2006 6:13 am

Re: Batch conversion of .csv to .xls

#2 Post by Jerry3904 »

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:

Code: Select all

mv *.csv *.xls
Seems to work fine.
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

User avatar
Gaer Boy
Posts: 859
Joined: Sat Jun 06, 2009 6:06 am

Re: Batch conversion of .csv to .xls

#3 Post by Gaer Boy »

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

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

User avatar
topcat
Posts: 61
Joined: Wed Jul 12, 2006 7:55 am

Re: Batch conversion of .csv to .xls

#4 Post by topcat »

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.
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)

User avatar
Jerry3904
Administrator
Posts: 21881
Joined: Wed Jul 19, 2006 6:13 am

Re: Batch conversion of .csv to .xls

#5 Post by Jerry3904 »

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

User avatar
topcat
Posts: 61
Joined: Wed Jul 12, 2006 7:55 am

Re: Batch conversion of .csv to .xls

#6 Post by topcat »

Sample code here from a google search:

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
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.
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)

User avatar
Gaer Boy
Posts: 859
Joined: Sat Jun 06, 2009 6:06 am

Re: Batch conversion of .csv to .xls

#7 Post by Gaer Boy »

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).

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

User avatar
fehlix
Developer
Posts: 10311
Joined: Wed Apr 11, 2018 5:09 pm

Re: Batch conversion of .csv to .xls

#8 Post by fehlix »

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?
Yes, I would do this with gnumeric's ssconvert.
Like this:
install gnumeric

Code: Select all

apt-get install gnumeric
Open terminal within the folder of your csv-files.
Run this command

Code: Select all

for CSV in $(ls *.csv); do ssconvert $CSV ${CSV%.csv}.xls; done 
which in my test-dir with 2 csv-file runs this command:

Code: Select all

ssconvert mycsvdata.csv mycsvdata.xls
ssconvert SPR1.csv SPR1.xls
MY example csv looks like:

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
which converts to xls: here loaded into LO_Calc (without csv-conversion)
with the table-name as the name of csv-file and shown as such:
mycsvdata-xls.png
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

clicktician
Posts: 136
Joined: Sat May 02, 2015 4:35 pm

Re: Batch conversion of .csv to .xls

#9 Post by clicktician »

topcat wrote: Sun Aug 05, 2018 3:21 pm It would be interesting to learn how to use Libreoffice Basic to do this.
+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.

User avatar
fehlix
Developer
Posts: 10311
Joined: Wed Apr 11, 2018 5:09 pm

Re: Batch conversion of .csv to .xls

#10 Post by fehlix »

Alternativly if you do not have/want gnumeric to be installed.
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
Copy/Symlink csv2xls.pl into your path or into the directory of you csv-file:

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 .
and run as the example above to convert like this

Code: Select all

for CSV in $(ls *.csv); do perl csv2xls.pl $CSV ${CSV%.csv}.xls; done 
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.
Gigabyte Z77M-D3H, Intel Xeon E3-1240 V2 (Quad core), 32GB RAM,
GeForce GTX 770, Samsung SSD 850 EVO 500GB, Seagate Barracuda 4TB

Post Reply

Return to “General”