0

Trying this again...

I have a survey that I'm trying to analyze in SQL and some people have commented using a comma (i.e. "Sick today, need Vit C.") and when I'm trying to load it into SQL that comma is reading as the delimiter and not reading the last column.

My csv is delimited by commas so I understand whats happening I just don't know how to change only commas inside text.

Should I download the file from google sheets as tab separated?

Can I sub the any commas in that column (the comments section) to be a space using sed, grep, tr or awk?

I setup questionnaire table in postgresSQL and I set comments column to TEXT.

I have the VassarXCTFReadinessQuestionnaire.csv saved in my server.

I'm converting VassarXCTFReadinessQuestionnaire.csv to quest_ready.csv with:

cat VassarXCTFReadinessQuestionnaire.csv | cut -d',' -f1-11 | grep -v ',NA' > quest_ready.csv

Actual data in VassarXCTFReadinessQuestionnaire.csv:

W/ Comments and comma in comment column:

11/7/18,Jackson Picker,3,3,3,3,4,3,7,"feeling alright, same situation with the hip.",4.1,,

w/o comments:

11/7/18,Hannah Happy,4,2,2,3,3,4,9,,4.35,,

w/ comments w/o comma:

11/6/18,Hannah Happy,4,2,2,3,3,4,9,All Good!,4.35,,

Desired result in quest_ready.csv is:

11/7/18,Jackson Picker,3,3,3,3,4,3,7,"feeling alright same situation with the hip.",4.1,,
11/7/18,Hannah Happy,4,2,2,3,3,4,9,,4.35,,
11/6/18,Hannah Happy,4,2,2,3,3,4,9,All Good!,4.35,,

THANKS!

Barmar
  • 741,623
  • 53
  • 500
  • 612
jutnut627
  • 1
  • 2
  • How are you loading the CSV into SQL? If you use `LOAD DATA INFILE`, the `FIELDS OPTIONALLY ENCLOSED BY '"'` clause should make it work. – Barmar Jan 17 '19 at 17:35
  • Also, what RDBMS are you using? MySQL, SQL-Server, Oracle, Postgres, etc.? – Barmar Jan 17 '19 at 17:36
  • New to all of this so I appreciate every bit of help! I'm loading it from local to remote server via scp -r then processing the file1 into a new file2 then login into postgresSQL on my server and loading it an already created table with COPY table_name FROM '/home/server/file_name.csv' DELIMITER ',' CSV HEADER; – jutnut627 Jan 17 '19 at 18:19

2 Answers2

0

Crude implementation, but:

#!/bin/bash

string=$(grep -o '".*"' my.csv | tr ',' ' ')

before=$(cat my.csv | cut -d',' -f1-9 | grep -v ',NA')
after=$(cat my.csv | cut -d',' -f12 | grep -v ',NA')
echo "${before},${string},${after}"

Output:

11/7/18,Jackson Pierce,3,3,3,3,4,3,7,"feeling alright same situation with the hip.",3.56

itChi
  • 642
  • 6
  • 19
  • I'm trying to achieve this at the terminal level. This does work but it just returns the value and doesn't change in the .csv Very new to this so I'm lost. Thanks, and sorry! – jutnut627 Jan 17 '19 at 18:33
0

You can't use cut to select fields when the delimiter can appear inside a field; it doesn't provide any way to escape delimiters.

Instead, you should copy directly from the VassarXCTFReadinessQuestionnaire.csv into the database table, rather than trying to convert it first.

Use the QUOTE parameter to the COPY command so that it recognizes quoted fields and doesn't use the commas inside as delimiters.

COPY table_name 
FROM '/home/server/file_name.csv' 
DELIMITER ',' 
QUOTE '"'

If this file has extra fields that don't correspond to table columns, see

Copy a few of the columns of a csv file into a table

Barmar
  • 741,623
  • 53
  • 500
  • 612