csv_to_xls

Install

pip install csv_to_xls

How to use

When you install csv_to_xls you will get a cli tool named csv2xls:

!csv2xls --help

usage: csv2xls [-h] [--file_glob FILE_GLOB] [--out_file OUT_FILE] [--recursive]
               [--delimiter DELIMITER] [--quotechar QUOTECHAR] [--symlinks]
               [--file_re FILE_RE] [--folder_re FOLDER_RE]
               [--skip_file_glob SKIP_FILE_GLOB] [--skip_file_re SKIP_FILE_RE]
               [--skip_folder_re SKIP_FOLDER_RE]
               path

Convert csv file(s) into an excel file, if multiple csvs put on tabs.

positional arguments:
  path                             path to searching for *.csv files

optional arguments:
  -h, --help                       show this help message and exit
  --file_glob FILE_GLOB            Only include files matching glob (default:
                                   *.csv)
  --out_file OUT_FILE              output excel file (default: output.xlsx)
  --recursive                      search subfolders (default: False)
  --delimiter DELIMITER            delimiter to use (default: ,)
  --quotechar QUOTECHAR            quote character to escape the delimiter
                                   (default: ")
  --symlinks                       follow symlinks? (default: False)
  --file_re FILE_RE                Only include files matching regex
  --folder_re FOLDER_RE            Only enter folders matching regex
  --skip_file_glob SKIP_FILE_GLOB  Skip files matching glob
  --skip_file_re SKIP_FILE_RE      Skip files matching regex
  --skip_folder_re SKIP_FOLDER_RE  Skip folders matching regex,

Example

Consider the below csv files:

!ls _tests/

addresses.csv biostats.csv  faithful.csv  hw_200.csv    tally_cab.csv

To merge these csvs into one excel file named merged.xlsx:

!csv2xls _tests/ --out_file merged.xlsx

We can see there is a worksheet for each filename:

import pandas as pd
pd.ExcelFile('merged.xlsx').sheet_names

['addresses', 'biostats', 'hw_200', 'tally_cab', 'faithful']

Let’s say we want to skip the biostats.csv file, we can use the --skip_file_re arg:

!rm -f merged.xlsx
!csv2xls _tests/ --out_file merged.xlsx --skip_file_re 'biostats*'

Now that particular sheet is not there:

pd.ExcelFile('merged.xlsx').sheet_names

['addresses', 'hw_200', 'tally_cab', 'faithful']