[EMAIL PROTECTED] wrote on 09/02/2005 08:46:59 AM: > Hi, > > i have a file which i need to import in about 7 tables. For this reason > i need toimport only parts of this file into each of the tables. > > Is there a way to do this with LOAD DATA INFILE or what would be the > preferred way with just having mysql standard tool at hand?? > > Cheers > > Thorsten > > -- > http://www.fastmail.fm - IMAP accessible web-mail > > Here is how I have approached this problem in the past. I create a table that matches the organization of the data in the raw text file you currently have. I bulk import the whole file into this table.
Now, I take care of as many data scrubbing tasks as I can. I look for invalid records, incomplete records, duplicates (if they are not allowed) and anything else that is just "junk" and strip it out of the RawImport table. That way, as I write my queries to copy rows from RawImport into my "production" tables I know I shouldn't be filling up my good tables with bad data. If the raw data is heirarchical in nature, I will usually add an auto increment column to the RawData table (sometimes the data doesn't need it). This comes in VERY handy when trying to re-link child records back to their parents as you add them to the production tables. I may also add a RawID field to my production tables. That way when I add a record from my raw data into my production table, I have an easy way to identify the new parent id value in an existing child-parent relationship. Also, fill in your production tables from the top of the heirarchy on down. Don't start at the bottom and try to work your way up. One other thing you may consider doing is to disable keys and foreign keys on your production tables, at least until you stabilize your new production data set. I try to save my queries into a SQL script as I build each step. That way, if I realize I screwed something up, I can fix the script, reimport the raw data, then re-run the script (hopefully producing a proper data merge this time). Take a backup of your production table just before you start merging in the raw data. That way you can restore to a "last known good state" if things turn pear shaped. Best of luck! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
