When working with Data between multiple Database Platforms (MS-Access, MS-SQL, MySQL, PostgreSQL, Oracle, etc), using CSV files can be a practical alternative to using a direct ODBC connection.
This example file (example_file.csv) stores a few rows of data that are to be imported tp a MySQL database.
"Sean O\'Donnell","Los Angeles","CA"
For this example, we save the contents to the following file/path:
Our Next step is to construct our SQL Query in order to correctly process our data during the import procedure.
load data local infile
this portion of the query is to declare the path of the file to import. ('/home/example_file.csv' for this example)
the into table declaration signifies the database and table to be used to import the data
fields terminated by
the fields terminated by declaration assigns the field-seperator flag, which tells mysql when the field ends. (in this case, we use a comma to sepearte fields)
the enclosed by declaration assigns the character in which we use to wrap string values in each field. (in this case, we use double-quotes)
lines terminated by
depending on the formatting of the text/csv file (unix or dos), we declare the 'nr' to seperate each line break as a row.
We then use (something like) the query below, to import the CSV file into our MySQL Database:
LOAD DATA LOCAL INFILE '/home/example_file.csv'
INTO TABLE db_example_name.tbl_example_name
FIELDS TERMINATED BY ','
ENCLOSED BY '\''
LINES TERMINATED BY '\r\n'