Tables from phpMyAdmin into Access and the reverse

select table and click Export
select Custom
select CSV or CSV for Excel
make sure the following are selected:
- character set is UTF-8
- print column names in 1st row
- field delitmiter (columns separated with) is Semicolon and
- Go
you get a file with CSV extension

Go to External Data
select Text File
select the 1st option: Import the source data into a new table in this database and
browse, find and select the table (file)

and

select Delimited (fig. 1) and next > and
(fig. 2) Delimiter that separates your fields: comma (,)
Text Qualifier: double quotes (“)
1st row contains field names
and go to Advanced… where you set
(fig. 3) Language All
Code page Utf-8
change decimal symbol into something else than comma, e.g. period (.) and next > and

and while still in Advanced… you tweak each field and especially you will have to declare

large fields into memo (e.g. post content), because axs cannot have more than 255 characters into a text field and
declare integers the fields that contain IDs
you may also have to tweak other fields (e.g. date delimiters etc) although mostly not and next >
don’t let Accesss set its own primary key (fig. 4). Select instead the one used by WordPress or leave no primary key and set it later and next > and finally
enter the name of the table and next >

possible import errors:

field truncation: one (or more) fields have contents that are longer than e.g. txt, have to be declared as memo
empty indexed field: remove the index. You can clean up the table later on and reinstate the index
it can happen that Access attempts to import into an integer field a number that goes beyond 32674. That requires a long integer. Better import the field as text w-out index and then convert it to long integer. It happened to _mausermeta
for MS-Access date is a reserved word. So when you try to import a field with that name it gets confused and produces an error

At this point you can set up the relationships in MS-Access and make any changes in the fields contents as per your requirements.

it is also straightforward to import into Calc but if you have a lot of records (lines) it will be hard to handle

create new_XYZ_ID fields and populate with the new numbers (you check the target MySQL for the next available numbers)
update all key fields
and make all other desirable updates, including: in _posts update also the _post_parent for images and pages (but not for posts)
then you a. break the existing relationships, b. create new ones, c. delete the old keys, and d. create again the relationships (for verification) and finally
export the tables with CSV format

select the table with right-click
select text file
browse and give a name (if necessary)
set delimited, comma, text qualifier (“) and leave 1st row contains … unchecked (because you will import into an existing table into MySQL)
give it a name and folder (if other than default)

but I took everything through Libre Office because Access would always produce errors:

I simply copy all records from Access into a Calc file,
delete the header row,
save as CSV with the name of the MySQL table that you are going to import to,
using all default settings (Unicode, field del…=(,), string del…=(“), save as shown), but
be careful: if you have records with fields containing more than 4000 characters (i.e. post_content in posts) during this copy they will be truncated. Must then paste manually into the field in phpMyAdmin.

Basically use all default settings:

choose file
character set should be Utf-8
choose format CSV
check all Format-specific Options but normally you shouldn’t need to change anything there
ONLY if you want records to be updated select ‘Update Data when …’ and
Go in this sequence: _posts, _comments, _postmeta, _terma_taxonomy, _terma_relationships, _terms

as a final step you (may) need to replace all pages in the old site with redirects that point to the new site

BrixFax.NET