# Trouble with SQL export **URL:** \ **Category:** Support and Helpdesk\ **Created:** [May 2, 2024, 3:02pm UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485 "2024-05-02T15:02:18Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![gordonmx](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/gordonmx/32/1344_2.png) [@gordonmx](https://forum.openrefine.org/u/gordonmx)\ **Post date:** [May 2, 2024, 3:02pm UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/1 "2024-05-02T15:02:18Z")
Hi. I'm getting strange errors when I import an SQL file (from a CSV file) made by OpenRefine into my MySQL database graphic interface - Sequel Ace. It used to work no problem, now I'm seeing: [ERROR in query 1] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"\__A_" VARCHAR(255) NULL, "_N\_N\_N\_N\_N\_N\_N\_N_" VARCHAR(255) NULL Is there something I'm supposed to be doing differently? I'm using Version 3.8.0 (TRUNK) ---
**Author:** ![timtom](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/timtom/32/55_2.png) [@timtom](https://forum.openrefine.org/u/timtom)\ **Post date:** [May 2, 2024, 7:29pm UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/2 "2024-05-02T19:29:45Z")
Hi and welcome! Is it possible there's a special character (like an apostrophe) in one of your column names? That may be incorrectly interpreted by your SQL client. Is _N\_N\_N\_N\_N\_N\_N\_N_ the name of one of your columns? If you're able to share a few lines from the faulty SQL file (in particular the CREATE statement) that could be helpful in diagnosing your issue. ---
**Author:** ![gordonmx](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/gordonmx/32/1344_2.png) [@gordonmx](https://forum.openrefine.org/u/gordonmx)\ **Post date:** [May 3, 2024, 9:11am UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/3 "2024-05-03T09:11:01Z")
Dear TimTom. Thank you for your reply. You got it in one. Yes, when I open my CSV file in OpenRefine 3.8.0, it adds several special characters to the first column. Instead of just "A", it adds "ÿþA" Yes, "NNNNNNNN", no spaces, is one of my columns. My process is to download the CSV from a website, open it in OpenRefine, strip out a formatting line, then export it, either as a TSV or SQL (I've tried both over time, but yesterday nothing worked.) Then I import it into Sequel Ace, an open source SQL database management tool. With my workflow, I don't see the CREATE statement, normally handled by the CSV import process in Sequel Ace unless it throws up an error. From an error I'm having right now, here is the CREATE statement: Error adding new table: An error occurred while trying to add the new table '20240503' by CREATE TABLE `20240503` ( `A` VARCHAR(255), `NNNNNNNN` VARCHAR(255), `NAME` VARCHAR(255), `STATUS` VARCHAR(255), `F` VARCHAR(255), `MMMMMMMM` VARCHAR(255), `NAME2` VARCHAR(255), `CCCCCCCC` VARCHAR(255), `NAME3` VARCHAR(255), `STARTING_DATE` VARCHAR(255), `ENDING_DATE` VARCHAR(255), `REGISTRATION_DATE` VARCHAR(255), `CLOSING_DATE` VARCHAR(255)). MySQL said: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`N' at line 3 If you have any further tips, I'd love to hear them. ---
**Author:** ![timtom](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/timtom/32/55_2.png) [@timtom](https://forum.openrefine.org/u/timtom)\ **Post date:** [May 3, 2024, 1:31pm UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/4 "2024-05-03T13:31:22Z")
I can see a few things. One is that the table name `20240503` may be an issue. As per the [MySQL documentation](https://dev.mysql.com/doc/refman/8.4/en/identifiers.html): "Identifiers may begin with a digit but unless quoted may not consist solely of digits." The problem is that the OpenRefine SQL exporter does not add quotes around table and column names (and I don't see an option to do so). So one thing you can do to fix this is to change the name of your table (which is the name of your OpenRefine project) so that it does not only contain digits. If however you do need your table to be named `20240503`, then you'll need to manually edit the SQL file generated by OpenRefine. It's a plain text file, so you can open it in e.g. TextEdit (if you're on a Mac) or Notepad (on Windows) and add **back ticks** on either side of the table name, e.g. change `CREATE TABLE 20240503 (` to `CREATE TABLE `20240503` (` and try again. The other thing that makes me pause are those special characters that you mention are added to the first column. This looks like an encoding issue: `ÿþ` is the [UTF-16 Byte-order mark interpreted as Windows-1252 encoding](https://en.wikipedia.org/wiki/Byte_order_mark#Byte-order_marks_by_encoding). When you first import your CSV into OpenRefine, try changing the value of the "Character encoding" field (just underneath the preview window) and set it to UTF-16 to see if it makes those characters disappear. That may solve some of your other issues as well, since working with incorrect encoding may add unexpected characters in unexpected places and potentially mess up your SQL statement. Hope this helps. ---
**Author:** ![timtom](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/timtom/32/55_2.png) [@timtom](https://forum.openrefine.org/u/timtom)\ **Post date:** [May 3, 2024, 1:42pm UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/5 "2024-05-03T13:42:12Z")
Edit: I just noticed that the latest version of OpenRefine (3.8.0) now includes quotes around column names in the SQL exporter, but still not around the table name. ---
**Author:** ![thadguidry](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/thadguidry/32/653_2.png) [@thadguidry](https://forum.openrefine.org/u/thadguidry)\ **Post date:** [May 3, 2024, 1:55pm UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/6 "2024-05-03T13:55:41Z")
Really? you sure? I thought that was working correctly last I looked. ```sql CREATE TABLE Clipboard ( " _____ ABC" VARCHAR(255) NULL, "2211" VARCHAR(255) NULL, "_22ABC" VARCHAR(255) NULL ); INSERT INTO Clipboard (" _____ ABC","2211","_22ABC") VALUES ( '111','222',null ), ( 'zzz','1212 ','1212' ), ( 'aaa','1212',null ) ``` Yeah, seems like we missed quoting around the table name? Please open an issue. We went with double quotes " " because most database vendors use them as the default "quoted identifier" or "delimited identifier". (MySQL loves being different...always.) ---
**Author:** ![timtom](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/timtom/32/55_2.png) [@timtom](https://forum.openrefine.org/u/timtom)\ **Post date:** [May 3, 2024, 2:14pm UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/7 "2024-05-03T14:14:12Z")
Yeah, sadly the double quotes in the table names wouldn't help in that particular case, since our friend mySQL expects backticks. It looks like [offering the user the option to specify the escape character was previously discussed but discarded in favour of the double quote](https://github.com/OpenRefine/OpenRefine/issues/4716#issuecomment-1130074206). ---
**Author:** ![thadguidry](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/thadguidry/32/653_2.png) [@thadguidry](https://forum.openrefine.org/u/thadguidry)\ **Post date:** [May 3, 2024, 2:39pm UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/8 "2024-05-03T14:39:27Z")
Hmm, we could add a toggle to the SQL Export dialog for - Delimit names with: Use "" double quotes Use `` backticks What do you think? If so, add that as a feature request. ---
**Author:** ![gordonmx](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/gordonmx/32/1344_2.png) [@gordonmx](https://forum.openrefine.org/u/gordonmx)\ **Post date:** [May 3, 2024, 2:44pm UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/9 "2024-05-03T14:44:07Z")
Hi Timtom, Thanks for your reply and public-spiritedness. I had spent hours trying to figure this out and am glad to have access to your expertise. I didn't know it would be an issue for your interpretation but I had edited the table name to remove easily google-able elements. The real table name is that date followed by four text characters, so that's not an issue. Thank you for the suggestion to use TextEdit on a Mac to edit the exported SQL. That's what I had already been doing on the exported TSV/CSV, which is what I was using since SQL wasn't importing, either, but I didn't know I could do on the SQL file. I have just done this. You're teaching me new tricks.Thanks! I had used TextEdit to open the original CSV and manually edit out the line that I used OpenRefine to remove, bypassing OpenRefine. That got me where I need to be while awaiting a workaround. In later iterations I was dealing with Sequel Ace telling me the import was too long. I ending trying out reducing the field sizes. Very messy. Here's what I saw in the SQL file. You can see there's an underscore character added before the A VARCHAR(255) NULL, CREATE TABLE 20240123IDENTIFIANTS\_5\_copy\_csv ( \_A VARCHAR(255) NULL, NNNNNNNN INT NULL, NAME1 VARCHAR(255) NULL, STATUS VARCHAR(255) NULL, F VARCHAR(255) NULL, MMMMMMMM INT NULL, NAME1 VARCHAR(255) NULL, CCCCCCCC INT NULL, NAME2 VARCHAR(255) NULL, STARTING\_MANAGEMENT DATE NULL, ENDING\_MANAGEMENT DATE NULL, REGISTRATION\_DATE DATE NULL, CLOSING\_DATE NULL ); INS On the part of your answer discussing the special characters that appear when I bring the CSV file into OpenRefine, thanks for that. I see that the Windows1252 character coding is pre-selected, and I didn't know enough about changing it. Thanks for the instructions for a workaround. I had read online that Window 1252 is a choice made when you don't know where the file will be opened, or something like that. I will give your suggestion a whirl. Have a great weekend! ---
**Author:** ![thadguidry](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/thadguidry/32/653_2.png) [@thadguidry](https://forum.openrefine.org/u/thadguidry)\ **Post date:** [May 9, 2024, 12:14am UTC](https://forum.openrefine.org/t/trouble-with-sql-export/1485/10 "2024-05-09T00:14:17Z")
The issue has been found. It appears this is an encoding regression of some kind in OpenRefine 3.8 which does not happen with 3.7. I have opened a new critical issue for it here: > > > In OpenRefine 3.8.0, extra NUL chars are being introduced between letters in a d…ataset, where it is not happening with previous version 3.7.9 > > \### To Reproduce > Steps to reproduce the behavior: > 1. First, download dataset zip from below > 2. Open using OpenRefine 3.8.0 > 3. Notice NUL chars between letters > 4. Now open using OpenRefine 3.7.9 > 5. Notice no NUL chars between letters > > \### Current Results > NUL chars are being introduced erroneously between letters. Appears to be some kind of errant decoding/encoding issue? > > \### Expected Behavior > Should work as it did before with OpenRefine 3.7.9 , without the extra NUL chars being introduced. > > \### Screenshots > > With OpenRefine 3.8.0 > !\[image\](https://github.com/OpenRefine/OpenRefine/assets/986438/1408d4db-a892-487a-b3b6-398e6b3f4414) > With OpenRefine 3.7.9 > !\[image\](https://github.com/OpenRefine/OpenRefine/assets/986438/e09c41ba-434a-497b-abd4-b4ad021cbd22) > > \### Versions > - Operating System: Mac OS and Windows 11 > - Browser Version: Edge and Safari > - JRE or JDK Version: embedded > - OpenRefine: 3.8.0 > > \### Datasets > \