CODEWITHSUNDEEP
mysqlsqlexcel
DeeBrad
DeeBrad

Reputation: 47

Exporting from Excel to MySQL

I have an Excel file in which i have 50 questions i want to include for a questionnaire in a java applet. The answers for this questionnaire are to be stored in a MySQL database i have created.

Is there any way i can select all (or a subset) of the questions from my Excel file and place them into fields (that i choose) in the MySQL table?

I have looked around and load data infile sounds like an options that works but i would like to select which questions and where to place them. Could anyone point me in the right direction? Or should i simply copy and paste?

Any help would be great!

Upvotes: 2

Views: 15457

Answers (5)

Srdjan
Srdjan

Reputation: 163

The easiest way for me was to open Excel file with MS Access (you can do it directly: File -> Open ->...) and with ODBC export data to MySQL (right click over table -> Export -> ODBC database.

By the way, I've experimented one bug with "MySQL for Excel", the output has only 999 rows. In theory, it will be solved with the next 1.3.4 version.

Upvotes: 0

Yordan Georgiev
Yordan Georgiev

Reputation: 5430

# wanna be generic implementation of xls to mysql upsert in perl
# by this time you should have your mysql connection open ... 
use Spreadsheet::XLSX;
use Text::Iconv;



#
# -----------------------------------------------------------------------------
# runs the insert sql by passed data part 
# by convention is assumed that the first column is unique and update could 
# be performed on it ... should there be duplicates the update should fail
# -----------------------------------------------------------------------------
sub RunUpsertSql {

   my $self             = shift ; 
   my $table_name   = shift ; 
   my $refHeaders   = shift ; 
   my $refData      = shift ; 
   my $data_str         = '' ; 
   my @headers      = @$refHeaders ; 
   my @data             = @$refData ; 

   print ( "\@data : @data" ) ; 
   print ( "\@headers: @headers" ) ; 

   my $sql_str = " INSERT INTO $table_name " ; 
   $sql_str .= '(' ; 
   for ( $i=0; $i<scalar (@headers);$i++ ) {
      $sql_str .= " $headers[$i] " . ' , ' ; 

   } #eof for

   for (1..3) { chop ( $sql_str) } ; 
   $sql_str .= ')' ; 

   foreach my $cellValue ( @data ) {
      # replace the ' chars with \'
      $cellValue        =~ s|\'|\\\'|g ; 
      $data_str .= "'" . "$cellValue" . "' , " ; 
   }
   #eof foreach ' replacement

   # remove the " , " at the end 
   for (1..3) { chop ( $data_str ) } ; 

   $sql_str .=  " VALUES (" . "$data_str" . ')' ; 
   $sql_str .= ' ON DUPLICATE KEY UPDATE ' ; 

   for ( $i=0; $i<scalar(@headers);$i++ ) {
      $sql_str .= "$headers[$i]" . ' = ' . "'" . "$data[$i]" . "' , " ; 
   } #eof for

   for (1..3) { chop ( $sql_str) } ; 

   print ( "sql_str : $sql_str " ); 

   $sth = $dbh->prepare($sql_str ) ; 
   $sth->execute( );

}
#eof sub RunUpsertSql


#
# -----------------------------------------------------------------------------
# walk trough the Excel and build the data part of the insert sql
# -----------------------------------------------------------------------------
sub ParseExcel {

   my $self = shift ; 
   print (  " == START == " ) ; 
   # not sure if it could work without the next line
   # for utf8 strings - slavic , japanese etc. 
   my $converter = Text::Iconv -> new ("utf-8", "utf-8");

   # http://search.cpan.org/~dmow/Spreadsheet-XLSX-0.13-withoutworldwriteables/lib/Spreadsheet/XLSX.pm
   my $objExcelParser = Spreadsheet::XLSX -> new ("$FileInputExcel", $converter);

   # iterate the sheets
   foreach my $objSheet (@{$objExcelParser-> {Worksheet}}) {

      print("Sheet: " . $objSheet->{'Name'});

      my $rowCount = 0 ; 
      # iterate the rows 
      my @headerData                    = ();
      foreach my $row ($objSheet -> {'MinRow'} .. $objSheet -> {'MaxRow'}) {
         my @rowData                    = (); 
         $objSheet -> {'MaxCol'} ||= $objSheet -> {'MinCol'};

         # iterate the coloumns
         foreach my $col ($objSheet -> {'MinCol'} ..  $objSheet -> {'MaxCol'}) {
            my $cell = $objSheet -> {'Cells'} [$row] [$col];
            if ($cell) {
               #debug printf("( %s , %s ) => %s\n", $row, $col, $cell -> {'Val'});
               # the unformatted value
               #my $cellValue = $cell->{'Val'}  ; 
               # push the formatted value
               push ( @rowData , $cell->value() )       if $rowCount != 0 ; 
               push ( @headerData , $cell->value() )    if $rowCount == 0 ; 

            }  #eof if the cell is defined
         } 
         #eof foreach col
      # by convention the name of the xls sheet is the same as the table name
      $self->RunUpsertSql ( $objSheet->{'Name'} , \@headerData , \@rowData) 
         if $rowCount != 0 ; 

         $rowCount++ ; 
      }
      #eof foreach row

   } 
   #eof foreach $objSheet

   print (  " == STOP  == " ) ; 

} #eof sub ParseExcel

Upvotes: 0

Mohsin Khan
Mohsin Khan

Reputation: 9

This might seems a bit old now but would help any other person looking for the solution.

There is official MySQL plugin for Excel to export, import, append and edit the MySQL data in Excel.

Here is the link: http://dev.mysql.com/doc/refman/5.6/en/mysql-for-excel.html

Upvotes: 0

Sirko
Sirko

Reputation: 74076

Save your Excel data in the CSV format and then import it in MySQL using the LOAD DATA command.

Information on the later can, e.g., found here:

Upvotes: 4

Arka Dev
Arka Dev

Reputation: 56

Have a look into Excel saving the data as a CSV file and then loading it to MySQL. Try to format the data, in Excel, according to the MySQL structure.

Upvotes: 0

Related Questions