Learning Mysql
Loading Data from Comma-Delimited Files
Download 4.24 Mb. View original pdf
|
- Navigate this page:
- Loading Data from Comma-Delimited Files | 281
| Loading Data from Comma-Delimited Files Databases are sometimes an afterthought. In fact, a staggeringly large amount of time spent by IT professionals is devoted to reformatting data from one application to suit another. It’s very common, for example, to store data using a spreadsheet program such as Microsoft Excel or OpenOffice Calc, only to realize later—when you’re swamped with data—that a relational database would have been abetter choice. Most spreadsheet programs allow you to export data as rows of comma-separated values (CSV), Loading Data from Comma-Delimited Files | 281 often also referred to as comma-delimited format (CDF). You can then import the data with a little effort into MySQL. If you need to import large numbers of spreadsheet files, you could use the xls2csv script (http://search.cpan.org/ ken/xls2csv) to automate the conversion from the Excel spreadsheet files to text files of comma-separated values. If you’re not using a spreadsheet program, you can still often use tools such as sed and awk to convert text data into a CSV format suitable for import by MySQL. This section shows you the basics of how to import CSV data into MySQL. Let’s work through an example. We have a list of Australian academics with their university affiliation that we want to store in a database. At present, it’s stored in a spreadsheet workbook file named academics.xls and has the format shown in Figure. You can see that the surname is stored in the first column, one or more given names and initials in the second column, and their affiliation in the third column. This example is formulated from a file that is publicly available at http://www.cs.jcu.edu.au/ acsadb/nameonly_db.html, and the workbook format example is available from the book’s web site. Saving the academics.xls file as values with a comma or other character as a delimiter is easy inmost spreadsheet programs. Inmost versions of Microsoft Excel, you click on the File menu, then select Save As, and then choose “CSV (Comma delimited for Figure 8-1. List of Australian academics stored in a spreadsheet file Download 4.24 Mb. Share with your friends:
|