Importing data from Excel or a CSV file

- Often, you have data stored in another format, such as a spreadsheet, so it's inconvenient to have to retype all the details manually into a database table. In the Exercise Files for this video, there's a Microsoft Excel spreadsheet that contains the names of car manufacturers that I want to use in the makes table. I've got the exercise files here on my desktop, so let's open that. And it's the chapter four folder that we need. And then 04_01. In there is a file called makes.xlsx, that's an Excel file. PhpMyAdmin isn't capable of importing an Excel file directly, but that's not a problem, because it is capable of importing from a CSV file. CSV stands for Comma Separated Values, and it's a common format for exporting data from a spreadsheet. Now, it doesn't matter if you don't have access to Excel, because this folder also contains makes.csv, that's the CSV file that I'll be exporting from Excel. But let's take a look at the spreadsheet first. I'm going to open it in Excel. And the first row contains "make," that's the name of the column. And underneath that are the names of all the car manufacturers. It doesn't matter that they're not in alphabetical order; even if I were to resort them, adding new makes later will result in the new makes being added at the end of the list. So let's save this as a CSV file. Go to the File menu, select Save As, I'm going to save it in the current folder, and Save as type, we need to change that from Excel Workbook, by opening this dropdown menu, and selecting CSV (Comma delimited). There are a couple of other CSVs in here, CSV (Macintosh) and CSV (MS-DOS). It's not those that you want, it's CSV (Comma delimited). Select that, and Excel automatically changes the file name extention to .csv. So all we need to do is Save. It says that it already exists, do I want to replace it? The answer is Yes. And then I get this warning from Microsoft Excel, it says "Some features in your workbook might be lost "if you save it as CSV (Comma delimited). "Do you want to keep using that format?" Yes I do, so I just click Yes. And that's it, that's all you need to do. To close this, I'm going to click that x up there, and then I get this warning from Microsoft Excel. "Want to save your changes to makes.csv?" Now, you might be tempted to click Save. The aswer is click Don't Save. The reason is that if you click Save, Excel will try to create a workbook again. Clicking Don't Save means that no changes are made to the CSV file that we saved a moment ago. So there is that CSV file, it's now been exported from Excel. So we need to go to phpMyAdmin, and work with the database, it's up_and_running that we're working with. And in the previous chapter, we created a makes table, so select that, and then select the Import tab. Under File to Import, click Choose File. And we need to go to the Exercise Files, chapter four, 04_01, and select makes.csv, and then click Open. Before we can import the data from the CSV file, there are several settings that we need to check. The first one is this one here, "Skip this number of queries (for SQL) "or lines (for other formats)." Well, we're importing from a different format, that CSV. So its lines, starting from the first one, we need to skip that first line that has got the column name there, "make." So we need to change this from zero to one. Under Format, phpMyAdmin has automatically selected CSV. But if we open that dropdown menu, there's also an option for CSV using LOAD DATA. The difference between CSV and CSV using LOAD DATA, is that if you choose using LOAD DATA, the CSV file must be on the same computer as the MySQL server. Now, I'm working locally at the moment, so they're both on the same computer. But if you're uploading to a remote server, and the CSV file is on your local computer, using LOAD DATA won't work. Even if you upload it to your remote computer, the user has to have FILE privilege, and FILE privilege isn't always enabled. So, if you select CSV, that's the default that is selected by phpMyAdmin, phpMyAdmin takes care of everything. Doesn't matter which computer it's on, and it doesn't matter if you've got that FILE privilege. So let's just scroll down and look at the format-specific options. The first option here, is "Update data when duplicate "keys are found on import." So, this means it's going to add ON DUPLICATE KEY UPDATE to the SQL that imports the data. We don't have any data in our table at the moment, so that's irrelevent. The next thing that you need to check with a CSV file, are the way that the columns are separated. CSV normally stands for Comma Separated Values, but it can also stand for Character Separated Values. So instead of using commas, the columns might be separated with tabs. Now, it's very difficult to see in here, but phpMyAdmin has selected a comma for CSV, so that is correct in our case. But let's say you had tabs, if you've got tabs, it's \t that you need to enter in here. Then the character that the columns are enclosed with, by default it's chosen double quotes, that's fine. "Columns escaped with," double quotes. Sometimes that needs to be a backslash. In our case, nothing is going to be escaped so it doesn't really matter. And the last one, "Lines terminated with," it selects "auto" and that's usually the best choice. This leaves it up to phpMyAdmin to work out what type of line endings you've got, whether it's Windows line endings, Mac OS X line endings, or Linux line endings. And this last one, "Column names," this is the really interesting part of importing from CSV. If we just hover over here, we get this tool tip, and it says "If the data in each row of the file "is not in the same order as in the database, "list the corresponding column names here. "Column names must be separated by commas, "and not enclosed in quotations." So, this is absolutely quite fascinating, because you don't have to have your columns in the same order, you don't even need to have the same number of columns. We've only got one column in our CSV file, and that's make. So we need to add make in here. We don't need this "Do not abort on INSERT error." So all we need to do now is click Go. And it tells us the import has been successful. So if we click the Browse button, we can see that all of the values from that CSV file have now been imported into the makes table. Everything has gone from the make column into make. And make_id, because that was set as an auto increment column, the primary keys have been automatically generated by phpMyAdmin. So, that's importing using a CSV file. Saving a spreadsheet as a CSV file is convenient way of importing data into a table. The columns in the CSV file don't need to be in the same order as the database table. As long as you list the order, the value should be inserted in the column names field. You can have fewer columns in the CSV file than in the table, but not more than the table has.