What do I need?

[keepingbusy]

Sorry if this sounds way below the expertise of you guys, but I am trying to understand how this is done.

I have a database containing tables
I want to upload a csv file into one of those tables within the database to update it
I dont want to do this via myphpadmin due to file size limit
I want to do the upload via MySql

So I understand there are a few commands:

mysql -u username -p password database_name < file_name

LOAD DATA INFILE 'C:/mytable.csv' INTO TABLE myproducts FIELDS TERMINATED BY ',';

Now I have seen SSH running through an MS-DOS window but was wondering how do I get MySql running? Is it a piece of software or something done through an MS-DOS prompt?

Is there anything you experts need to know or can you please give me some guidance?

Thank you

 

[vacunita]

If you have it installed on your machine yes, open a DOS box, and navigate to wherever its installed, and run the command.

If your doing this on a hosting server then probably you won't have terminal access which means you won't be able to do it that way.

So your are either bound the phpmyadmin or if your host allows by using a front end app (such as SQLYOG) to connect to the DB and upload the file.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[keepingbusy]

Hi vanunita

If you have it installed on your machine yes, open a DOS box, and navigate to wherever its installed, and run the command.

Sorry, have what installed?

Are you talking about MySql? If so, which version as I can see there are many on MySql.com

Thank you for the very quick reply.

 

[twiSSt]

Where is your version of MySQL running - your computer, inhouse server or on a hosted server?

Or do you not have MySQL running at all and are looking to get started?

I'm not understanding you 100%, so can't form a solid response for you.

Yes you can SSH into a MySQL database - as long as the ports are open, you can use a tool like Putty to do that.

There are a few really good tools for managing data in MySQL, Navicat and EMS to name a few as well as MySQL query Browser available from MySQL for free!

Give us some more details and I'm sure we can point you in the correct direction.

Twist

===========================================
Everything will be OK in the end.
If it's not OK, then it's not the end

 

[vacunita]

I have a database containing tables

I assumed this meant you had mysql running somewhere with your tables.

If you have your database running on your own machine, then you can use a DOS box to run the command.

If your database is being hosted on a separate server, such as a hosting company then I would assume you don't have terminal access to it to run the commands. Which means you need a different kind of access such as the myphpadmin or a front end to run the updates if you host allows remote connections.





----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[keepingbusy]

Thank you for the replies

twiSSt

Or do you not have MySQL running at all and are looking to get started?

Yes i am lloking to get started somewhere.

I do not have MySql installed anywhere at the moment but would want it installed on a local computer so I can upload the csv files to a table in a database on a server.

Sorry for the confusion and hope that makes sense.

Kind regards

 

[ingresman]

it sounds like you think mysql is a tool to do the upload and insert not the database server itself ?

 

[keepingbusy]

Yes, perhaps I am getting confused here. I simply want something that I can issue the commands I have previously mentioned so I can insert the csv data into a table contained within a database on our server.

The reason behind this is because I may want to automate this process and also we are restricted to a 2mb file size via phpmyadmin.

 

[twiSSt]

MySQL is a database server, not a tool to interact with the database, if you have it running on server you will need the IP address of the server and the login info. For remote I would recommend Putty for the command line that you refer to

If you are in the testing/planning pahase go to MySQL.com and download the latest stable version of the community server.

Should get you to the correct place:

http://dev.mysql.com/downloads/mysql/5.0.html

Then you need to decide how you want to interact with it via some sort of software - MySQL query browser is a good place to start:

http://dev.mysql.com/downloads/gui-tools/5.0.html

These give you a GUI to work in.

Check out Navicat - I forget how much but it is under $100.00. there is alot of freeware out there, but off the top of my head I cannot name any. I kinda like this one:

http://www.sqlmanager.net/products/mysql/manager

There are also many sites that have great tutorials on how to manage, import and export data in MySQL, time well spent.



Twist

===========================================
Everything will be OK in the end.
If it's not OK, then it's not the end

 

[vacunita]

So do you or do you not have a database somewhere that is holding your tables? And is it MYSQL?

In the case that you do have a database that holds your tables, then again I say you need a front end app to do it.

To actually run the DOS commands, (like the first one you posted)

mysql -u username -p password database_name < file_name

You need terminal access. That is access to the dos prompt in case of Windows either via telnet, or by physically sitting in front of the computer that holds the database server and opening a DOS prompt to issue the commands.

Using a Front end you won't be able to run the DOS commands
But you will be able to run the queries such as LOAD DATA INFILE.

Again from what myself and other's have posted you have options as to what front end you want to use.

Mysql Query Browser is one.
Found at the mysql.com website here:

http://dev.mysql.com/downloads/gui-tools/5.0.html

The first one there: Windows (x86) 5.0-r12 17.4M
is the one you want.

I prefer the free version of Sqlyog myself: SQLyog MySQL GUI - Community Edition 6.56 (Stable)

http://www.webyog.com/en/downloads.php

But again I say using these tools are all subject to your host allowing remote connections to the database server.





----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[keepingbusy]

Ok guys, thanks for everything so far.

I have managed to use Putty SSH and been able to log in using my details.

At the prompt (in a DOS type window) I typed mysql -u dbxxxxxxxxx2 -p xxxxxxxx dbxxxxxxxx2 and this gave me some information about the database (Not sure what it means yet, but I am getting there).

So firstly, am I in the right place?

If yes, am I now able to issue the commmand I mentioned earlier about uploading my csv file and if so, can you give me some idea what I need to issue and in what sort of order?

I will in the meantime, do some research for MySql commands.

My sincerest thanks.