mySQL - How to dump & upload text files

Hi,
Oracle uses SQLoader, SQL server uses BCP to load text files with millions of records. What technology mySQL have if I need to load around 10 million records every single day ?
crazywolf2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomarseneaultTAC-CX EngineerCommented:
l'm not a MYSQL expert but in my last job we did this by "mysql -u <user_name> -p <password> <database> < file_to_load.sql". Note that the file_to_load.sql was in sql format.
0
crazywolf2010Author Commented:
Hi,
I don't have insert statements here.

I have tonnes of 3rd party text files in varying sizes (KB to GB)  in comma, pipe delimited format.

Thanks
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
In mysql you can use the LOAD DATA INFILE statement. There is also a command line version for this
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

arnoldCommented:
Mysqldump is the backup utility.

To load/restore the database/data
You would cat the backup file into mysql -u root databasename


With a backup, you can also set the inclusion of use statements.
0
crazywolf2010Author Commented:
Hi,
Can LOAD DATA INFILE  manage files in size of GB? Does anyone have a good example of how it should be used and any best practises.

Effectively I need to have details of rejected records while loading and any error trapping done automatically will be very useful.

Thanks
0
arnoldCommented:
Lad data in file deals with CSV, TSV I.e. exported data only.

Load data in file "filename "
Into table
Defines what the separator, terminator and the columns of the table.
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Your best approach might be to use a perl or similar script that will go through the file and perform tests while inserting the data into mysql.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.