import csv file to table

you can easily import these data into mysql table with a piece of PHP code. I would go that way, easy and quick.

sample..
<?php
// connect to your database
$handle = fopen("data_file.csv", "r");
$data = fgetcsv($handle, 1000, ",");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
// collect all data in tha line by 0,1,2 etc
$field_name=addslashes($data[0]);

// form insert query  to insert into mysql

mysql_query($query);

}
fclose($handle);
?>

What KennyTM said would work

but
you said that you have Excel CVS; Excel CVS is a MS™ CVS, meaning that it's almost like it should be but it's like 5% off.
so there is a small chance that it will not work, because Excel saves the CVS "wrong". 1 of the things it doesn't do is adding "" between string fields. It does it only for fields which have a " (double quote) or the seperator in the sentence.

examples:

1;this is a string
2;"this is a ""string"""
3;"this is a; string"

see what i mean here ?
now, mysql isn't stupid and can live with situation 1 & 3. However, it can not live with situation 2; an escaped double quote (") in microsoft land is "". In the rest of the world, an escaped double quote is \". This means we'll have to do some search&replacing in the CVS file before mysql LOAD DATA INFILE can use the Excel CVS file.

A small (prolly not the fastest) function i wrote for this when i had to deal with this exact situation:

<?
$inputcsv = "input.csv";
$outputcsv = "output.csv";

$fp = fopen($inputcsv, "r");
$fp2 = fopen($outputcsv, "w");
$teller = 0;
while ($stream = fgets($fp, 4096)){

        $outputline = str_replace("\"\"", "\\\"", $stream);
        fwrite($fp2, $outputline);
}

fclose($fp);
fclose($fp2);

?>

your output.csv file will be usable in the query KennyTM just showed.

hope this helps,

cheers,
Andy

AndyAelbrecht, thanks for pointing that out. However, situation 2 can in fact be overcome using the FIELD ENCLOSE BY keywords as you can see in http://dev.mysql.com/doc/refman/5.0/en/load-data.html . Also in the same page there is already a solution:

LOAD DATA INFILE 'filename.csv' INTO TABLE `table` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

(Search: Posted by Norman Santos on September 14 2004 8:53am)

Hope this helps.

that does take care of situation 1 & 3 but not for situation 2 afaik ?

the optionally enclosed by lets the MySQL CVS parser know that strings can be enclosed by "" but it doesn't tell the mysql cvs parser that it should treat "" as \" ?

well, it could work ofcoz, and it's been some time since i wrote this script (like 9 months or so); i'm pretty sure there was a good reason for me to do so, but i could be wrong and can't test your solution right now :(

In fact it isn't "my" solution; the solution is just copied from the comments in the MySQL documents.
(I can't test it either because my host's MySQL server and web server are different, that LOAD DATA INFILE cannot access (at least I don't know how to) cross two servers. But I believe that the writer of that solution have tested it before posting...)

As far as the "Qoutations" goes, that isn't an issue with me. I did a search & resplace for all of them--so now there aren't any in my csv file. However, in order to run the following query, how do I indicate the path to my csv file?--

mysql_query("LOAD DATA INFILE 'filename.csv' INTO TABLE `your_table`");

The safest option is to use the full path...

(*NIX)
mysql_query("LOAD DATA INFILE '/tmp/file.csv' INTO TABLE `your_table`");

-or-

(Win)
mysql_query("LOAD DATA INFILE 'C:/path/to/the/file.csv' INTO TABLE `your_table`");

That's exactly what I had tried before, but still getting the following error:
Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us...

The columns are-- ID (auto_increment), Yr, Title, Description
(Nulls are not allowed)
Here's a sample of some of my csv file:

Skydive,1,Skydive 1,John goes out for his civillian skydiving certification. This is his 1st jump thereof.*LearningToFly1.htm
Skydive,2,Skydive 3,His 3rd jump towards certification.*LearningToFly3.htm
Skydive,3,Guantanamera,No Description Available.*Guantanamera.htm
Skydive,4,Wonderful,A wonderful day to be skydiving.*Wonderful.htm
Skydive,5,Skydive Flip,John doesn't just like to fall through the sky--he likes to have fun falling. Here he does a flip.*SkydiveFlip.htm
Skydive,6,1st HoolaHoops,John, out in the sky with a group of other skydivers doing hoolahoops. This is the 1st one of that day.*HoolaHoops1.htm
Skydive,7,2nd HoolaHoops,John, out in the sky with a group of other skydivers doing hoolahoops. This is the 2nd one of that day.*HoolaHoops2.htm
Skydive,8,Both HoolaHoops,John, out in the sky with a group of other skydivers doing hoolahoops. This is a video of both hoolahoop jumps of that day.*BothSkydiveHoola.htm
Skydive,9,Roland Skydives,Roland needed a good adrenalin fix--and that's exactly what he got from his brother, John, for Christmas. He was scared. But he aint no joke--he leaps right into fear.*RolandSkydives.htm
2004p,10,A day at the BioDome,John (Gabe) & Family. spend a day at the world reknown BioDome in Montreal, in 2004*BioDom.html
2004p,11,Winter Scenes in Montreal 04,A Montreal Winter in 2003*WinterScenes.html
2004p,12,Robert Medina in Montreal,Robert Medina visits Gabe in Montreal*RobertMontreal.html
2004p,13,Gabe visiting from Montreal, 04,No Description Available*BackFromMontreal.html
2004p,14,Camping Trip-7/31/04,No Description Available*CampingJuly31.html
2004p,15,Circus Trip 04,No Description Available*MikeCirucus.html
2004p,16,Camping Trip 04,No Description Available*Camping04.html
2004p,17,Donna's son, James,No Description Available*James.jpg
2004p,18,Mike--The Professional,No Description Available*ProMike.html
2004p,19,Mike & Family. At Monster Inc 04,No Description Available*MonsterInc04.html
2004p,20,Mike & Family. Christmas 2003,No Description Available*Mike-Xmas-2003.html
2004p,21,Birthday Party 04,No Description Available*Rolands35B-Day.html
2004p,22,Roland & His New Car,No Description Available*RolandsLexis.html
2004p,23,LouisBday04,No Description Available*LouisB-Day04.html
2004p,24,Christmas Wonderland,No Description Available*WinterWonderland.html

Several things you can do...

1) Try using LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.
2) There may be errors in the CSV file after you've done the search & replace stuff. Try using the original CSV without any modification.
3) Try some simple CSV and see whether this is the statement's error or the CSV's error. For example:

1,"2"
3,"a"

4) Check if you has enough priviledge to LOAD DATA. (I don't think this is the cause though, because the error is "syntax error".)

Yes, I have enough priveledges. Still, nothing is working as per your instructions. So, just to let you know, in case this makes any difference, MySQL is on a dedicated server--Not on a computer here at my desk. I connect to mysql Remotely via MySQL Control Center 0.9.4-beta. Does that make any difference?

yes, mysql cannot be accesed from a remore machine by default, you will have to setup that.

if this is the case you may get access denied error, try connecting to the server with a sample connection script...

If the following error occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host:

Host ... is not allowed to connect to this MySQL server

You can fix this by setting up an account for the combination of client hostname and username that you are using when trying to connect.

If you do not know the IP number or hostname of the machine from which you are connecting, you should put a row with '%' as the Host column value in the user table. After trying to connect from the client machine, use a SELECT USER() query to see how you really did connect. (Then change the '%' in the user table row to the actual hostname that shows up in the log. Otherwise, your system is left insecure because it allows connections from any host for the given username.)

Login to MySQL Server(host on which MySQL Server is running)

[root@mmkserv test]# mysql -usqladmin -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.20-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON *.* TO 'EdwardPeter'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.14 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.16 sec)

mysql> \q

Hope this helps!

I' m trying to import a file with primefaces . i m using jpa

That's ok.

I'm having dificulties with read the data to put into my table (Items) on MySQL.

i have done the import , but still tryed read with java poi , jtable csvreader and others ... all of that  i ve gor erros.

i will put a example that Works witout merge in table.

Named
@RequestScoped
public class FileBean {
      private String diretorioDestino = "c:\temp\";

      private UploadedFile file;

      public UploadedFile getFile() {
            return file;
      }

      public void setFile(UploadedFile file) {
            this.file = file;
      }

      public void send() throws IOException {
            FacesContext context = FacesContext.getCurrentInstance();
            if (getFile() == null) {
                  context.addMessage(null, new FacesMessage("Erro",
                              "Escolha um arquivo"));
            } else {
                  String nomeArquivo = file.getFileName().substring(
                              file.getFileName().lastIndexOf("\") + 1);
                  Upload(nomeArquivo, file.getInputstream());
                  context.addMessage(null, new FacesMessage("Sucesso",
                              "Arquivo importado com sucesso"));
            }
      }

      private void Upload(String fileName, InputStream in) {
            try {
                  OutputStream out = new FileOutputStream(new File(diretorioDestino
                              + fileName));
                  int reader = 0;
                  byte[] bytes = new byte[(int) getFile().getSize()];
                  while ((reader = in.read(bytes)) != -1) {
                        out.write(bytes, 0, reader);
                  }
                  in.close();
                  out.flush();
                  out.close();
            } catch (IOException e) {
                  System.out.println(e.getMessage());
            }
      }

}

Att.