Properly formatted CSV not possible with MySQL’s “INTO OUTFILE”

I have been attempting to export my data from a SELECT statement into CSV format without having to modify the original SELECT statement. This is apparently not possible.
 
Let’s start with outputing using the default, TAB delimited:

INTO OUTFILE "test1.csv"

RMC200BK        SYNTHETIC,2",BLACK      \N      01/01/2014

RMC200BK
SYNTHETIC,2",BLACK
\N
01/01/2014

 
This works fine, I’ve got my item, description with quote and commas included, then a NULL field, and finally my date field.
Now I want CSV instead of TAB delimited so let’s terminate by a comma.
 

INTO OUTFILE "test2.csv" FIELDS TERMINATED BY ','

RMC200BK,SYNTHETIC\,2"\,BLACK,\N,01/01/2014

RMC200BK
SYNTHETIC\
2"\
BLACK
\N
01/01/2014

 
Well that doesn’t work. I get a backslash in front of all my commas in my description, not just in front of my “N” (NULL) field. Let’s try enclosing everything with quotes so all my text stays together in the description field.
 

INTO OUTFILE "test3.csv" 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'

"RMC200BK","SYNTHETIC,2\",BLACK",\N,"01/01/2014"

RMC200BK
SYNTHETIC,2\
BLACK"
\N
01/01/2014

 
Well that doesn’t work either. Sure, I’ve got quotes around my text, but the backslash has changed from being in front of the commas and is now being added in front of my double quotes in my description field. That’s not proper CSV, it should be an extra double quote instead, so let’s change the escape character from backslash to double quote.
 

INTO OUTFILE "test4.csv" 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
ESCAPED BY '"'

"RMC200BK","SYNTHETIC,2"",BLACK","N,"01/01/2014"

RMC200BK
SYNTHETIC,2",BLACK
N,01/01/2014

 
Still not right. I’ve got my description properly quoted, but now it sees the NULL and adds a double quote in front to escape it, so now that field doesn’t terminate properly. As a last resort, let’s try terminating and escaping fields, but not enclosing them.
 

INTO OUTFILE "/data/backups/abcs3/test5.csv" 
FIELDS TERMINATED BY ',' 
ESCAPED BY '"'

RMC200BK,SYNTHETIC",2""",BLACK,"N,01/01/2014

RMC200BK
SYNTHETIC"
2"""
BLACK
N,01/01/2014

 
Not even close.
 
Conclusion: It’s not possible to export properly formatted CSV files without having to modify the original SELECT statement. The only exception would be if you knew none of your data contained NULLs or none of your data contained double quotes.

Advertisements

EXCEL Add-ins and MySQL Scripts

I write a lot of update scripts on MySQL and many times the data I update comes from Excel.  In order to get the data I want, I create a table of the data from the spreadsheet.  It ends up looking something like:

Drop Table If Exists _TempTable;
Create Table _TempTable
(value1 varchar(10)
, value2 varchar(100)
, value3 decimal(10,2));
Insert Into _TempTable
Values
('01','2','3.00')
,('A','B''C','5.23')
;

And that’s fine but to easily create the inserts for multiple rows I created an Excel Add-in.

The specifications of the add-in are that

1. You give it a range of cells
2. It will create the MySQL script complete with single quotes around it
3. It will take any single quotes from the input and double them (Example: the second line, second value will insert B’C)
4. It will keep any leading zeroes (Example: the first line, first value will keep 01 instead of making it 1)
5. It will trim spaces from the beginning and end of each cell’s value

Here’s the script.

Function InsertIntoValues_Range(v As Range)
 If v Is Nothing Then Exit Function
 Dim cell As Range
 InsertIntoValues_Range = ",('"
 For Each cell In v.Cells
    InsertIntoValues_Range = InsertIntoValues_Range + Replace(Trim(cell.Text), "'", "''") + "','"
 Next cell
 InsertIntoValues_Range = Left(InsertIntoValues_Range, Len(InsertIntoValues_Range) - 2) + ")"
End Function

If you’d like to automatically replace empty strings with NULL, add this function:

Function InsertIntoValues_Range_Nullable(v As Range)
 If v Is Nothing Then Exit Function
 Dim cell As Range
 InsertIntoValues_Range_Nullable = ",("
 For Each cell In v.Cells
    If Trim(cell.Text) = "" Then
        InsertIntoValues_Range_Nullable = InsertIntoValues_Range_Nullable + "NULL,"
    Else
        InsertIntoValues_Range_Nullable = InsertIntoValues_Range_Nullable + "'" + Replace(Trim(cell.Text), "'", "''") + "',"
    End If
 Next cell
 InsertIntoValues_Range_Nullable = Left(InsertIntoValues_Range_Nullable, Len(InsertIntoValues_Range_Nullable) - 1) + ")"
End Function

To add it to your worksheet, which will make the functions only available to that worksheet

1. Just hit <Alt>+F11 to open the Visual Basic editor
2. Right-click your worksheet and choose Insert | Module
3. In the opened window paste the above function
4. Close the Visual Basic application (<Alt>+Q)
5. The worksheet will now have this new function available

To make this an Add-In which makes the functions automatically available to any Excel workbook you open

1. Add the functions to an empty workbook using steps above
2. Save the workbook as an .xla file
3. Choose File | Options, Add-Ins
4. Manage: Excel Add-ins, “Go..”
5. Select your Add-In (browse for it if it’s not listed) and hit “OK”

So now all I have to do is type in something like “=InsertIntoValues_Range(A1:C1)” and double-click the bottom right cell corner to apply it all the way down and I’ve got myself the second half of my insert statement.

MySQL Numeric Functions

Some useful numeric MySQL functions I wrote (unless sourced elsewhere). When I created these I was originally looking to clean some phone numbers and format them, but found there was not a way to get only numeric characters from a string, let alone an IsNumeric function in MySQL.

IsNumeric()
Accepts varchar up to 255 in length.
Returns 1 if string is numeric; returns 0 if fails test. (Source)

DROP FUNCTION IF EXISTS IsNumeric;

CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint 
 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

NumericOnly()
Accepts varchar up to 255 in length.
Returns only the characters from passed in string which are numeric. (Removes all non-numeric characters.)

DROP FUNCTION IF EXISTS NumericOnly;

CREATE FUNCTION NumericOnly (val VARCHAR(255)) 
 RETURNS VARCHAR(255)
BEGIN
 DECLARE idx INT DEFAULT 0;
 IF ISNULL(val) THEN RETURN NULL; END IF;

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = LENGTH(val);
  WHILE idx > 0 DO
  IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
   SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
   SET idx = LENGTH(val)+1;
  END IF;
  SET idx = idx - 1;
  END WHILE;
  RETURN val;
 END;

FormatPhone()
Accepts varchar up to 255 in length.
Returns string with the 10 right-most digits from input string (padded on the left with zeros) in the format: (###) ###-####

  DROP FUNCTION IF EXISTS FormatPhone;

  CREATE FUNCTION FormatPhone (val VARCHAR(255))
   RETURNS VARCHAR(255)
 BEGIN
  SET val = RIGHT(CONCAT("0000000000",NumericOnly(val)),10);
  RETURN CONCAT("(",LEFT(val,3),") ",LEFT(RIGHT(val,7),3),"-",RIGHT(val,4));
 END;

Things Wrong With (or I dislike about) MySQL Workbench

1. The “Find” feature

• The “Find” icon button doesn’t search what’s in the textbox, it searches what you last put in the textbox and hit “Enter” and searched for.
• Doesn’t bring up a prompt like “Replace” does.
• Doesn’t respond with any information if no results found.
• If I have a word highlighted, it doesn’t automatically get placed in the textbox of the “Find”, I must copy and paste it there.

2. SQL problems

• Today I did a count of records in a table, found none, inserted a single record and got a “Duplicate entry” error!  Had to view the table from command line to see that there was actually already a row in the table, but Workbench never could return any results!

3. Slooooowwww

• Takes approx. 60 sec to refresh database list.  Then if I click to expand a database, takes another 60 sec.  Then if I click on a table, takes another 60 sec.

4. Query Tabs

• Can’t re-arrange tabs.
• Can’t middle-click to close a tab.
• Have to leave at least one tab open.

5. Object Browser

• I can’t view the Primary Key of a table in the little text summary below or by expanding a table.  I must right-click and “Alter table” to view this information.

6. Glitches

• Sometimes when I use the shortcut <CTRL>+<ENTER> to execute a query, it doesn’t do anything and I have to try a second time for workspace to respond.