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

 

[hvass]

Can you do a
>ls -all employees.csv
then follow that with
>pwd
and post

I am a bit sceptical that you have found the right directory /etc not the natural place to be putting data for an ISP perhaps there is some sort of symbolic link in the /etc directory that points to the real directory

 

[keepingbusy]

Hi hvass

The /etc was just an example rather than me post the full path I found for security reasons. I hope this didn't confuse the issue.

I will take a look at your last post and reply back soon.

 

[keepingbusy]

Hello all

With regards to hvass request, I have issued the following commands within ssh:

ls -all employees.csv

This resulted in:

ls: employees.csv: No such file found

However, I tried the same ls -all command with a file I know is in the root of our domain called index.php and it found that file.

The file employees.csv is contained in a folder called csvfiles and I know it exists even though the ls -all command states otherwise.

I also issued:

pwd

This came back with (I have change some of the actual names for security reasons):

/kxxxxx/homepages/99/d1xxxxxxx8/htdocs

I would be grateful if anyone could assist with this.

Thank you

 

[hvass]

ls -all only lists files in the current directory

cd to csvfiles and do again, you probably need something like ...

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

 

[keepingbusy]

Hi hvass (and others)

Well I'm not having any luck with this and I think its time to give up as I'm getting nowhere fast.

This is what I have tried:

1:

LOAD DATA INFILE '/kxxxxx/homepages/99/d1xxxxxxx8/htdocs/cdxcsv/employees.csv' INTO TABLE `employee` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

This did not work and changed the prompt to ->

When I typed \c at the prompt, it changed it back to mysql>

2:

LOAD DATA LOCAL INFILE '/cdxcsv/employees.csv' INTO TABLE `employee` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

This did not work and also changed the prompt to ->

3:
In phpmyadmin, I noticed that the command line is created when you manually "insert data from a text file into the table"

However, some of the code in the line changes, example:

LOAD DATA LOCAL INFILE '[b]/var/tmp/phpncLTAA[/b]' INTO TABLE `employee` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

The bold text changes each time I upload the csv file manually so I replaced that with the location folder where the csv file is contained on the C drive of our computer.

This still doesn't work.

I am at a complete loss with this and cannot understand why something simple can be so difficult.

I have no doubt it is something I am doing but I am sure I have followed the advice and instructions you guys have given.

Tek-Tips doesn't like you to use the word "Help" in forums, but I could do with some please!

 

[hvass]

Dont worry it is straight forward... only complication is coming from the fact that you dont control the server it is hosted on

1.& 2. You need to complete a mysql command with ;
try no 1 again
3. I dont think is the way to go as you noticed phpmyadmin uses a temporary file - dont assume you have access to it - phpmyadmin does

 

[keepingbusy]

Apologies for the delay in posting back.

Having tried out all the code it now appears that our hosting company are saying that we cannot upload a csv file via SSH even though they told us we could.

We are now in dispute with them and have asked them to seriously consider changing the authorities that will allow us to fulfil our request.

When we get the reply or other result, I'll post back.

I'm sure sooner or later, this isssue will be resolved.

Seasons greetings to all

 

[TonyGroves]

Another approach you might like to consider:

Install a MySQL client program on your computer. Edit the CSV file to produce an SQL command file. For example:[tt]
123 happy
456 christmas[/tt]
becomes:[tt]
insert mytable
(id,message)
values
(123,'happy'),
(456,'christmas')[/tt]
Connect the client to the remote server and run the command file through it.

Now I'm off to bed to make sure Santa Claus leaves me some toys. Happy Christmas everyone!

 

[keepingbusy]

Hi TonyGroves

Sorry my friend, I have no idea what you mean!

I am still waiting for a reply from our hosting company and once I get that, I'l go from there (but thank you for the suggestion)

Lee

 

[TonyGroves]

What I mean is, install a MySQL client program such as one of the GUI clients already mentioned, on your own computer. Using that client program you can execute queries on the remote database from the comfort of your computer, without having to use SSH. Assuming that your hosting company allows it, of course.

Then edit the CSV file, using a powerful text editor or your favourite scripting language, so that you end up with a valid MySQL insert command, and save the file.

Now feed the resulting command into your MySQL client program, which will pass the command to the remote server for execution.

 

[keepingbusy]

Ok, makes sense now.

I am still wating to see if our hosting company can explain why they said we could achieve this project using the method explained in this post and then changed their minds.

We await....

Thank you for your post