csv-split-easy3.1.0

Splits the CSV string into array of arrays, each representing a row of columns

§ Quick Take

import { strict as assert } from "assert";
import splitEasy from "csv-split-easy";

assert.deepEqual(
  splitEasy(
    'Product Name,Main Price,Discounted Price\nTestarossa (Type F110),"100,000","90,000"\nF50,"2,500,000","1,800,000"'
  ),
  [
    ["Product Name", "Main Price", "Discounted Price"],
    ["Testarossa (Type F110)", "100000", "90000"],
    ["F50", "2500000", "1800000"],
  ]
);

§ Purpose

Split a string representing a CSV file into an array of arrays, so that we can traverse it later.

Acceptance Criteria:

  • It should accept CSV's with or without a header row
  • Header row might have different amount of columns than the rest of the rows
  • Content (not header) rows might be offset and have different amount of columns from the rest
  • There can be various line break types (\n, \r, \n\r or \n\n\n\n\n\n\r\r\r\r\r\n\n\n\n or whatever)
  • It should ignore any values wrapped with double quotes
  • It should interpret commas as part of the value if it is wrapped in double quotes
  • It should accept empty fields and output them as empty strings
  • It should automatically detect (dot/comma) and remove thousand separators from digit-only cells
  • Minimal dependencies and 100% unit test code coverage in all ways: per-branch, per-statement, per-function and per-line.

Outside of the scope:

  • Trimming the values of leading and trailing empty space. Just use String.prototype.trim()
  • Parsing numeric values. Parse them yourself. It's outside of the scope of this lib.
  • Sorting rows of double-entry, accounting CSV's. See csv-sort.

§ API - Input

splitEasy(str, [opts])

In other words, it's a function which takes two input arguments, second one is optional (marked by brackets).

The API is string-in, an array of arrays-out.

Empty values, same as numbers too, are set as empty strings.

§ API - Options

options object's keyTypeObligatory?DefaultDescription
removeThousandSeparatorsFromNumbersBooleannotrueShould remove thousand separators? 1,000,0001000000? Or Swiss-style, 1'000'0001000000? Or Russian-style, 1 000 0001000000?
padSingleDecimalPlaceNumbersBooleannotrueShould we pad one decimal place numbers with zero? 100.2100.20?
forceUKStyleBooleannofalseShould we convert the decimal separator commas into dots? 1,51.5?

Here is it in one place:

{
removeThousandSeparatorsFromNumbers: true,
padSingleDecimalPlaceNumbers: true,
forceUKStyle: false,
}

§ API - Output

Returns an array of arrays (each depicting a row). When there's nothing given, returns [['']]

There's always one array within the main array and there's always something there, at least an empty string.

§ The algorithm

CSV files, especially accounting-ones, are different from just any files. We assume that we don't want any empty rows in the parsed arrays. It means, conventional opens in a new tab string splitting libraries would be inefficient here because after splitting, we'd have to clean up any empty rows.

The second requirement is that any of the column values in CSV can be wrapped with double quotes. We have to support mixed, wrapped and not wrapped-value CSV's because Metro bank used to produce these when we banked with them back in 2015.

The third requirement is that any of the values can be wrapped with double quotes and have commas within as values.

The requirements mentioned above pretty much rule out the conventional regex-based split algorithms. You can opens in a new tab just split by /\r?\n/ but later you'll need to clean up possible empty rows. You can't string.split each row by comma because that comma might be a value, you need to check for wrapping double quotes first!

So, the best algorithm is a single for-loop traversal on the input string, detecting and array.pushing the values one by one. It worked very well on email-comb where we remove unused CSS from an HTML template within around 2.5 times more characters "travelled" than there are in the file. Traversing as a string also worked well on html-img-alt which needs only a single traversal through the string to fix all the img tag alt attributes and clean all the crap in/around them.

§ Licence

MIT opens in a new tab

Copyright © 2010–2020 Roy Revelt and other contributors

Related packages:

📦 csv-sort 3.1.0
Sorts double-entry bookkeeping CSV coming from internet banking
📦 csv-sort-cli 1.9.84
Command line app to sort double-entry CSVs coming from internet banking statements