filePro to MySQL

[Tim Fischer]

But wouldn't this recreate the entire table each and every time?  We're 
using files with over 400,000 records in it, so it wouldn't really work 
too well.  The way we do it updates individual rows in the MySQL table - 
could this be altered to do the same?  (It looks like it could be.)

Tim Fischer




Lerebours, Jose wrote:

>Simple, simply use a script with following
>
>#LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
>#    [REPLACE | IGNORE]
>#    INTO TABLE tbl_name
>#    [FIELDS
>#        [TERMINATED BY '\t']
>#        [[OPTIONALLY] ENCLOSED BY '']
>#        [ESCAPED BY '\\' ]
>#    ]
>#    [LINES
>#        [STARTING BY '']
>#        [TERMINATED BY '\n']
>#    ]
>#    [IGNORE number LINES]
>#    [(col_name,...)]
>#
># For more information on this command, see
># http://dev.mysql.com/doc/mysql/en/load-data.html
>#
>
>This is a working sample:
>
>if [ $# -eq 3 ]
>then
>
>mysql -u filepro -pfilePro -e "LOAD DATA INFILE '$3'
>        REPLACE
>        INTO TABLE $1.$2
>        FIELDS TERMINATED BY '~'
>        ESCAPED BY '\\\\'
>        LINES TERMINATED BY '\n';"
>
>else
>
>echo "
>
>Usage:  putMySQL "database" "table_to_write_to" "data_source"
>
>
>"
>fi
>
>
>If you visit the site listed above, you can learn more about
>this and many other commands.
>
>So, as you can see, writing to MySQL from filePro is a 1,2,3
>sort of thing.  All you need to do is produce the file with
>data to be loaded and execute a script similar to the one
>above.  Of course, this script is in its simplest form.  I
>suggest you read through the above listed URL pages and see
>what other things this practice has to offer.
>
>As far as updating filePro from MySQL, it really is subject
>to what tools you use.  PHP, CFML, CGI, ASP, ... all have
>the means to create text files and automatically transfer
>these files to a repository where your 'cron' can pick them
>up and import them to your filePro files.  
>
>
>And if you want, you can even create MySQL tables directly
>from filePro using:
>
>mysql -u filepro -pfilePro -D MyfpDataBase -e "CREATE TABLE \`MyfpTable\` (
>\`field_01\` VARCHAR( 10 ) ,
>\`field_02\` DECIMAL( 9, 2 ) DEFAULT '0.00' NOT NULL ,
>\`field_03\` VARCHAR( 10 ) NOT NULL ,
>\`field_04\` VARCHAR( 10 ) NOT NULL ,
>\`field_05\` VARCHAR( 10 ) ,
>PRIMARY KEY ( \`field_03\` ) ,
>INDEX ( \`field_04\` ) ,
>UNIQUE (
>\`field_01\`
>)
>) COMMENT = 'Created from filePro';"
>
>
>So, given these two MySQL commands and simple scripts, you 
>should now have enough to play around and see how easy it
>really is to create MySQL tables matching every one of your
>filePro files and keep these tables nicely fat with your
>filePro data as well.
>
>
>Regards;
>
>
>--
>Jose Lerebours
>Principal Software Engineer
>Eagle Global Logistics
>1801 NW 82nd Avenue
>Miami, FL 33126
>Voice: (305) 594-0038 Ext 7556
>Fax #: (305) 593-0431
>eMail: Jose.Lerebours at eaglegl.com
>http://www.eaglegl.com
>_______________________________________________
>Filepro-list mailing list
>Filepro-list at lists.celestial.com
>http://mailman.celestial.com/mailman/listinfo/filepro-list
>
>
>  
>