![]() When you select the CSV file in the import dialog, you can see there's a "Fields escaped by" setting underneath the file selection dialog. The problem here is that the CSV you have uses Excel-style quote escaping, where escaped double quotes are represented by "" instead of using backslash escaping - \". Realistically, it's a simple thing I should've thought of myself, but if I missed it and others have too, I thought it was worth documenting here so that others could save time looking for a solution. I did a bit of searching and eventually found a source that outlined the solution to the problem. Upgrading to the latest version of Sequel Pro didn't help either. It seemed that Sequel Pro's import engine was not able to interpret the "" encoding of " content and the age-old options of backslashing \" or custom delimiters are no longer available. I tried to looking for options to use different delimiters, but this is not available Excel (certainly not the version I use), I tried opening the spreadsheet in Google Docs and saving as CSV from there, but this returned the same result. ![]() This time I was getting fields by split due to some of the fields containing quotes and commas in their text content. Usually I would just Save As a CSV (comma separated values) file, and then import the CSV file using OSX application Sequel Pro. On top of "ropey data", I also had some problems with importing the data into mySQL. The data in the spreadsheet has been problematic due as each row included cells with pipe delimited items, typos and other strangenesses that have slowed the pat to a relational database. One of those sources is a big Excel spreadsheet. I guess to be less aggressive, just change the column's character set.I've been working with a database migration project lately that is bringing together data from three different sources. Or change a column's character set ALTER TABLE anothertable MODIFY col1 CHAR(50) CHARACTER SET utf8 Perhaps you should load another table that has the matching character set: CREATE TABLE anothertable LIKE mytable Ĭhange the whole table's character set ALTER TABLE anothertable CONVERT TO CHARACTER SET charset_name You can also see the character set of the database mysql> show create database mydb\G | character_sets_dir | /usr/share/mysql/charsets/ | Please run this query and you will see something like this: mysql> show variables like 'character_set%' You may have to resort to setting the default character set to match that CSV file. If the CSV file was generated on another Mac OSx server, you should not be having this issue. See the following URLs as SequelPro's character set problems are not new If the CSV file was generated on a Windows machine, there could be some character set issues This may depend on where you generated the CSV file. This is what I'm getting when I run the character set query: show variables like 'character_set%' Ĭharacter_sets_dir /usr/local/mysql-5.6.10-osx10.7-x86_64/share/charsets/ Which ones doesn't MySQL accept and how would I find and replace them? ![]() Out of 130,000 rows, there's definitely the possibility for some non-English characters. ![]() csv file, which there were, but I got rid of those and the same thing happened. When I hit "Ok," everything else seems to work relatively fine, I'm just missing about 107,000 rows.Īny idea as to what it could be? Maybe I should use something other than auto-detect during the import? I thought that it might have been some extra commas floating around in the actual. Everything seems to work fine, then I get the message:įile Read Error: An error occurred when reading the file, as it could not be read using the encoding you selected (Auto-detect - Unicode (UTF-8)). I used the import function to upload a 130k. ![]()
0 Comments
Leave a Reply. |