Skipping CSV rows with odd numbers of quotes using ripgrep

I'm working with several huge CSV files - over 5 million rows total - and I ran into a problem: it turned out there were a few lines in those files that imported incorrectly because they were not correctly escaped.

Here's an example of an invalid line:

SAI Exempt,"Patty B"s Hats & Tees,LLC",,26 Broad St

The apostrophe in Patty B's Hats & Tees is incorrectly represented here as a double quote, and since that's in a double quoted string it breaks that line of CSV.

I decided to filter out any rows that had an odd number of quotation marks in them - saving those broken lines to try and clean up later.

Finding rows with odd numbers of quotes

StackOverflow offered this regular expression for finding lines with an odd number of quotation marks:

[^"]*"   # Match any number of non-quote characters, then a quote
(?:      # Now match an even number of quotes by matching:
 [^"]*"  #  any number of non-quote characters, then a quote
 [^"]*"  #  twice
)*       # and repeat any number of times.
[^"]*    # Finally, match any remaining non-quote characters

I translated this into a ripgrep expression, adding ^ to the beginning and $ to the end in order to match whole strings.

This command counted the number of invalid lines:

rg '^[^"]*"(?:[^"]*"[^"]*")*[^"]*$' --glob '*.csv' --count

04.csv:52
03.csv:42
02.csv:24
01.csv:29

Adding --invert-match showed me the count of lines that did NOT have an odd number of quotes:

rg '^[^"]*"(?:[^"]*"[^"]*")*[^"]*$' --glob '*.csv' --count --invert-match

05.csv:2829
04.csv:812351
03.csv:961311
02.csv:994265
01.csv:995404

This shows that the invalid lines are a tiny subset of the overall files.

Removing --count shows the actual content.

Importing into SQLite with sqlite-utils

I used this for loop to import only the valid lines into a SQLite database:

for file in *.csv;
    do rg --invert-match '^[^"]*"(?:[^"]*"[^"]*")*[^"]*$' $file | \
    sqlite-utils insert my.db rows - --csv;
done;

Saving the broken lines for later

To save the lines that contained odd numbers of double quotes I used this command:

rg '^[^"]*"(?:[^"]*"[^"]*")*[^"]*$' \
  --glob '*.csv' \
  --no-line-number \
  --no-filename > saved.txt

Since I don't actually care which file they lived in - all of these CSV files share the same structure - I used --no-filename to omit the filename from the results and --no-line-number to omit the line number. The result is a saved.txt file containing just the raw CSV data that I skipped from the import.

Related

• bash Finding CSV files that start with a BOM using ripgrep - 2021-05-28
• bash Escaping a SQL query to use with curl and Datasette - 2020-12-08
• sqlite One-liner for running queries against CSV files with SQLite - 2022-06-20
• bash Looping over comma-separated values in Bash - 2020-09-01
• zsh Passing command arguments using heredoc syntax - 2022-07-07
• sqlite Importing CSV data into SQLite with .import - 2021-07-13
• llms Piping from rg to llm to answer questions about code - 2024-02-11
• sqlite Fixing broken text encodings with sqlite-transform and ftfy - 2021-01-18
• bash Ignoring errors in a section of a Bash script - 2022-06-27
• bash Using awk to add a prefix - 2022-04-08

Created 2020-12-11T19:50:58-08:00, updated 2021-01-18T17:27:54-08:00 · History · Edit