Is there a way to separate text by commas without including any commas that are within quotation marks?

Question

Is there a way to separate text by commas without including any commas that are within quotation marks?

My challenge involves a Typescript file that is responsible for splitting a CSV file using the code below:

var cells = rows[i].split(",");

However, I am now faced with the task of modifying this code so that any commas inside quotes do not trigger a split. For instance, The,"quick, brown fox", jumped should be split into The, quick, brown fox, and jumped instead of inadvertently splitting quick and brown fox. How can this issue be addressed effectively?

regex typescript csv

Answer 1

Answer №1

Update:

I have come up with what I believe to be the optimal final version in a single line:

var cells = (rows[i] + ',').split(/(?: *?([^",]+?) *?,|" *?(.+?)" *?,|( *?),)/).slice(1).reduce((a, b) => (a.length > 0 && a[a.length - 1].length < 4) ? [...a.slice(0, a.length - 1), [...a[a.length - 1], b]] : [...a, [b]], []).map(e => e.reduce((a, b) => a !== undefined ? a : b, undefined))

Alternatively, presented in a more aesthetically pleasing manner:

var cells = (rows[i] + ',')
  .split(/(?: *?([^",]+?) *?,|" *?(.+?)" *?,|( *?),)/)
  .slice(1)
  .reduce(
    (a, b) => (a.length > 0 && a[a.length - 1].length < 4)
      ? [...a.slice(0, a.length - 1), [...a[a.length - 1], b]]
      : [...a, [b]],
    [],
  )
  .map(
    e => e.reduce(
      (a, b) => a !== undefined ? a : b, undefined,
    ),
  )
;

While it may seem lengthy, this code remains elegantly functional in its design. Allow me to elaborate:

To begin with, let's discuss the regular expression component. Essentially, each segment you desire can fall into one of three potential scenarios:

*?([^",]+?) *?,, which represents a string absent of " or , enclosed by spaces and followed by a ,.
" *?(.+?)" *?,, representing a string enveloped within quotes, preceded and succeeded by an indeterminate number of spaces, followed by a ,.
( *?),, indicating any number of spaces, trailed by a ','.

Therefore, dividing based on a non-capturing group that includes these three possibilities will lead us to the desired outcome.

Keep in mind that when splitting using a regular expression, the resulting array comprises of:

Strings separated by the separator (the regular expression)
All the capturing groups identified in the separator

In our current scenario, the separator encompasses the entire string; thus, the separated strings are predominantly empty barring the last desired section which lacks a trailing ,. Consequently, the resulting array structure should resemble:

An empty string
Three strings denoting the three matching capturing groups from the first delimiter
An empty string
Three strings signifying the three matching capturing groups from the second delimiter
...
An empty string
The concluding desired part standing independently

Hence, by adding a , at the conclusion, we achieve a harmonious pattern. This explains the presence of (rows[i] + ',').

The resultant array now consists of capturing groups segregated by empty strings. Removing the initial empty string, they appear in sets of four: [ 1st capturing group, 2nd capturing group, 3rd capturing group, empty string ].

The purpose of the reduce function is to organize them into clusters of four:

  .reduce(
    (a, b) => (a.length > 0 && a[a.length - 1].length < 4)
      ? [...a.slice(0, a.length - 1), [...a[a.length - 1], b]]
      : [...a, [b]],
    [],
  )

Ultimately, identifying the premier non-undefined elements (an unmatched capturing group manifests as undefined) within each cluster reveals the sought-after components:

  .map(
    e => e.reduce(
      (a, b) => a !== undefined ? a : b, undefined,
    ),
  )

This brings about the resolution in full.

With that said, I propose the following succinct alternatives:

var cells = rows[i].split(/([^",]+?|".+?") *, */).filter(e => e)

Alternatively, if quotations extraction is superfluous:

var cells = rows[i].split(/(?:([^",]+?)|"(.+?)") *, */).filter(e => e)

Answer 2

Update:

I have come up with what I believe to be the optimal final version in a single line:

var cells = (rows[i] + ',').split(/(?: *?([^",]+?) *?,|" *?(.+?)" *?,|( *?),)/).slice(1).reduce((a, b) => (a.length > 0 && a[a.length - 1].length < 4) ? [...a.slice(0, a.length - 1), [...a[a.length - 1], b]] : [...a, [b]], []).map(e => e.reduce((a, b) => a !== undefined ? a : b, undefined))

Alternatively, presented in a more aesthetically pleasing manner:

var cells = (rows[i] + ',')
  .split(/(?: *?([^",]+?) *?,|" *?(.+?)" *?,|( *?),)/)
  .slice(1)
  .reduce(
    (a, b) => (a.length > 0 && a[a.length - 1].length < 4)
      ? [...a.slice(0, a.length - 1), [...a[a.length - 1], b]]
      : [...a, [b]],
    [],
  )
  .map(
    e => e.reduce(
      (a, b) => a !== undefined ? a : b, undefined,
    ),
  )
;

While it may seem lengthy, this code remains elegantly functional in its design. Allow me to elaborate:

To begin with, let's discuss the regular expression component. Essentially, each segment you desire can fall into one of three potential scenarios:

*?([^",]+?) *?,, which represents a string absent of " or , enclosed by spaces and followed by a ,.
" *?(.+?)" *?,, representing a string enveloped within quotes, preceded and succeeded by an indeterminate number of spaces, followed by a ,.
( *?),, indicating any number of spaces, trailed by a ','.

Therefore, dividing based on a non-capturing group that includes these three possibilities will lead us to the desired outcome.

Keep in mind that when splitting using a regular expression, the resulting array comprises of:

Strings separated by the separator (the regular expression)
All the capturing groups identified in the separator

In our current scenario, the separator encompasses the entire string; thus, the separated strings are predominantly empty barring the last desired section which lacks a trailing ,. Consequently, the resulting array structure should resemble:

An empty string
Three strings denoting the three matching capturing groups from the first delimiter
An empty string
Three strings signifying the three matching capturing groups from the second delimiter
...
An empty string
The concluding desired part standing independently

Hence, by adding a , at the conclusion, we achieve a harmonious pattern. This explains the presence of (rows[i] + ',').

The resultant array now consists of capturing groups segregated by empty strings. Removing the initial empty string, they appear in sets of four: [ 1st capturing group, 2nd capturing group, 3rd capturing group, empty string ].

The purpose of the reduce function is to organize them into clusters of four:

  .reduce(
    (a, b) => (a.length > 0 && a[a.length - 1].length < 4)
      ? [...a.slice(0, a.length - 1), [...a[a.length - 1], b]]
      : [...a, [b]],
    [],
  )

Ultimately, identifying the premier non-undefined elements (an unmatched capturing group manifests as undefined) within each cluster reveals the sought-after components:

  .map(
    e => e.reduce(
      (a, b) => a !== undefined ? a : b, undefined,
    ),
  )

This brings about the resolution in full.

With that said, I propose the following succinct alternatives:

var cells = rows[i].split(/([^",]+?|".+?") *, */).filter(e => e)

Alternatively, if quotations extraction is superfluous:

var cells = rows[i].split(/(?:([^",]+?)|"(.+?)") *, */).filter(e => e)

Is there a way to separate text by commas without including any commas that are within quotation marks?

Answer №1

Similar questions

Issue: The element 'app-header' is unrecognized in Angular 2

Changing the way in which text is selected and copied from a webpage with visible white space modifications

Adding a filter function to an ngFor loop upon a user's click event in Angular

AngularTS - Using $apply stops the controller from initializing

Utilizing Observable to dynamically bind ngClass in Angular

Ratio validation pattern is utilized by ng-pattern

Python2 regular expression for determining words containing a mix of different languages at the end

Is it possible to use regex to substitute text that includes round brackets (parentheses)?

Analyzing text to extract URLs that may have trailing commas

Unicode character splitting function - divide and conquer!

What are the properties used in functional components of React?

Utilizing the dialogue feature within Angular 6

PHP regex vsprintf method to add a slash on file extension

Utilizing union type return values in Typescript

Error: The argument passed to the function must be an Array type. Undefined value was received instead of an array

After applying the withStyles and withTranslation higher order components to a React component, a Typescript error is displayed

Is there a way to determine if a browser's Storage object is localStorage or sessionStorage in order to effectively handle static and dynamic secret keys within a client?

Adding a Third-Party JavaScript Plugin to Angular 7

Angular: Implementing a Dark and Light Mode Toggle with Bootstrap 4

What is the process for removing a specific column (identified by its key value) from a JSON table using JavaScript and Typescript?