Jump to content


Photo

Cannot import into MySQL


  • Please log in to reply
6 replies to this topic

#1 xgab

xgab
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 September 2003 - 01:39 AM

Hi!
This is my first experience importing comma, pipe or tabs delimited plain files into a MySQL Database.

I tried using more than one tool and none of them worked...

The thing is that one of the plain files has 250,000 records.

- I tried with a nice freeware script called CSV Importer. It enabled me to import succesfully one plain file of 6,000 records, but the second one has 24,000 and it uploaded only 8,000 (so the largest one will be imposible). Does anyone ever used CSV Importer ? (downloaded from hotscripts.com)

- Then I tried with the latest PHPMyAdmin system. It is very clear how to export information, but I could not figured out how to import!. There is an import link at the little SQL window that asks me for a txt file, but it is not clear how to format and process it.

- I also tried with a Windows freeware to create the .sql file from Access. It created a huge file with many INSERTs. How should I process that big file, thru the little PHPmyAdmin SQL window ???

- Finally I tried with LOAD FROM and mysqlimport, but... unfortunately I get many errors and warnings and the help information at the mysql site is not enough for me... I a newbie :-(

Which is the best option or technique to import large amounts of records ?

The server I use is Linux Mandrake with PHP 4.3.3, the latest MySQL and also my PC is Windows XP.

I will apreciate you help. Than you!

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 27 September 2003 - 02:54 AM

i am only familiar with importing via the LOAD command, or by using a redirect at the command line. have you tried that? i think this is correct:

./mysql databasename < file.sql

what kind of errors and warnings do you encounter?
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 27 September 2003 - 06:42 AM

Have you tried the sqlyog tool www.sqlyog.com

It helps you in imoprting data easily GUI based

This is the command to import csv file

LOAD DATA INFILE <Filename with path> INTO TABLE <table_name> FIELDS TERMINATED BY \',\' LINES TERMINATED BY \'n\'

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#4 xgab

xgab
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 September 2003 - 02:45 PM

I already considered sqlyog, but... unfortunately is not freeware...

#5 Blu_Smurf

Blu_Smurf
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts
  • LocationTexas

Posted 27 September 2003 - 03:30 PM

they have several gui\'s for sql on mysql.com... try one of them

#6 pauper_i

pauper_i
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts
  • LocationPhoenix, AZ, USA

Posted 27 September 2003 - 03:33 PM

Go with the big \'inserts\' file and the little SQL window!
It isn\'t the size of the file, its the length of the inserts statement that matters - if it will handle one, you can batch process the rest.

Better yet, write a php script to handle it all. Whichever way you choose, its going to be time consuming importing it all, in terms of machine cycles. Might as well bite the bullet and kick it off, it may well be done before you can find a faster method, and I\'m assuming that this is something you\'ll only have to do the once!

Oh yes, and check your backup policies too, make SURE you only have to do it once! *grin*

Good luck with it!

D
Genius is one percent inspiration, and ninety-nine percent perspiration. [br]Thomas Alva Edison

#7 xgab

xgab
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 30 September 2003 - 03:02 PM

I have found a freeware version of SQLyog !

The current published on-line version 3.2 is paid... but version 3.1 is still freeware!


Thank you all!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users