Upload table to MySQL. Columns with TEXT and \n\r

[#1]

I have a table in JMP with some numeric columns, character columns, date columns and a couple columns with big chunks of text (full of special characters, commas, .

I would like to upload this table to MySQL but I am facing some challenges. I have tried several methods:

Method 1 - save as csv, upload via workbench/other mysql tool

JMP does not seem to code the text pieces (special characters) into the csv properly and when I upload the csv, some of the characters in the text blocks are interpreted as different cells and the full row of data ends up being messed up. I corrected the "\n" and "\r" manually but there are still some other characters that are not well coded and make the csv fail to upload correctly.

Method 2 - upload via JMP "upload table to DB"

Unfortunately, this option only allows for AUTOMATIC (no data column type selection) uploading of a NEW table. This results in JMP defining the text blocks as CHAR (255) which is not big enough to accomadate the text I have and it therefore gets truncated.

My ideal solution:

- I would just create a table in my schema with the correct columns and column definitions.

- I would then use a script to upload the data to that pre-existing empty (or not!) table.

I have been looking in the community and I have found bits and pieces of code that may help me to start putting together a script to do the above.

Three questions then:

- Am I doing something wrong when converting to csv or is this just a bug in JMP that needs to be adressed?

- Any other idea/methods you could think of to upload this dataset?

- Any help with putting together the above script would be very welcome!

Thanks in advance!

[#2]

How about:

1. Save a .csv file with every field except for the long text fields

2. Upload the .csv file to MySQL

3. From within JMP loop over the table and update the table in MySQL with the long text field

[#3]

It looks like mySQL can be told how to import CSV:

php - Line break issue from CSV to MySQL - Stack Overflow

Correct LINES TERMINATED BY when importing uploaded CSV into MySQL table from PHP - Stack Overflow​

edit: if that turns out to be the easy answer, please post back the mySQL syntax that makes it work, thanks!

[#4 · Accepted solution]

seems to work:  I made a CSV from this table:

And, after several false starts, imported it like this:

load data  infile '/home/c/Desktop/windowsShare/stories.csv' replace into table stories fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

And then dumped the data in hex with

select hex(story) from stories;

+------------------------------------------------------------------------------------------+

| hex(story)                                                                               |

+------------------------------------------------------------------------------------------+

deleted false starts...

| 7468697320697320612074776F0D6C696E652073746F7279                                         |

| 7468697320697320612074687265650D6C696E650D73746F7279                                     |

| 6F6E65206C696E6572                                                                       |

+------------------------------------------------------------------------------------------+

I made the CR (0D) characters bold.

I'm not a mysql expert; I don't know if mysql can handle column names in the data better.  I made the mysql table and the JMP table have the same order to get this to work.

Without the HEX, it is a bit hard to understand what happened:

select * from stories;

+--------+----------------------------------------------+-------+

| name   | story                                        | grade |

+--------+----------------------------------------------+-------+

deleted false starts

| name   | story                                        |     0 |

line story                     |    12 |

story                   |    12 |

| fred   | one liner                                    |    99 |

+--------+----------------------------------------------+-------+

9 rows in set (0.00 sec)

(This is output in a linux shell window).  The CR does not have a LF (the way this data table was created).  that moves the cursor to the beginning of the line but does not line feed to a new line...typing over the beginning of the line.  The data is actually in the mysql table, as shown by the hex.

Finally, the dump of the CSV:

loadtextfile("$desktop/stories.csv",blob)

Char To Blob(

    "name,story,grade~0D~0AKATIE,~22this is a two~0Dline story~22,12~0D~0ALOUISE,~22this is a three~0Dline~0Dstory~22,12~0D~0Afred,one liner,99~0D~0A",

    "ascii~hex"

)

You can see that JMP, on Windows, uses CRLF at the ends of lines (bold ~0D~0A). 

[#5]

There have been several improvements in JMP 13 (coming soon) with respect to Database integration (File->Database->Save Table and File->Database->Open Table).