Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

import csv file to table

Posted on 2006-04-23
16
Medium Priority
?
630 Views
Last Modified: 2013-12-30
Hello Experts. I have an xls (excell) spreadsheet that I exported to csv. I want to create a table in MySQL and populate it with the data from this spreadsheet, accordingly. What's the easiest way to do this?

Many thanks in advance,
John
0
Comment
Question by:John Account
  • 5
  • 4
  • 4
  • +2
16 Comments
 
LVL 12

Expert Comment

by:str_kani
ID: 16522708
you can easily import these data into mysql table with a piece of PHP code. I would go that way, easy and quick.
0
 
LVL 12

Expert Comment

by:str_kani
ID: 16522794
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);
?>
0
 
LVL 8

Accepted Solution

by:
KennyTM earned 2000 total points
ID: 16523361
Hi. Try

mysql_query("LOAD DATA INFILE 'filename.csv' INTO TABLE `your_table`");
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Expert Comment

by:AndyAelbrecht
ID: 16524119
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
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16524550
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.
0
 
LVL 4

Expert Comment

by:AndyAelbrecht
ID: 16524893
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 :(
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16524973
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...)
0
 

Author Comment

by:John Account
ID: 16527031
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`");
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16527082
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`");
0
 

Author Comment

by:John Account
ID: 16527110
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...
0
 

Author Comment

by:John Account
ID: 16527190
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
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16527207
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".)
0
 

Author Comment

by:John Account
ID: 16527460
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?
0
 
LVL 12

Expert Comment

by:str_kani
ID: 16527482
yes, mysql cannot be accesed from a remore machine by default, you will have to setup that.
0
 
LVL 12

Expert Comment

by:str_kani
ID: 16527508
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!
0
 

Expert Comment

by:Rafael_Moreira
ID: 39746305
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question