0

I'm trying to create a dump file from an MSAccess database to import in to a MySQL database. I can create the dump file, but when I try to import it I get this error Incorrect string value: '\xA325- R...' for column...

I'm not really sure what it means or how to fix it. I know the dump partially works as other tables and data get imported. The import gets to this error and then stops

I've tried setting this DEFAULT CHARACTER SET = utf8; on the table I've also tried this on the column CHARACTER SET utf8 COLLATE utf8_unicode_ci

Here's an example from my dump file

CREATE TABLE Agent_Table(
    AgentID INT NOT NULL AUTO_INCREMENT ,
    Agent VARCHAR(255)  ,
    Archive VARCHAR(255)    ,
    AgentEmail VARCHAR(255) ,
    AgentMobile VARCHAR(255)
  , PRIMARY KEY (`AgentID`)
)

DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

I've tried a number of the utf8 options as well and still no joy

Nothing seems to work.

I'm writing a dump file because I've exhausted all other methods to try and to import/export the data

All the methods/tools I have tried all fail with some form of ODBC error I'd list them here but there are so many as I have been at this all day.

If anyone knows how to fix this problem or how else I can import this data I would appreciate it

UPDATE

The import doesn't seem to like £ or ` or ’ that I have seen so far. No matter what charset I use it won't import and throws an error when it gets to that point.

djack109
  • 131
  • 1
  • 4
  • That is not a valid character in any character set. In latin1 it might be `£%`; does that seem 'right'? – Rick James Nov 20 '20 at 22:33
  • I've updated my question. If I remove these chars then everything is fine. I create an update file which I can run after and replace the chars back to their original – djack109 Nov 20 '20 at 22:37
  • Did you try latin1? – Rick James Nov 20 '20 at 22:44
  • @RickJames I think I tried most of them. Some just screwed up the import data. With that said tho I used the encoding on the database and/or table creation. Because I'm using VBA to generate the dump file the encoding options (for the dump file) are very limited to unicode or not unicode and that's it. – djack109 Nov 20 '20 at 22:47
  • Assuming that `\xA325` represents two bytes in the stream, that is _not_ valid Unicode. That is, the problem is on the VBA side. – Rick James Nov 20 '20 at 22:51
  • Yes I know. I just can't get around it, hence I find and replace any occurrence during the dump file creation – djack109 Nov 20 '20 at 22:58

0 Answers0