[SOLVED] Importing/Amending MySQL table

[Grant Holmes]

I have researched this high and low. There seem to be 1000 answers out there that unfortunately DON'T help me. My client is on a Yahoo server. They allow NO external access to the DB/table, so you must import from within PHPmyAdmin.

 

When I go to the DB and the Table I wish to import to, I must be able to browse to the file on my PC and upload it. No matter what I've tried 'fixes' from using Excel to create the file to using note pad... From using quotes to not using them, colons, commas, different extensions lik CSV & TXT, etc. No matter what I do, I get errors.

 

Surely there must be a simple way to do this. I even abandoned trying to import my bigger table just to import/upload a simple two field file with three records. ERROR. Usually a syntax error.

 

I have the DB and Table created. I have data in my working table and need to bring customer data into the table. In the case of my small test, the table it empty. Same results.

 

Any help? I've not seen any tutorial or exact instructions on how to prepare the file.

[revraz]

Why don't you just use SQL commands and enter the data?  PHPMyAdmin will export it in a format you can use to copy/paste as a SQL command.

[Grant Holmes]

I think I'll get the data from the customer exported from Constant Contact, so will probably have to manipulate the data in Excel first for import. IOW, I don't have the data in a MySQL db for export.

 

Or did I not understand your question?

[revraz]

You didn't state whether you had access to the old DB or not, so I assumed you did.

[Grant Holmes]

Sorry. I guess I do have access to the data, but not in the format we'd like! That brings up a question however. While this still needs solved, I'll see if Constant Contact has an export function that might go to MySQL.

[Grant Holmes]

In the mean time, I've gotten a bit of help that the file created needs to be a flat file (like text), with one line per record. So, I used this format to test my small test table. I made a text file like this:

INSERT INTO `test` VALUES ('Value1', 'Value2');

And named the file "test.sql"

 

I got back:

""INSERT INTO `test`

VALUES (

'Value1', 'Value2'

)

 

Error: #1046 - No database selected

==

 

How then do I select the database? In PHPmyAdmin, I was already inside the DB inside the table when I did the import??

[Barand]

you can load a delimited text file with

 

http://dev.mysql.com/doc/refman/4.1/en/load-data.html

[Grant Holmes]

Barand,

I'll look that over in the morning and likely be back with questions.

 

I do see in the first line, it talking about LOCAL FILE. Does that mean it has to be uploaded to the server? I"m not sure Yahoo let's me do that, but I'll read the file. Thanks again.

[fenway]

You won't be able to do this on a remote server unless it's configured to do so...

[Grant Holmes]

I don't see any way to perform what Barand suggests in Yahoo. In the example above, it appears I'm close, but the "no database selected" is obviously the stopping point.

 

When I'm in PHPmyAdmin, I go to the pull down on the left, select my database, then select my table, then click the "SQL" tab where I do the import. How can I not have the DB selected?? I don't get this. What am I missing?

===

Added:

Even when I research "import" on this MySQL page I see nothing about selecting the DB.

[Grant Holmes]

Well, I figured it out. Here's the instructions for those as lost as I was (am).

 

First create the import text file of data to import. Make sure to have the field layout matching. Separate values with ";". So in my simple two field values test the data looked like this:

data1;data2

I saved my file with the extension of (sql) or "filename.sql" locally on my PC.

 

When in PHPmyAdmin, on the left, select your database (DB). then select the table you wish to import to.  In the upper left corner you'll see an SQL button under the PHPMyAdmin logo and click on that button.

 

It will open a separate smaller window. At the bottom of that window, you'll see a text link that says, "Insert data from a text file into the table"- Click that link. Back at the main page, PHPmyAdmin will open a screen where you can browse to upload your data, including changing many parameters. I changed nothing. I hit submit and the data was amended to my table correctly.

 

It shouldn't be that hard.