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
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 😕
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
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
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
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
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'
Regards,
Penny.
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
Penny.