CODEWITHSUNDEEP
mysqlsqlbash
Jamie Reid
Jamie Reid

Reputation: 552

SQL SELECT INTO OUTFILE Not working as expected

I am stuck with an SQL query running from a bash script.

The following works and outputs the file as expected but there are some fields that contain commas and I would like these fields to be included but have the commas escaped.

FILE="/var/lib/mysql-files/UMHP/ConditionAssessment.csv"
mysql --user='root' --password='xxxxxxxxxxxxxxx' UMHPSurvey << EOFMYSQL

SELECT 
DateEntry AS 'Entry Date',
FirstName AS 'First Name',
LastName As 'Last Name',
member_id as 'Member ID',
mobile AS 'Mobile',
Doctor AS 'Need help getting connected to a doctor',
CASE WHEN HealthConditions LIKE '%Asthma%' THEN 'Yes' ELSE 'No' END AS 'Asthma',
CASE WHEN HealthConditions LIKE '%COPD%' THEN 'Yes' ELSE 'No' END AS 'COPD',
CASE WHEN HealthConditions LIKE '%Cancer' THEN 'Yes' ELSE 'No' END AS 'Cancer',
CASE WHEN HealthConditions LIKE '%CHF%' THEN 'Yes' ELSE 'No' END AS 'CHF',
CASE WHEN HealthConditions LIKE '%Diabetes%' THEN 'Yes' ELSE 'No' END AS 'Diabetes',
CASE WHEN HealthConditions LIKE '%Hypertension (High Blood Pressure)%' THEN 'Yes' ELSE 'No' END AS 'Hypertension (High Blood Pressure)',
AdditionalConditionsDescription AS 'Additional Health Conditions',
MedicationQuestions AS 'Questions about meds',
InsuranceQuestionsDescription AS 'Healthcare concerns'
FROM UMHPSurvey.Assessment
where Doctor = 'Yes' or ConditionQuestions = 'Yes' or AdditionalConditions = 'Yes'
or MedicationQuestions = 'Yes' or Concerns = 'Yes'
INTO OUTFILE '$FILE' FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
EOFMYSQL

The following doesn't

FILE="/var/lib/mysql-files/ConditionAssessment.csv"
mysql --user='root' --password='xxxxxxxxxxxxxxx' UMHPSurvey << EOFMYSQL

SELECT 
DateEntry AS 'Entry Date',
FirstName AS 'First Name',
LastName As 'Last Name',
member_id as 'Member ID',
mobile AS 'Mobile',
Doctor AS 'Need help getting connected to a doctor',
CASE WHEN HealthConditions LIKE '%Asthma%' THEN 'Yes' ELSE 'No' END AS 'Asthma',
CASE WHEN HealthConditions LIKE '%COPD%' THEN 'Yes' ELSE 'No' END AS 'COPD',
CASE WHEN HealthConditions LIKE '%Cancer' THEN 'Yes' ELSE 'No' END AS 'Cancer',
CASE WHEN HealthConditions LIKE '%CHF%' THEN 'Yes' ELSE 'No' END AS 'CHF',
CASE WHEN HealthConditions LIKE '%Diabetes%' THEN 'Yes' ELSE 'No' END AS 'Diabetes',
CASE WHEN HealthConditions LIKE '%Hypertension (High Blood Pressure)%' THEN 'Yes' ELSE 'No' END AS 'Hypertension (High Blood Pressure)',
AdditionalConditionsDescription AS 'Additional Health Conditions',
MedicationQuestions AS 'Questions about meds',
InsuranceQuestionsDescription AS 'Healthcare concerns'
FROM UMHPSurvey.Assessment
where Doctor = 'Yes' or ConditionQuestions = 'Yes' or AdditionalConditions = 'Yes'
or MedicationQuestions = 'Yes' or Concerns = 'Yes'
INTO OUTFILE '$FILE' 
FIELDS ESCAPED BY '\\' 
OPTIONALLY ENCLOSED BY '\"' 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
EOFMYSQL

Giving the error below

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR at line 2: Unknown command '\"'.

I am not sure what I am doing wrong.

I have also tried the following

FILE="/var/lib/mysql-files/ConditionAssessment.csv"
mysql --user='root' --password='xxxxxxxxxxxxxxx' UMHPSurvey << EOFMYSQL

SELECT 
DateEntry AS 'Entry Date',
FirstName AS 'First Name',
LastName As 'Last Name',
member_id as 'Member ID',
mobile AS 'Mobile',
Doctor AS 'Need help getting connected to a doctor',
CASE WHEN HealthConditions LIKE '%Asthma%' THEN 'Yes' ELSE 'No' END AS 'Asthma',
CASE WHEN HealthConditions LIKE '%COPD%' THEN 'Yes' ELSE 'No' END AS 'COPD',
CASE WHEN HealthConditions LIKE '%Cancer' THEN 'Yes' ELSE 'No' END AS 'Cancer',
CASE WHEN HealthConditions LIKE '%CHF%' THEN 'Yes' ELSE 'No' END AS 'CHF',
CASE WHEN HealthConditions LIKE '%Diabetes%' THEN 'Yes' ELSE 'No' END AS 'Diabetes',
CASE WHEN HealthConditions LIKE '%Hypertension (High Blood Pressure)%' THEN 'Yes' ELSE 'No' END AS 'Hypertension (High Blood Pressure)',
AdditionalConditionsDescription AS 'Additional Health Conditions',
MedicationQuestions AS 'Questions about meds',
InsuranceQuestionsDescription AS 'Healthcare concerns'
INTO OUTFILE '$FILE' 
FIELDS ESCAPED BY '\\' 
OPTIONALLY ENCLOSED BY '\"' 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
FROM UMHPSurvey.Assessment
where Doctor = 'Yes' or ConditionQuestions = 'Yes' or AdditionalConditions = 'Yes'
or MedicationQuestions = 'Yes' or Concerns = 'Yes'
EOFMYSQL

Giving the same error (below)

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR at line 2: Unknown command '\"'.

Can anyone help me understand where I am going wrong

Upvotes: 0

Views: 403

Answers (1)

Eric Bolinger
Eric Bolinger

Reputation: 2912

Because you're using parameter expansion to substitute $FILE, you also get backslash quoting inside the "here document". What the shell is sending to MySQL looks like this:

FIELDS ESCAPED BY '\'

Try double-escaping the backslash:

FIELDS ESCAPED BY '\\\\'

You other use of backslash is unaffected. This is confusing. Read that section of the the manual carefully. To debug the here document, replace your call to mysql with cat:

cat - << EOFMYSQL

Upvotes: 2

Related Questions