Working with data: Import data

  1. Create a table
  2. Working with data
  3. View data

We have two sets of sample data. The one we’re looking at right now is the albums.txt file. This file is a tab-delimited list of albums, artists, years, and date purchased.

In Sequel Pro, pull down the “File” menu, go to the “Import” submenu, and choose “CSV file…”. Because different computers have different line endings, and because different export options will use different things to separate fields, you need to tell Sequel Pro that fields are terminated by tabs (“\t”), that the fields are not enclosed by anything, and that the first line does not contain field names.

image 6

Next, you’ll need to choose which items in the file go to which field. One of the first things you’ll notice is that we have an extra field. This data contains what looks to be the format of the album: if you scroll through the data you can see values of CD, Vinyl, or Cassette. We aren’t keeping track of that data, so choose “Do not import”.

Because Sequel Pro does not let you import the same data into two different fields, you will need to also use “Do not import” temporarily for one or two of the others.

image 7

You will notice that the date is in MySQL format. Often when transferring data in bulk you will find it easier to convert date and time while exporting, into a format that the destination understands.

Click the “refresh” button in Sequel Pro to see your changes.

image 8

On the command line, you’ll need to make sure that you are in the same directory as the albums.txt file (or that it is in the same directory as you) and import it using the MySQL statement:

load data local infile "albums.txt " into table albums (album, artist, rating, year, purchasedate);

You need to list the columns in the order that they appear in the import file.

  1. Create a table
  2. Working with data
  3. View data