Loading Data into MySQL from a Text File

[keepingbusy]

Hi

I am trying to upload a large csv file ( >2mb ) into a table within a database. I cannot use phpmyadmin because the csv file is too large.

I have a copy of SSH and can access our database via this (I think!)

Searching on Google I found Loading Data into MySQL from a Text File example:

mysql>LOAD DATA LOCAL INFILE 'd:\\employees.tab' 
->INTO TABLE employee 
->FIELDS TERMINATED BY "\t" 
->LINES TERMINATED BY "\n" 
->(employee_lname, employee_fname, employee_ssn,
->employee_phone, employee_email);

Ok, I understand that (sort of) but how do you get it to firstly recognise the correct database on our server as we have many?

I'm not sure I am going down the right path here but basically I just want to be able to access a database, upload several large csv files to update our website content.

Some guidance not the sollution would be appreciated.

Thank you

 

[TonyGroves]

First you have to connect to the database in some way. On the command line it would be something like:
[tt]
mysql -h myhostname -u myusername -p mypassword mydbname
[/tt]
That will bring you into the MySQL client shell where you can enter SQL commands and queries. The "mydbname" in the command specifies the default database to use. You can also use the SQL command "USE mydbname".

You could also use a GUI database client if you prefer.

 

[keepingbusy]

TonyGroves

Thank you! Its a pleasure to get a straight and easy answer. Thats just what I need to know. I've searched Google and found a few GUI database client's such MONyog and SQLWave.

Just out of curiosity, can you (or anyone else) suggest a suitable/reliable GUI database client?

I'll post back when I get a step further.

Kind regards

 

[TonyGroves]

MySQL Query Browser (free from MYSQL) is good.

 

[vacunita]

I prefer SQLYog myself. Which also has a free version.

----------------------------------
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]

Thanks Guys, I'll post back soon

 

[keepingbusy]

Hello guys

Ok, I have mangaged to access the actual database using Putty SSH and using the command TonyGroves suggested:

mysql -h myhostname -u myusername -p mypassword mydbname

I then typed in the example I gave above:

mysql>LOAD DATA LOCAL INFILE 'd:\\employees.tab' 
->INTO TABLE employee 
->FIELDS TERMINATED BY "\t" 
->LINES TERMINATED BY "\n" 
->(employee_lname, employee_fname, employee_ssn,
->employee_phone, employee_email);

and received the following error:

-bash: syntaxsyntax error near unexpected token `('

As the error suggests '(' and I can only see one pair of brackets, can anyone suggest what the error means.

Also, is there any way of loading these commands into the dos prompt window of Putty SSH? I have tried to copy and paste but it doesn't seem to work and this appears to be quite a long winded way of issuing commands especially of they are extensive!

I am a complete newbie to this, but so far so good.

Thank you

 

[keepingbusy]

Sorry the error should read:

-bash: syntax error near unexpected token `('

 

[keepingbusy]

Any suggestions guys?

Thank you

 

[TonyGroves]

Sorry, I've been asleep. That looks like a bash (system command shell) error. I can't understand why you'd get that if you're safely inside the MySQL command shell, which you seem to be. Are you able to run simpler queries with no problem?

 

[ingresman]

You can write the query to a text file and load it into mysql rather than use redirection.
You start mysql with the -u etc, and then at the prompt type \. filename and it should work then.
I use windows so don't get involved with the cliever *nix stuff !

 

[keepingbusy]

TonyGroves

Are you able to run simpler queries with no problem?

Yes. There is a list of commands shown when I am in the SSH window so I searched Google to find out what some of them mean and tried a few. They work ok.

ingresman

... and then at the prompt type backslash . filename and it should work then. I use windows so don't get involved with the cliever *nix stuff !

Ok, I understand that but when I type \. filename where does the file have to be? So for example, if I copy the code to upload.txt where does that file have to be and can you give me some idea what the actual would be at the prompt.

Thank you both for your help so far.

 

[TonyGroves]

You shouldn't need to use ssh, it just adds another layer of complication. You could just connect directly to the database via a local command shell or GUI.

 

[keepingbusy]

TonyGroves

Thank you for the reply. My apologies, but I am not sure how to connect via a local command shell or GUI and also, I do not want you to think I am looking for the actual sollution as learning is the key of life!

What I would be grateful for at the moment, is some sort of direction on why I am getting the error (only because so far, I've managed to access my database via the SSH window) so I can have a go at somehow, uploading the csv file to a table.

When I have managed to achieve that, I can then perhaps, advance and try what you have suggested.

I'm grateful so far, for the advice on this thread.

KB

 

[TonyGroves]

I've no idea why you're getting that error but at least if you eliminate the extra layer that SSH imposes you're eliminating a source of possible error.

If you have the MySQL command-line client installed locally (which you probably haven't) you can use the connection command already given. Otherwise, install a GUI client like the ones already recommended. They're simple and free of charge.

 

[keepingbusy]

Thank you for the reply.

 

[keepingbusy]

Hello Guys

Just revisiting this thread with what I have achieved so far but I am still havng problems with the final stage.

My hosting company gave false info so that put me back a bit. Anyway, this is what I have:

Start up SSH with the relevant settings
At the command promp I type in the relevant details in this format:
mysql -h localhost -u dxxxxxxxxxxx -p dxxxxxxxxxx
I'm then prompted for the database password
password: xxxxxxxx

Ok, this is fine so far as I obviously now have access to the database.

I have a file called employees.csv which can either be in a folder called test on the root of the server where the actual domain name is or on a hard drive on a computer also in a folder called test.

So this is where my problem starts.

I have tried several commands but with out success (either saying file not found or access denied or mysql comand error)

Command 1:

LOAD DATA INFILE "./employees.csv" INTO TABLE employee FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n";

Command 2:

LOAD DATA LOCAL INFILE '/test/employees.csv' INTO TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

I have tried other combinations also with out success.

I would be grateful if someonecould please tell me where this is going wrong for something that appears to be so simple looking at searches on Google but I'm completely stuck with this.

Thank you

Lee

 

[hvass]

kepingbusy

Asuming employees.csv is on the root drive of the mysql server then the correct command is

LOAD DATA INFILE "/employees.csv" INTO TABLE employee FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n";

Note not ./employees.csv

You mentioned 'doman' so I am guessing this is not a corporate server but some hosted server that you cannot connect to directly hence having to use ssh. In which case the root of your domain is not the same as the root but probably some directory structure that keeps all the hosted domain accounts kept very separate from the root of the server for security maybe something like /home/hostedaccounts/enduserdomain.com/htdocs

You need to find out exactly where emplyees.csv is so from the bash prompt find where employees.csv is and the command pwd will show you the full path.

 

[keepingbusy]

Hi hvass

Now what your saying is starting to make some sense. Firstly, you metion about "the root". Yes, in fact, there is a long name such as /home/hostedaccounts/enduserdomain.com/htdocs that stores our domains so my first job is to establish that.

Secondy, the coding change you have mentioned, I will try out when I find the correct path as above.

I'm grateful for your advice and I'll post back as soon as I have tried this out.

Many thanks

Lee

 

[keepingbusy]

Hi hvass(and others)

Ok. I've established the path by using pwd. It gave a similar directory to that posted by hvass.

I then tried to issue the LOAD DATA command etc... by adding my domain name at the end of the path but got a file not found error.

If our domain (for example

http://www-dot-mydomain.com)

is located in webspace /mydomain am I simply to say:

mypath/etc/etc/www-dot-mydomain.com/mydomain/employees.csv

I have tried several combinations of th above, all of which give me file not found so I am very sure now it is just a case of getting the correct path and this should then work.

Please note: I have added DOT so as not to create a hyperlink in this thread

I would appreciate your suggestions guys.

Thank you

Lee