IN2CSV(1)

📦
version 2.2.0 · 7 versions
, in arch rolling, nixos 26.05, nixpkgs unstable, ubuntu resolute

🎀 in2csv — the csvkit girlie who fixes everybody's data 💅

okay so bestie, welcome to in2csv (that's the IN2CSV(1) page from the csvkit manual, section 1, if you're being formal about it). She's that friend who takes your chaotic little file and makes it a clean CSV. Obsessed, honestly.

✨ NAME ✨

in2csv — and lowkey the whole name of this doc is just in2csv Documentation. That's it. That's the vibe. 🦋

🌸 DESCRIPTION 🌸

She converts various tabular data formats into CSV. That's the whole job. No notes.

But heads up, bestie: converting fixed width data means you have to hand her a schema file with the -s option. No schema, no slay. The schema file should look like this:

column,start,length
name,0,30
birthday,30,10
age,40,3

The header line is required — mandatory, non-negotiable, periodt — though the columns can be in any order you want. Your order, your rules. 👑

🫶 the usage (verbatim, she's iconic like this) 🫶

usage: in2csv [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
              [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-L LOCALE]
              [-S] [--blanks] [--null-value NULL_VALUES [NULL_VALUES ...]]
              [--date-format DATE_FORMAT] [--datetime-format DATETIME_FORMAT]
              [-H] [-K SKIP_LINES] [-v] [-l] [--zero] [-V]
              [-f {csv,dbf,fixed,geojson,json,ndjson,xls,xlsx}] [-s SCHEMA]
              [-k KEY] [-n] [--sheet SHEET] [--write-sheets WRITE_SHEETS]
              [--use-sheet-names] [--reset-dimensions]
              [--encoding-xls ENCODING_XLS] [-y SNIFF_LIMIT] [-I]
              [FILE]

Convert common, but less awesome, tabular data formats to CSV.

positional arguments:
  FILE                  The CSV file to operate on. If omitted, will accept
                        input as piped data via STDIN.

optional arguments:
  -h, --help            show this help message and exit
  -f {csv,dbf,fixed,geojson,json,ndjson,xls,xlsx}, --format {csv,dbf,fixed,geojson,json,ndjson,xls,xlsx}
                        The format of the input file. If not specified will be
                        inferred from the file type.
  -s SCHEMA, --schema SCHEMA
                        Specify a CSV-formatted schema file for converting
                        fixed-width files. See web documentation.
  -k KEY, --key KEY     Specify a top-level key to look within for a list of
                        objects to be converted when processing JSON.
  -n, --names           Display sheet names from the input Excel file.
  --sheet SHEET         The name of the Excel sheet to operate on.
  --write-sheets WRITE_SHEETS
                        The names of the Excel sheets to write to files, or
                        "-" to write all sheets.
  --use-sheet-names     Use the sheet names as file names when --write-sheets
                        is set.
  --reset-dimensions    Ignore the sheet dimensions provided by the XLSX file.
  --encoding-xls ENCODING_XLS
                        Specify the encoding of the input XLS file.
  -y SNIFF_LIMIT, --snifflimit SNIFF_LIMIT
                        Limit CSV dialect sniffing to the specified number of
                        bytes. Specify "0" to disable sniffing entirely, or
                        "-1" to sniff the entire file.
  -I, --no-inference    Disable type inference (and --locale, --date-format,
                        --datetime-format, --no-leading-zeroes) when parsing
                        CSV input.

Some command-line flags only pertain to specific input formats.

See also: Arguments common to all tools — the shared-flag lore lives there, iykyk. 📚

💜 let's go through every single flag, one by one 💜

  • -h, --help — shows this help message and then dips. exit energy. 💀
  • -f {csv,dbf,fixed,geojson,json,ndjson,xls,xlsx}, --format {csv,dbf,fixed,geojson,json,ndjson,xls,xlsx} — tells her what the input file's format actually is. If you don't specify it, she'll figure it out from the file type herself. main character detective behavior 🔍
  • -s SCHEMA, --schema SCHEMA — give her a CSV-formatted schema file so she can convert fixed-width files. Check the web documentation for the full tea. 📄
  • -k KEY, --key KEY — for JSON: name a top-level key to look inside, where a list of objects is hanging out waiting to be converted. 🗝️
  • -n, --names — displays the sheet names from your input Excel file. Just shows them. Very "look at me" of it. ✨
  • --sheet SHEET — the name of the Excel sheet you want her to operate on. One sheet, chosen, blessed. 🎯
  • --write-sheets WRITE_SHEETS — the names of the Excel sheets to write out to files, or "-" to write all the sheets. All of them. Every single one. 🫠
  • --use-sheet-names — when --write-sheets is set, use the sheet names as the file names. Naming things after themselves, iconic. 🏷️
  • --reset-dimensions — ignores the sheet dimensions that the XLSX file claims. More on why you'd want this in troubleshooting below. 👀
  • --encoding-xls ENCODING_XLS — specify the encoding of the input XLS file. Because sometimes XLS is just like that. 😭
  • -y SNIFF_LIMIT, --snifflimit SNIFF_LIMIT — limits CSV dialect sniffing to a set number of bytes. Specify "0" to disable sniffing entirely, or "-1" to sniff the entire file. Go big or go home. 🐽
  • -I, --no-inference — disables type inference when parsing CSV input (and also --locale, --date-format, --datetime-format, --no-leading-zeroes). She takes everything at face value. No assumptions. 💅

And fr fr, some command-line flags only pertain to specific input formats — so don't come for her when --sheet doesn't do anything on a .dbf. 🙈

🧚 the three NOTE moments 🧚

NOTE: The "ndjson" format means "newline delimited JSON", the kind lots of streaming APIs use. 🌊
NOTE: If an XLS looks identical to an XLSX when you open it in Excel, they may not be identical as CSV. For example, XLSX has an integer type, but XLS doesn't. So numbers that look like integers coming from an XLS will have decimals in CSV, while the same-looking numbers from an XLSX won't. Same face, different data. 💀
NOTE: To convert from HTML, consider messytables. Different tool, different era. 🌈

🔥 EXAMPLES 🔥 (the good part)

Convert the 2000 census geo headers file from fixed-width to CSV, and from latin-1 encoding to utf8:

in2csv -e iso-8859-1 -f fixed -s examples/realdata/census_2000/census2000_geo_schema.csv examples/realdata/census_2000/usgeo_excerpt.upl
NOTE: A library of fixed-width schemas is maintained in the ffs project: https://github.com/wireservice/ffs

Convert an Excel .xls file:

in2csv examples/test.xls

Standardize the formatting of a CSV file (quoting, line endings, etc.):

in2csv examples/realdata/FY09_EDU_Recipients_by_State.csv

Fetch csvkit's open issues from the GitHub API, convert the JSON response into a CSV and write it to a file:

curl https://api.github.com/repos/wireservice/csvkit/issues?state=open | in2csv -f json -v

Convert a DBase DBF file to an equivalent CSV:

in2csv examples/testdbf.dbf

This tool names unnamed headers. To avoid that behavior, run:

in2csv --no-header-row examples/test.xlsx | tail -n +2

😭 TROUBLESHOOTING 😭 (we've all been here)

If an error like this one shows up when you hand her an input file in CSV or Excel format:

ValueError: Row 0 has 11 values, but Table only has 1 columns.

...then your input file probably has some initial rows sitting before the header and the data rows. Rude of it, honestly. You can skip those rows with --skip-lines (-K):

in2csv --skip-lines 3 examples/test_skip_lines.csv

And if an XLSX file gives you too few rows or too few columns? That's likely because the application that created the file set the worksheet's dimensions wrong. Try again with the --reset-dimensions option. She'll just ignore what the file claims and do her own thing. 💅✨

👑 AUTHOR 👑

Christopher Groskopf and contributors. Shoutout to the whole squad. 🫶

2016, Christopher Groskopf and James McKinney.

and that's the whole page bestie!! 🎀 this is csvkit 2.2.0, dated Aug 16, 2024, and she ate, no crumbs. go convert something. ⭐💜🦋

📦 where this page comes from