In this article, we created a database in MySQL, as well as a sample table called systemsdoc. This article is for you screwheads that still document your systems with Excel spreadsheets. Eventually, we will show you how to run reports and update the database via customized web pages. First, though, we need to get the data from the nasty spreadsheet into the database. We created a sample Excel spreadsheet, and saved the spreadsheet as tab delimitted text: sysdoc.xls sysdoc.txt. Let’s load the file!
u-1@srv-1 mysqlart $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 to server version: 4.0.14-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> connect sysops Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Connection id: 14 Current database: sysops mysql>LOAD DATA INFILE '/share/sysdoc.txt' INTO TABLE systemsdoc -> LINES TERMINATED BY '\r\n'; Query OK, 7 rows affected (0.00 sec) Records: 7 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select manu, model, sernum, sysversion from systemsdoc; +------------+----------------+-------------+--------------+ | manu | model | sernum | sysversion | +------------+----------------+-------------+--------------+ | ibm | x342 | 2224522232 | NT 4.0 | | ibm | x345 | 2324442424 | Windows 2000 | | ibm | x335 | 232323werj2 | Windows 2000 | | dell | poweredge | 333sdf | Red Hat 7.3 | | comap | ml530 | qwerty1 | Windows 2003 | | supermicro | fabulous | 333 | NT 3.51 | | beige box | franky special | 45sdf | OS/2 4.0 | +------------+----------------+-------------+--------------+ 7 rows in set (0.00 sec) mysql> |
You need to use the LINES TERMINATED BY ‘\r\n’ dealie if you are using a text file created on a Windows/DOS system because of the way these systems terminate the lines. If you need to massage the data a bit to deal with commas and other freakiness, see this article.
