Help with loading data into MYSQL?

Hi Folks,

This might be a simple task for some people, but it is a very difficult one for me. I am having trouble with my work whenever I try to load data into a table in MYSQL. Whether I am using PHP and MYSQL, or JAVA and MYSQL or XML and MYSQL I always have this same problem. I have never been able to do it successfully once. I have read many tutorials so far and still have not been able to get it right. So I need your help please.

I am working with a tutorial I got from the MYSQL site now and it is not doing the correct thing for me. Maybe you have another idea for me.

1) The code below is my xml file, which I saved as person.xml in the httpdoc folder on the server (even with the file on my desktop, the same problem occurs). I am trying to import the data into mysql.

Code:

---

<?xml version="1.0"?>

  <list>
  <person person_id="1" fname="Pekka" lname="Nousiainen"/>
  <person person_id="2" fname="Jonas" lname="Oreland"/>
  <person person_id="3"><fname>Mikael</fname><lname>Ronstrom</lname></person>
  <person person_id="4"><fname>Lars</fname><lname>Thalmann</lname></person>
  <person><field name="person_id">5</field><field name ="fname">Tomas</field><field name="lname">Ulin</field></person>
  <person><field name="person_id">6</field><field name ="fname">Martin</field><field name="lname">Skold</field></person>
  </list>

---

2) This is the code I used to create the MYSQL table.

Code:

---

CREATE TABLE person (
  person_id INT NOT NULL PRIMARY KEY,
  fname VARCHAR(40) NULL,
  lname VARCHAR(40) NULL,
  created TIMESTAMP
  );

---

3) When I use the Import function in MYSQL to import the data, I get the error messages below. Nothing is imported into my table.

Code:

---

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?xml version="1.0"?> <list> <person person_id="1" fname="Pekka" lname="' at line 1

---

Code:

---

<?xml version="1.0"?> <list> <person person_id="1" fname="Pekka" lname="Nousiainen"/> <person person_id="2" fname="Jonas" lname="Oreland"/> <person person_id="3"><fname>Mikael</fname><lname>Ronstrom</lname></person> <person person_id="4"><fname>Lars</fname><lname>Thalmann</lname></person> <person><field name="person_id">5</field><field name ="fname">Tomas</field><field name="lname">Ulin</field></person> <person><field name="person_id">6</field><field name ="fname">Martin</field><field name="lname">Skold</field></person> </list>

---

Please, what am I doing wrong and could you have a look and tell me the best way to do it please?

I have a huge excel data that I need to import into my PHP and MYSQL work. I cannot enter it all manually, I would like to import it, so your help will be great please.

Thanks,
Menre

are you using mysqlimport? it allows tab-separated text files to be used for input (which excel should be able to export as).

if you aren't, then post whatever you're actually doing, or post a link to this tutorial you're talking about.

Hi,

Thanks for your response to my post. To start with, here is the link for the tutorial that I am working with now. It shows what the result should be like, but I have not been able to do it or apply the techniques to my work.

http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

Menre

Hi Again,

I am using phpmyadmin to import the data into the mysql table. Yet nothing is working so far.

Menre

the "LOAD XML" command is only supported by MySQL >= 5.5. if you don't have MySQL 5.5 (which I would assume you don't if you've done everything the documentation for LOAD XML does), then you can use LOAD DATA. it may be a bit trickier to use with XML, though. I'd still recommend just exporting your Excel document as a comma- or tab-delimited text file and importing it that way.

and you're using phpMyAdmin to do what? run the LOAD XML command?

As an alternative to LOAD XML, you could use an XSL transformation to create a comma- or tab-delimited file which can then be read using LOAD DATA. However, if the data is coming from Excel, I suggest following kows' recommendation of exporting it in a different format.

Hello,

Thanks for your suggestions. Like I mentioned earlier, I get this same problem be it PHP and MYSQL, JAVA and MYSQL or XML and MYSQL that I am working with. Sometimes I just switch from one task to another in attempt to get this thing right. I have had a look at the link you posted, and obviously there is a lot to read there. So, I have now come up with a task that involves PHP and MYSQL. Maybe, I will get the concept right through this one first and use the process for other tasks later.

I am using MYSQL version: 5.1.41.

I have now created a table in MYSQL and want to load some excel data into it for PHP work. How do I achieve this? Is there a better way to do it from PHP file, or how would this work with phpMyAdmin?

Look at how I have attempted it so far, and I have seen some good progress at the moment. There are some screenshots attached to this message!

I created a table with the code below.

Code:

---


CREATE TABLE students(
    fname CHAR(25) NOT NULL DEFAULT '',
    lname CHAR(25) NOT NULL DEFAULT '',
    nationality CHAR(50) NOT NULL DEFAULT '',
    snumber INT(6)NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (snumber)
  );

---

2) Now, I have an excel file, which I have save as a csv file.

I saved it as students.csv and it is in a folder (C:\xampp\htdocs\menre\students.csv) on my server. You can see the excel cvs file as an attachement to this post.

How do I load it into mysql now? I have tried with phpmyadmin, using the option CSV using LOAD DATA. I used their default settings in this option.

3) See the attached images for the settings and results that I have achieved so far.

4) Everithing seems to be loaded into one column. How do I make them go to their correct columns properly?

5) On another note, with the file and table settings at your disposal now, how would you do this normally?

I will apreciate you help please.

Thanks,
Menre

Hi,

Here is the screenshot for the excel file. It is attached to this message please.

Menre

Hi,

Apologies for the confusion with the screenshot images that I posted before. This is the one for the CSV and LOAD DATA options that I used. I hope this will show you the true settings that I am used.

Menre

On the "Options" box, set "Fields terminated by" to a comma, set "Fields enclosed by" to nothing (delete the " and leave it blank), and also set "Fields escaped by" to nothing.

Wow!!!!,

I got it right. Thanks for your help. I followed your instructions and everything worked perfectly this time. Thanks to everyone for your help, your posts were really helpful, I have had too many difficulties with this task of loading data into a table in MYSQL for far too long. Glad it's over now. I will now be doing it with my other projects.

Once again, thanks.

Menre