Difference between revisions of "Technical How To's"

Revision as of 08:51, 15 June 2017

directly transferring tables between a Microsoft Office Access Database and a MySQL Database

see instructions here. This solution sets up an ODBC DSN (data source name) using the ODBC Data Source Administrator (control panel --> administrative tools --> ODBC Data Sources (32-bit)) You must have previously installed the MySQL ODBC drivers on the Windows machine running Access. The drivers are available from MySQL/Oracle here. Make sure to download & install the 32 bit drivers and use the 32 bit version of the ODBC Data Sources when setting up the driver and the ODBC DSN.

Using the above process is easier than exporting to flat files and loading them, as the ODBC transfer automatically creates the table for you.

importing delimited text files into MySQL via cmd line

We were able to export the data from the Comic Base MS Access database into UTF8 pipe delimited text files. We now want to load that data into the MySQL database so we can easily query the Comic Base data and examine its structure. In these exported files, the first line contains the column names. All fields are quoted. Values are pipe delimited.

When exporting the data out of Acess, we need to make sure that we specify the Character Set as: UTF8

instructions

~% mysql -u root -p comic_base

-- create the table to be loaded based on the columns listed in the first, make sure to create the db with a charset of utf8. mysql> DROP TABLE publishers; mysql> CREATE TABLE publishers ( publisher_name VARCHAR(50) NOT NULL, default_copy_right VARCHAR(255), web_page VARCHAR(255) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- use LOAD DATA INFILE command. make sure to specifiy the input file encoding as utf8 mysql> LOAD DATA LOCAL INFILE '/Users/gregskluzacek/Desktop/ComicBase_Exports/Publishers.txt' INTO TABLE publishers CHARACTER SET utf8 COLUMNS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

Load Data Infile Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

   [REPLACE | IGNORE]
   INTO TABLE tbl_name
   [PARTITION (partition_name,...)]
   [CHARACTER SET charset_name]
   [{FIELDS | COLUMNS}
       [TERMINATED BY 'string']
       [[OPTIONALLY] ENCLOSED BY 'char']
       [ESCAPED BY 'char']
   ]
   [LINES
       [STARTING BY 'string']
       [TERMINATED BY 'string']
   ]
   [IGNORE number {LINES | ROWS}]
   [(col_name_or_user_var,...)]
   [SET col_name = expr,...]

examples

-- use default delimiter of TAB and default line ending of ? mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

-- change the line ending for Windows files mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';