Tag Archives: csv

MySQL: Quickly Load a CSV file into a Database Table Tips, Tricks and Tutorials 17 FEB 2014

20090107_MySQL_Logo2CSV (comma separated value) files are often used as a way to transfer data between two different systems – it’s a simple, verbose, and difficult to break (if you double quote every single string of course) way of saving grouped data into a flat text file.

Pleasingly, MySQL comes with a command line tool that makes it a snap to load a CSV file directly into a database table, namely mysqlimport – a utility described at best as a client that provides a command-line interface to the handy LOAD DATA INFILE SQL statement.

Generally you invoke mysqlimport pretty much like this:

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file’s contents. For example, files named ‘patient.csv’, ‘patient.txt’, and ‘patient’ all would be imported into a table named patient.

In addition to this basic call, you general tweak things via various settable options, for example specifying the user account and password to make use of, how the tool should break up values and lines, etc.

It is important to note that this is a relatively inflexible tool and as such requires good data inputs, and a pretty much near match in terms of available table columns versus the comma separated values.

This utility is a great way to automate data imports via the cron (or scheduled task handler), with my bash files often executing lines that look an awfully lot like this:

mysqlimport -u Myusername -pMypassword  --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --local Mydatabase datatoimport.csv

In other words, mysqlimport is a great way of grabbing CSV data and dumping it into your database fast!

Related Link: mysqlimport

PHP: How to Force a CSV File Download CodeUnit 14 SEP 2011

CSV files are handy ways of handing over long lists of data like transactions, audits, surveys or logs over to a user, particularly because they are so universally supported by just about all spreadsheet applications.

Normally when you want a user to download a file you simply force the browser to point to the location of the file you want them to download, however as we all know, this doesn’t always work as often the browser will decide what to do with the file – be it to display the file contents, redirect you to a particular browser plugin, or give you the choice as to whether you want to download the file or perhaps do something else with it.

Because CSV files are nothing more than plain text files, browsers often choose simply to open them up as text, pretty useless in that if forces the user to now look for the “save as” option in the browser’s menu system.

Luckily, the way to get around this and force a download is pretty common to most file types – all you do is set the necessary header information and then read the target file’s contents and push it straight to the browser via the output buffer.

So let us see this in action for a CSV file then:

ob_start();
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=downloadrecords.csv");
readfile($_SERVER['DOCUMENT_ROOT'] . '/downloadrecords.csv');
ob_end_flush();

In the above example we want the user to download /downloadrecords.csv. Now instead of directing a user directly to that path, we instead send them to a force download script, with sets the necessary headers like the all important content-disposition header, and then simply read the existing CSV file contents straight into the output buffer using the handy readfile function.

Flush the buffer and the browser will now do the rest.

Nifty.