I am looking for a way to quickly convert a directory of .csv files into the .xls or .xlsx format (would be nice if I could do either/or).
Is there an easy way to do this or do I need to install a third party program?
I am looking for a way to quickly convert a directory of .csv files into the .xls or .xlsx format (would be nice if I could do either/or).
Is there an easy way to do this or do I need to install a third party program?
Assuming you like and have Python (for some reason), you could use this script I whipped up:
import os
import glob
import csv
import xlwt # from http://www.python-excel.org/
for csvfile in glob.glob(os.path.join('.', '*.csv')):
wb = xlwt.Workbook()
ws = wb.add_sheet('data')
with open(csvfile, 'rb') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, val in enumerate(row):
ws.write(r, c, val)
wb.save(csvfile + '.xls')
Ran in the directory with all the CSV files, it will convert them all and slap a ".xls" onto the end.
For Excel 2007+ (xlsx files) supporting up to about 1 Mrows:
import os
import glob
import csv
import openpyxl # from https://pythonhosted.org/openpyxl/ or PyPI (e.g. via pip)
for csvfile in glob.glob(os.path.join('.', '*.csv')):
wb = openpyxl.Workbook()
ws = wb.active
with open(csvfile, 'rb') as f:
reader = csv.reader(f)
for r, row in enumerate(reader, start=1):
for c, val in enumerate(row, start=1):
ws.cell(row=r, column=c).value = val
wb.save(csvfile + '.xlsx')
I sympathise with you! I had to do this same conversion. I was trying to manipulate data using openpyxl but for some reason it wasn't doing what I wanted with with csv files so had to convert to xlsx first.
I used Python and Pandas. I am using Python 3.
import pandas as pd
import os
for (dirname, dirs, files) in os.walk('.'): #source directory
for filename in files:
if filename.endswith('.csv'):
df = pd.read_csv(filename)
df.to_excel(filename + '.xlsx')
os.remove(filename) #deletes csv file
Here is a perl script that supposedly does it, but it does seem like a lot of work to do something that is already built into Excel.
There is a lot left unsaid in your question.
Assuming that your CVS files are in a directory structure similar to
c:\randompath\CSV\
a.csv
b.csv
c.csv
:
:
z.csv
and you want to end up with
c:\randompath\XLS\
a.xls
b.xls
c.xls
:
:
z.xls
I can think of three routes to go depending on the ratio of upfront work to clean-up work you are willing to do.
Note that I haven't written any code to do these things (yet) I am just offering ideas for a starting point. Perhaps if you could give more details about what you want you could get more detailed explanations for how to handle approach from me or another forum member.
For Windows? CoolUtils "Total CSV Converter" command line version supports many output formats including JSON, Access, DBF, XML & SQL and is only $40. It can recurse sub-directories, delete original CSV files, combine all files to one document, and more.
http://www.coolutils.com/TotalCSVConverter
CSVConverter.exe <source> <destination> <options>
"Advanced CSV Converter" ($40-200) is a portable EXE that can do it quickly and without Excel having to be installed.
http://www.dbf2002.com/csv-converter/commandline.html
"c:\Program Files\CSV Converter\csvcnv.exe" c:\base\*.csv c:\exports\ /TOXLSX /SRCHDR
SoftInterface's "Convert XLS" can use Excel (but is not required) and is more expensive ($500+), but supports more formats and has more options.
http://www.softinterface.com/Convert-XLS/Convert-XLS.htm
"c:\Program Files (x86)\Softinterface, Inc\Convert XLS\ConvertXLS.exe" /V /S"c:\base\*.csv" /T"c:\exports\*.xlsx" /F6 /C51 /M2
"Gnumeric" is an open-source spreadsheet program that can perform a direct conversion, but it was discontinued for Windows in Aug 2014.
ssconvert file.csv file.xlsx
If you have Python installed, "csv2odf" is a open-source option and uses a templated approach to generate ods, odt, html, xlsx, or docx files.
http://sourceforge.net/projects/csv2odf/
csv2odf data.csv template.odt output.xlsx
#!
line to python3. Add import html
. Use html.escape
instead of cgi.escape
.
With Node 8+ and bash:
npm install -g pguardiario/csv2xlsx
for file in *.csv; do csv2xlsx "$file"; done
The Easy way: open your csv file from Microsoft Excel, convert text to columns (select the cells/text, click Menu - Data - Text to Columns) set your option to convert.