mysql importing data locally basics

Question

currently learning mysql and hit a wall when it comes to importing data locally. I think I'm missing something. The data I want to import is from a notepad, I'm going to guess it wont be possible from a notepad? if anyone could give me some brief basic information on importing locally that would be great just some common rules. cheers, jon

Answer 1

MySQL can import bulk data locally from a comma-separated or tab-separated value text file (CSV or TSV file). Many people create and edit these files with the Notepad text editor program (or the equivalent on other platforms like Mac or *nix). You can also get your favorite spreadsheet program to export and import CSV files.

Then you can use MySQL's LOAD DATA INFILE command to slurp up your file and put it into a table. It's wicked fast (in New England slang).

But LOAD DATA INFILE is honestly a hassle to troubleshoot. CSV and TSV files are a cranky format to work with, especially if you're working on a multiplatform environment.

1. Windows lines end with <CR><LF>. Mac lines end with <CR>, and Linux / FreeBSD / Unix lines end with <LF>. The Notepad++ program in Windows has ways to inspect line endings.

2. CSV is a surprisingly complex format. What if the content of some columns has commas in it? That has to work properly, so some column values can be surrounded in double quotes. But what if some content has double quotes in it? You get the picture. It's messy. A good way to troubleshoot a suspect CSV file is to import it into your favorite spreadsheet program and eyeball it.

3. MySQL can require special security settings for LOAD DATA INFILE to work. Read this: http://dev.mysql.com/doc/refman/5.7/en/load-data-local.html It's possible a hosting vendor doesn't allow it.