Bash CSV Handling - Revision history

Beard: /* Empty Fields */

2019-06-27T18:40:56Z

‎Empty Fields

Beard: /* Quoted Strings with Commas */

2019-06-27T18:29:31Z

‎Quoted Strings with Commas

Beard at 18:26, 27 June 2019

2019-06-27T18:26:38Z

Beard: /* Quoted Strings with Commas */

2019-06-27T18:25:22Z

‎Quoted Strings with Commas

Beard: /* Quoted Strings with Commas */

2019-06-27T18:22:51Z

‎Quoted Strings with Commas

Beard: /* Quoted Strings with Commas */

2019-06-27T18:00:46Z

‎Quoted Strings with Commas

Beard: Created page with "I'm sick of screwing this up, so I'm creating this definitive guide for my own reference. I will update this as I learn to handle individual issues. = Quoted Strings with Com..."

2019-06-27T16:35:51Z

Created page with "I'm sick of screwing this up, so I'm creating this definitive guide for my own reference. I will update this as I learn to handle individual issues. = Quoted Strings with Com..."

New page

I'm sick of screwing this up, so I'm creating this definitive guide for my own reference. I will update this as I learn to handle individual issues.

= Quoted Strings with Commas =

← Older revision		Revision as of 18:40, 27 June 2019
Line 47:		Line 47:

	= Empty Fields =		= Empty Fields =
		+
		+	On line 4 of the example file, we see that there is no phone number, so we have a field that is null. This is entirely valid CSV, but how do we handle it?
		+
		+	The naive approach of just separating fields by the comma delimiter presents a problem:
		+	<source lang="shell">
		+	cat test.csv \| column -t -s ","
		+	</source>
		+
		+	<pre class="result">
		+	id firstname lastname email phone hiredate
		+	1111 "Colin" "Quinn" "c.quinn@example.com" 1112223333 "02/12/2017"
		+	2222 "Richard" "Vos Richard" "r.vos@example.com" 2223334444 "06/27/2019"
		+	3333 "Patrice" "O'Neal" "p.o'neal@example.com" 3334445555 "08/11/2018"
		+	4444 "Jim" "Jefferies" "j.jefferies@example.com" "09/11/2001"
		+	</pre>
		+	The <i>phone</i> field gets populated by the <i>hiredate</i> field.
		+
		+	The best way to handle this is to pass the output through <code>awk</code> while defining the <i>output field separator (OFS)</i> as having a space before the comma:
		+	<source lang="shell">
		+	cat test.csv \| awk -F, -vOFS=" ," '{$1=$1}1' \| column -t -s ","
		+	</source>
		+
		+	<pre class="result">
		+	id firstname lastname email phone hiredate
		+	1111 "Colin" "Quinn" "c.quinn@example.com" 1112223333 "02/12/2017"
		+	2222 "Richard" "Vos Richard" "r.vos@example.com" 2223334444 "06/27/2019"
		+	3333 "Patrice" "O'Neal" "p.o'neal@example.com" 3334445555 "08/11/2018"
		+	4444 "Jim" "Jefferies" "j.jefferies@example.com" "09/11/2001"
		+	</pre>
		+
		+	Notice now that the <i>phone</i> field is blank as it should be.

← Older revision		Revision as of 18:29, 27 June 2019
Line 45:		Line 45:

	<i>Note: Obviously, the last name needs to be corrected, but we don't want mistakes like this to break automated processes. Also, you should be using input validation for anything that takes user input, but this is for when that is not possible.</i>		<i>Note: Obviously, the last name needs to be corrected, but we don't want mistakes like this to break automated processes. Also, you should be using input validation for anything that takes user input, but this is for when that is not possible.</i>
		+
		+	= Empty Fields =

← Older revision		Revision as of 18:26, 27 June 2019
Line 1:		Line 1:
−	I'm sick of screwing ~~this up~~, so I'm creating this definitive guide for my own reference. I will update this as I learn to handle individual issues.	+	I'm sick of screwing around with the same caveats in CSV files, so I'm creating this definitive guide for my own reference. I will update this as I learn to handle individual issues.
−

	For the following examples, I will use this sample CSV file <code>test.csv</code> (assuming Unix line ending "\n"). I've included several "problems" in it.		For the following examples, I will use this sample CSV file <code>test.csv</code> (assuming Unix line ending "\n"). I've included several "problems" in it.

← Older revision		Revision as of 18:25, 27 June 2019
Line 44:		Line 44:
	4444 "Jim" "Jefferies" "j.jefferies@example.com" "09/11/2001"		4444 "Jim" "Jefferies" "j.jefferies@example.com" "09/11/2001"
	</pre>		</pre>
		+
		+	<i>Note: Obviously, the last name needs to be corrected, but we don't want mistakes like this to break automated processes. Also, you should be using input validation for anything that takes user input, but this is for when that is not possible.</i>

@@ Line 1: / Line 1: @@
 I'm sick of screwing this up, so I'm creating this definitive guide for my own reference. I will update this as I learn to handle individual issues.
 = Quoted Strings with Commas =
@@ Line 5: / Line 15: @@
 When your fields are separated by commas, and one of the fields has a quoted string with a comma in it, it can screw up the parsing of the fields.
-Here is a sample CSV file <code>test.csv</code>:
+The problem in the sample file is in the second line. Someone accidentally entered "Vos, Richard" in a field that is only supposed to contain the last name "Vos". This kind of clerical error happens all the time. Not only is it incorrect, it has a comma in the text which is the character we use to delimit the fields.
 <pre>
-id,firstname,lastname,email,phone,hiredate
+if we try to naively break out the fields into separate columns, we see a problem:
-,"Colin","Quinn","c.quinn@example.com",1112223333,"02/12/2017"
+<source lang="shell">
-,"Richard","Vos, Richard","r.vos@example.com",2223334444,"06/27/2019"
+cat test.csv | column -t -s ","
-,"Patrice","O'Neal","p.o'neal@example.com",3334445555,"08/11/2018"
+</source>
-,"Jim","Jefferies","j.jefferies@example.com",,"09/11/2001"
 </pre>

← Older revision		Revision as of 18:00, 27 June 2019
Line 2:		Line 2:

	= Quoted Strings with Commas =		= Quoted Strings with Commas =
		+
		+	When your fields are separated by commas, and one of the fields has a quoted string with a comma in it, it can screw up the parsing of the fields.
		+
		+	Here is a sample CSV file <code>test.csv</code>:
		+	<pre>
		+	id,firstname,lastname,email,phone,hiredate
		+	1122,"Colin","Quinn","c.quinn@example.com",1112223333,"02/12/2017"
		+	2233,"Richard","Vos, Richard","r.vos@example.com",2223334444,"06/27/2019"
		+	3344,"Patrice","O'Neal","p.o'neal@example.com",3334445555,"08/11/2018"
		+	4455,"Jim","Jefferies","j.jefferies@example.com",,"09/11/2001"
		+	</pre>