How do I Export & Import a CSV File to an Excel Table?

Importing a CSV File to an Excel Table

LOAD DATA INFILE is mostly used to import CSV data into MySQL. This statement efficiently loads file data into a table.

Syntax and Essentials Basic syntax includes file path and target table:

LOAD DATA INFILE 'path_of_the_file/file_name.csv' INTO TABLE employee;

Note that LOAD DATA assumes tabs and linefeeds for values and lines. Note that CSV files require FIELDS and LINES clauses to specify format.

CSV Parsing Options-FIELDS Try this to parse CSV files, which utilize commas as field terminators and double quotes for values:

Termination of Line (LINES Clause) Depending on the operating system, CSV files may have different line ends. The LINES TERMINATED BY clause should be used to specify this:

• LINES TERMINATED BY ‘\n’: For Unix/Linux-style newlines.
• LINES TERMINATED BY ‘\r\n’: For Windows-style carriage return and newline.
• LINES TERMINATED BY ‘\r’: For Apple macOS (older versions).

Example of CSV Import While observing quoting and escaping restrictions and missing a header row, the command would import file.csv into my_table:

LOAD DATA INFILE '/tmp/file.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES; -- To skip the header row if present

Location of File (LOCAL Keyword) Where MySQL looks for the data file is determined by the LOCAL keyword.

• If LOCAL is present (LOAD DATA LOCAL INFILE), MySQL looks for the file on the client machine from which the command is issued.
• If LOCAL is omitted, MySQL looks for the file on the server host using the absolute pathname612. The output file is created directly by the MySQL server.

Giving Column Specifics You can specify columns in a list following the table name if the order of the columns in your CSV file is different from the table structure or if you simply want to load particular columns:

LOAD DATA INFILE 'file.csv' INTO TABLE employee
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n'
(id,name,sex,designation,@dob) -- @dob signifies a user variable for transformation
SET date = STR_TO_DATE(@dob, '%d-%b-%Y'); -- Example of transforming a date format

This example also shows how to change data during import by using user variables.

Taking Care of Extra Keys If the table contains a primary key or unique keys, duplicates may appear while importing data into a database that already contains records. It is within your power to regulate this behavior:

• REPLACE: If a duplicate unique or primary key is found, the new row replaces the old one.
• IGNORE: Existing rows with duplicate keys are preserved, and new ones are ignored.

Utilizing the mysqlimport utility Mysqlimport is a command-line tool that loads input files straight from the command line by wrapping the LOAD DATA statement. The command-line parameters it offers match the FIELDS and LINES clauses.

Exporting a Table to a CSV File

The SELECT… INTO OUTFILE statement is frequently used to export data from a MySQL table to a CSV file. This command exports a query result straight to the server host’s file.

Grammar and Fundamental Use The standard syntax adds the filename INTO OUTFILE to a standard SELECT statement:

SELECT * FROM my_table INTO OUTFILE '/tmp/output.csv';

Options for CSV Formatting (Fields and Lines Clauses) Like with import, you must use the FIELDS and LINES clauses to specifically define the CSV format for the output file:

• FIELDS TERMINATED BY ‘,’: Specifies that columns in the output file should be separated by commas.
• ENCLOSED BY ‘”‘: Encloses each field value in double quotes.
• LINES TERMINATED BY ‘\n’: Specifies the line ending character(s) for the output file.

A CSV file export example The following would be used to export the tutorials_tbl table in CSV format with CRLF (Carriage Return Line Feed) terminated lines:

SELECT * FROM tutorials_tbl
INTO OUTFILE '/tmp/tutorials.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Location of Output File Given that the MySQL server on the server host creates the output file directly, the filename in INTO OUTFILE should provide the intended location on the server’s file system.

Making Use of the Mysqldump Utility The mysqldump tool is used to copy or backup tables and databases, and it can write table output as a raw data file or as a collection of INSERT statements. In contrast, SELECT… INTO OUTFILE is suitable for exporting query results to a custom-formatted file, such as CSV. To select the directory where MySQL should write the raw data file, use the –tab option when using mysqldump. Without parameters, this dumps tab-delimited files, not CSV.

PHPMyAdmin exporting The MySQL web-based graphical interface phpMyAdmin makes data export easy. The “Export” tab lets users choose SQL, CSV, and PDF file formats. You can export a single table instead of the database using this interface.

To sum up, SELECT… INTO OUTFILE and LOAD DATA INFILE both offer strong and adaptable methods for controlling data flow between MySQL tables and CSV files, facilitating effective data integration and manipulation.