cancel
Showing results for 
Search instead for 
Did you mean: 

My SQLAdmin CSV Import problem

rustek
Newbie
Posts: 5
Registered: ‎11-04-2008

My SQLAdmin CSV Import problem

Hi.
I'm having a problem importing tables using MySQL Admin console.
I'm trying to import data into a table in MySQL from a csv file on my local drive.
I have tried several tables.
I have tried formatting the csv file with and without quotes.
I have tried formatting the field delimeters as commas and semi-colons.
I have even exported data from MySQL admin and tried to read it back in but it doesn't matter what i do I get the follwing error:
""No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration. See FAQ 1.16.""
The file size cannot be the problem since I have tried a csv file with a single line of text
Can anyone help?
13 REPLIES 13
Penny
Superuser
Superuser
Posts: 968
Thanks: 513
Fixes: 5
Registered: ‎05-04-2007

Re: My SQLAdmin CSV Import problem

Quote from: rustek
Hi.
I'm having a problem importing tables using MySQL Admin console.
I'm trying to import data into a table in MySQL from a csv file on my local drive.

Does it have to be a csv file?  I use MySql with txt files (output from Excel and imported via Telnet) and that works fine.  Can write down the sequence, if that helps.
Regards,
Penny.
Penny Rollo * Force 9 from 17/02/98 * PlusNet from 2000 onwards
Project HappyChild - free maths worksheets, free French-English
worksheets and 12 other languages
personal site www.pennymidasrollo.plus.com
Prod_Man
Grafter
Posts: 286
Registered: ‎04-08-2007

Re: My SQLAdmin CSV Import problem

Well,
I didn't even know MySQL even supported such poor notation as CSV for equivilant Table operations such as 'INSERT INTO' instructions.
I would personally process the CSV into SQL so that it will know exactly what tables to insert/update etc.
Again I've never seen anyone use anything different to SQL Type language with an SQL Server.
If push came to shove,
you can probably convert it to SQL locally with MSSQL and use PHP to transfer to MySQL,
and then dump the MySQL Databse inorder to create an SQL Dump.
Hope that helps!
Good luck with it
Jim,
rustek
Newbie
Posts: 5
Registered: ‎11-04-2008

Re: My SQLAdmin CSV Import problem

Penny - Thanks. I'd appreciate that.
I know the admin page says it can import csv though - and i have used it before. My data came from MSAccess.
never converted anything to SQL before, If you can get me started that would be great - thanks.

Prod_Man - yeah cheers. Similar response as above - would love an example of what Access might output as a CSV converted to SQL
Cheers
Rus
Penny
Superuser
Superuser
Posts: 968
Thanks: 513
Fixes: 5
Registered: ‎05-04-2007

Re: My SQLAdmin CSV Import problem

Quote from: rustek
Penny - Thanks. I'd appreciate that.
I know the admin page says it can import csv though - and i have used it before. My data came from MSAccess.
never converted anything to SQL before, If you can get me started that would be great - thanks.

Rus, I've used csv files previously but only when dredging up info for USA counties (and converted the csv files to something I could use more easily) so can only give steps for doing the mysql process using a txt file (though maybe csv would operate in the same way).
I'll apologise at the outset for idiot-guide-type instructions, but I don't know how much you do or don't know about the whole process, so have just included all the steps that occur to me, with explanations where these seem required.
1) First output (eg) an Excel file as a txt file (text-and-tabs option).
2) Upload the txt file to the ccgi server (into the outer directory, not inside the cgi-bin itself).
3) No need to chmod the file.
4) Using mysql admin, create the mysql db (the only boxes I fill in are 'field name' and 'type' (I use 'text' for most in the type column, 'longtext' for fields where there are more-than-255-characters to be fitted in a single cell).  The default settings for other columns seem to generate 'latin1_swedish_ci' in the collation field, and 'No' in the Null column when you get back to the overview page.
5) Bottom left of your screen, select Start - Run - telnet (type telnet in box).
6) On the black screen, type o ccgi.force9.net (or the plusnet variable of this).
7) Type in username + password when asked (max 60 seconds-ish allowed for this).
8] This should get you accessed to the ccgi server.
9) Now you need to get onto the mysql server.
Type, at the prompt, mysql -h xxxxxx (name of the server, this will be the same one you used to get into mysql admin via the browser) -u xxxxxx (your username) -pXXXXXXXXXXXXXXXX (your long mysql admin password).  Note that a space must not be left between -p and your password.
So what you'll have typed should look like
mysql -h xxxxxx -u xxxxxx -pxxxxxxxxxxxxxxxx
That should get you onto the mysql server.  Note that from this point onwards, every command line you type in this area, now needs to end in a ; (and then use the 'enter' key)
10) Now you need to get into 'your' database on the mysql server (the naming is a bit weird, as you can have lots of individual dbs/tables, but your personal area of the mysql server seems to be called a 'database' overall).
Type, at the prompt, USE xxxnameofyourdatabasexxx;
so you might type USE rustek_f9;
(or whatever it happens to be called - you'll find the name of your database near the top of your mysql admin page after the name of your server).
11) Once you get 'database changed' come up on telnet, you can then load the txt file you uploaded to the ccgi server, into the db you created with mysql admin.
To do this, type the following:
LOAD DATA LOCAL INFILE 'filename.txt' INTO TABLE rustek01;
(substitute the name of your file, and the name of your table)
If you did it correctly (I find it only works with caps where shown and the filename and table name exactly the same case as whatever you called them) you'll get an immediate confirmation that the file has uploaded.
If you stuff things up somehow (or need to upload an updated file into the same table) you may need to go to mysql admin and 'empty' the table before re-loading the original file into it via telnet (or an updated file that you have pre-loaded onto the ccgi server).
Have to say I never work on tables in mysql admin itself - it's vastly easier to just empty the table, upload a new file to the ccgi server and telnet in the content.
Hope that covers everything Smiley  it's late so forgive me if I've left out anything vital, but hopefully there's enough there for you to at least go through the mechanics of this using a txt file, and figure out how to do it with a csv file afterwards.
Regards,
Penny.
note - I've used 'ccgi' rather than cgi because that's how the address was re-named when there were changes some while back, but it's a cgi-server as far as I know 😕
Penny Rollo * Force 9 from 17/02/98 * PlusNet from 2000 onwards
Project HappyChild - free maths worksheets, free French-English
worksheets and 12 other languages
personal site www.pennymidasrollo.plus.com
rustek
Newbie
Posts: 5
Registered: ‎11-04-2008

Re: My SQLAdmin CSV Import problem

Thanks Penny.
I need to modify my exported text file a little but your method has worked.
Thank you so much for that.
Rus
rustek
Newbie
Posts: 5
Registered: ‎11-04-2008

Re: My SQLAdmin CSV Import problem

One further thing if you can help...
Now I have my text file sorted it is importing perfectly using telnet other than 1 record which is generating a warning.
How can i find out what that warning is?
Peter_Vaughan
Community Veteran
Posts: 14,469
Registered: ‎30-07-2007

Re: My SQLAdmin CSV Import problem

What exactly does it say when reporting this warning?
adagio
Grafter
Posts: 196
Registered: ‎03-04-2008

Re: My SQLAdmin CSV Import problem

Just a thought. If your data is in Access can you not:-
1. Create your new table in MySQL using MySQL Administrator
2. Make an ODBC connection to it
3. Link to it from Access
4. Run an Access Query to append the data
?
I have done this successfully.
David
jelv
Community Veteran
Posts: 26,786
Thanks: 990
Fixes: 10
Registered: ‎10-04-2007

Re: My SQLAdmin CSV Import problem

Even if it's not in Access you can link the table and then import from a csv file to the linked table using Access.
Edit: You need http://www.mysql.com/products/connector/odbc/
Edit2: To connect to your database on the Plusnet servers you specify the server name as rumpus.plus.net or humbug.plus.net
jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£14.40/month)
Mobile: iD mobile (£4/month)
adagio
Grafter
Posts: 196
Registered: ‎03-04-2008

Re: My SQLAdmin CSV Import problem

Quote from: jelv
Even if it's not in Access you can link the table and then import from a csv file to the linked table using Access.

But the data WAS originally in Access, see reply #3, hence my suggestion.
Penny
Superuser
Superuser
Posts: 968
Thanks: 513
Fixes: 5
Registered: ‎05-04-2007

Re: My SQLAdmin CSV Import problem

Quote from: rustek
Thanks Penny.
I need to modify my exported text file a little but your method has worked.
Thank you so much for that.

You're welcome Smiley
Quote from: rustek
... my text file ... is importing perfectly using telnet other than 1 record which is generating a warning.
How can i find out what that warning is?

LOL  I'm possibly not the best person to ask - it's comparatively rare than I get no 'warnings' when uploading to mysql via telnet Wink  but so long as the php call-ups from mysql "work" I tend to disregard the 'warnings' (no time). I tend to take the "plant-path-through-concrete" approach (ie any route will do so long as it *works*) rather than what the 'experts' might say would be ideal.
Having said that, there are probably plenty of techie people here who can tell you how to do things 'properly' Smiley
Regards,
Penny.
Penny Rollo * Force 9 from 17/02/98 * PlusNet from 2000 onwards
Project HappyChild - free maths worksheets, free French-English
worksheets and 12 other languages
personal site www.pennymidasrollo.plus.com
rustek
Newbie
Posts: 5
Registered: ‎11-04-2008

Re: My SQLAdmin CSV Import problem

Reply to Penny,
Thanks.. I had to crawl through 5000 records but i found one date which hadnt converted from the MSAccess long date/Time format because it wasn't a true date. Corrected that and 'Hey Presto' No warnings. Thanks
Reply To Peter Vaughan...
The error message that is produced using the My PHP Admin console is given verbatim in the first post in quotes.
Rus
Penny
Superuser
Superuser
Posts: 968
Thanks: 513
Fixes: 5
Registered: ‎05-04-2007

Re: My SQLAdmin CSV Import problem

Quote from: rustek
Thanks.. I had to crawl through 5000 records but i found one date which hadnt converted from the MSAccess long date/Time format because it wasn't a true date. Corrected that and 'Hey Presto' No warnings.

Glad you got it sorted, Rus Smiley
Penny. 
Penny Rollo * Force 9 from 17/02/98 * PlusNet from 2000 onwards
Project HappyChild - free maths worksheets, free French-English
worksheets and 12 other languages
personal site www.pennymidasrollo.plus.com