Mark Brown's Blog

I'm working on web enabling my inventory of IT Equipment with ColdFusion and MySql. The database is an Access database that is pretty simple, just name, model number, date of purchase, blah, blah, blah.

The first thing I've had to do to get this accomplished is to export my Access database as a tab delimited text file. In Access, under the File menu, I went to Export. That opened the Export Wizard. I gave the file a name "inv.tab", then on the first screen of that Wizard I choose delimited, on the second screen of the wizard choose "Tab", then set the "Text Qualifier" option to "None". Set a location for the exported file to reside, then clicked the Finish button. Upon confirmation of the successful export, I opened the text file I created with Access with a text editor (gEdit in Linux, Notepad in Windows) and looked at the file to make sure that all the columns and data looked ok. I've got 3 date columns in the database, and noticed that behind each date was a time and that the date format was wrong and wouldn't work in MySql. (We'll tackle the date problem in another post as I haven't figure out how to convert them all to the proper date format for MySql without touching each date by hand) Anyway, I did a find and replace and replaced all the times in the file with a space. This application I only need the dates. I verified that all the times were gone, then saved the file.

Then I connected to MySql via the command line client and create my database and created my tables to include all the columns to match the columns in my Access database. On the three date fields, I set them up with data types of VARCHAR(20). For now as I'm just wanting to get the data in the database. I also made all the columns to allow NULL's except the Primary Key. I'm working with some people on the MySql newbie forum to get help on converting the dates to the correct format. There is a MySql function STR_TO_DATE that will convert strings to dates in the correct format. I'm hoping that will help me convert all the dates at once.

Once the table was created, and the export files was checked, I started my first import by using the LOAD command in MySql.

I copied one record from the export file (inv.tab) into a new file (test.tab) and ran a test on it to make sure it would import the file in the correct columns.

Then, in the MySQL Command line client, I imported my test file (test.tab) with this command

mysql>LOAD DATA LOCAL INFILE '/home/mbrown/test.tab' INTO TABLE misequip;

Much to my amazement it ran without error.

so I did a mysql>SELECT * FROM misequip;

Sure enough, there was my data. Cool. So, now I tried the big file.

I opened the inv.tab export file, made sure the line I already imported was gone, then type this:

mysql>LOAD DATA LOCAL INFILE '/home/mbrown/inv.tab' INTO TABLE misequip;

No errors again. Cool!

Then I did a mysql>SELECT * from misequip; and BOOM, there's all my data! SWEEETTTT! I love MySql! LOL

Now I'm going to tackle the date convert thing. I'll keep you posted.