Home > General, Java > Load Data XLS/XLSX/CSV to MySQL with Java

Load Data XLS/XLSX/CSV to MySQL with Java

21 November 2010 Leave a comment Go to comments

Pada beberapa kasus, banyak data suatu instansi/perusahaan disimpan dalam file MS Excel. Hal ini karena data dapat disimpan dengan mudah dan tidak menyulitkan pengguna. Namun, ada kalanya data yang telah disimpan tersebut menjadi banyak seiring dengan waktu. Bahkan menjadi sulit jika recordnya sudah mencapai ribuan bahkan lebih. Seiring perkembangan teknologi, data tersebut harus disimpan pada database tertentu misalnya MySQL. Tentu kita tidak ingin mengentri data secara manual dari awal, mungkin tidak menjadi masalah jika datanya hanya berkisar puluhan, tapi bagaimana jika ribuan…???

Posting ini akan membahasa tentang cara meload data dari file XLS/ XLSX ke Database MySQL, dengan cara mengimport data yang telah ada ke database. Misalnya, kita mempunyai data pada file XLS/XLSX sebanyak 10 record yang terdiri dari Nama dan NIM.

XLS File

Simpan file tersebut (Save As) sebagai file CSV.

SaveASSCV

Jika kita buka file SCV tersebut, masing-masing field dipisahkan dengan tanda titik koma (;). Secara default file SCV menggunakan titik koma (;) sebagai pemisah (delimiter).

NotepadView

Struktur database dan table yang kita miliki:


CREATE DATABASE LATIHAN_JAVA;

CREATE TABLE LOADDATA(
   ID INTEGER AUTO_INCREMENT PRIMARY KEY,
   NAMA VARCHAR(255),
   NIM VARCHAR(11) UNIQUE
);

File Koneksi, gunakan database, username dan password yang sesuai dengan konfigurasi server Anda.

KoneksiMySQL.java

/*
 * Copyright(c) 2010
 * pizaini.wordpress.com
 */

package loaddatacsvmysql.com.pizaini.fx;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.SQLException;
import javax.swing.JOptionPane;

/**
 *
 * @author Pizaini
 */
public class KoneksiMySQL {
 private static Connection connection;

 public static Connection getConnection() {
 if(connection == null){
 MysqlDataSource dataSource = null;
 try{
 dataSource = new MysqlDataSource();
 dataSource.setUrl("jdbc:mysql://localhost:3306/LATIHAN_JAVA");
 dataSource.setUser("pizaini");
 dataSource.setPassword("xxxxx");
 connection = dataSource.getConnection();
 }catch(SQLException exc){
 JOptionPane.showMessageDialog(null, exc.getMessage(), "Error Koneksi", JOptionPane.ERROR_MESSAGE);
 }
 }
 return connection;
 }
}

Source code berikut menggunakan File Choser dan File Filter untuk memfilter file CSV. Dan menggunakan String Replace untuk mereplace karakter (\) menjadi (/), agar dapat dibaca oleh Query MySQL.

FrameBrowse.java

/*
 * Copyright(c) 2010
 * pizaini.wordpress.com
 */

/*
 * FrameBrowse.java
 *
 * Created on Nov 15, 2010, 9:25:51 PM
 */

package loaddatacsvmysql.com.pizaini.fx;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.filechooser.FileFilter;

/**
 *
 * @author Pizaini
 */
public class FrameBrowse extends javax.swing.JFrame {
 private static final long serialVersionUID = 1L;
 private static Connection connection;

 /** Creates new form FrameBrowse */
 public FrameBrowse() {
 setLocationRelativeTo(null);
 initComponents();
 }

 @SuppressWarnings("unchecked")
 // <editor-fold defaultstate="collapsed" desc="Generated Code">
 private void initComponents() {
 buttonBrowse = new javax.swing.JButton();
 textPath = new javax.swing.JTextField();
 buttonLoad = new javax.swing.JButton();
 jLabel1 = new javax.swing.JLabel();

 setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
 setTitle("Load Data to MySQL");

 buttonBrowse.setText("Browse");
 buttonBrowse.addActionListener(new java.awt.event.ActionListener() {
 public void actionPerformed(java.awt.event.ActionEvent evt) {
 buttonBrowseActionPerformed(evt);
 }
 });

 buttonLoad.setText("Load Data to MySQL");
 buttonLoad.addActionListener(new java.awt.event.ActionListener() {
 public void actionPerformed(java.awt.event.ActionEvent evt) {
 buttonLoadActionPerformed(evt);
 }
 });

 jLabel1.setText("PATH");

 javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
 getContentPane().setLayout(layout);
 layout.setHorizontalGroup(
 layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
 .addGroup(layout.createSequentialGroup()
 .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
 .addGroup(layout.createSequentialGroup()
 .addContainerGap()
 .addComponent(jLabel1)
 .addGap(18, 18, 18)
 .addComponent(textPath, javax.swing.GroupLayout.DEFAULT_SIZE, 334, Short.MAX_VALUE)
 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
 .addComponent(buttonBrowse))
 .addGroup(layout.createSequentialGroup()
 .addGap(166, 166, 166)
 .addComponent(buttonLoad)))
 .addContainerGap())
 );
 layout.setVerticalGroup(
 layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
 .addGroup(layout.createSequentialGroup()
 .addContainerGap()
 .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
 .addComponent(buttonBrowse)
 .addComponent(textPath, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
 .addComponent(jLabel1))
 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
 .addComponent(buttonLoad)
 .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
 );

 pack();
 }// </editor-fold>

 private void buttonBrowseActionPerformed(java.awt.event.ActionEvent evt) {
    browseCSV();
 }

 private void buttonLoadActionPerformed(java.awt.event.ActionEvent evt) {
    if(textPath.getText().equals("")){
       JOptionPane.showMessageDialog(null, "No File Choiced !", "Error", JOptionPane.ERROR_MESSAGE);
    }else
      loadData();
 }

 private void browseCSV(){
   JFileChooser jfc = new JFileChooser();
   FileFilter fileFilter = new FileFilter() {
     @Override
     public boolean accept(File f) {
       if(f.getName().toLowerCase().endsWith(".csv") || f.isDirectory() ){
          return true;
      }else{
         return false;
     }
   }
 @Override
 public String getDescription() {
   return "*.csv";
  }
 };

 jfc.addChoosableFileFilter(fileFilter);
 jfc.setMultiSelectionEnabled(false);
 jfc.showOpenDialog(this);
 //mengambil path file
 String path = jfc.getSelectedFile().getAbsolutePath();
 textPath.setText(path);
 }

 private void loadData(){
   connection = KoneksiMySQL.getConnection();
   Statement statement = null;
   String path = validatePath(textPath.getText().toString());
   final String delimiter = ";";
   final String query = "LOAD DATA INFILE '"+path+"' INTO TABLE LOADDATA FIELDS TERMINATED BY '"+delimiter+"'"
   + "LINES TERMINATED BY '\n' (NAMA, NIM);";
   try {
     statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
     statement.executeUpdate(query);
     JOptionPane.showMessageDialog(null, "Sukses");
   } catch (SQLException ex) {
     System.out.println(ex.getMessage());
  }
 }

 private String validatePath(String invalidPath){
 String validPath;
 validPath = invalidPath.replace('\\', '/');
 return validPath;
 }
 // Variables declaration - do not modify
 private javax.swing.JButton buttonBrowse;
 private javax.swing.JButton buttonLoad;
 private javax.swing.JLabel jLabel1;
 private javax.swing.JTextField textPath;
 // End of variables declaration
}

Pada method browseCSV, terdapat file Choser dan File Filter untuk memilih file CSV yang mengandung data yang akan dimasukkan ke MySQL, kemudian PATH akan ditampilkan ke TextField.

...
 jfc.addChoosableFileFilter(fileFilter);
 jfc.setMultiSelectionEnabled(false);
 jfc.showOpenDialog(this);
 //mengambil path file
 String path = jfc.getSelectedFile().getAbsolutePath();
 textPath.setText(path);
...

Pada mothod loadData, karakter (\) direplece dengan (/) dengan methid validatePath. Gunakan query LOAD DATA INFILE … untuk meload data dari sebuah file CSV dan delimiter (pembatas) antar field adalah titik koma (;).

...
String path = validatePath(textPath.getText().toString());
 final String delimiter = ";";
 final String query = "LOAD DATA INFILE '"+path+"' INTO TABLE LOADDATA FIELDS TERMINATED BY '"+delimiter+"'"
 + "LINES TERMINATED BY '\n' (NAMA, NIM);";
...

//validate Path
...
private String validatePath(String invalidPath){
 String validPath;
 validPath = invalidPath.replace('\\', '/');
 return validPath;
 }
...

Query LOAD DATA INFILE ‘”+path+” ‘ … menjelaskan bahwa kita mengambil path file dari file Choser. Sedangkan TERMINATED BY … menjelaskan bahwa field dipisahkan dengan sebuah delimiter (titik koma). LINE TERMINATED … menunjukkan bahwa pergantiaan baris (LINE) menggunakan Enter (\n). Dan yang terakhir adalah … (NAMA, NIM)… menunjukkan bahwa field yang akan diload akan dimasukkan ke dalam field NAMA dan NIM pada table database.

MainProgram.java


/*
 * Copyright(c) 2010
 * pizaini.wordpress.com
 */
package loaddatacsvmysql.com.pizaini.fx;
/**
 *
 * @author Pizaini
 */
public class MainProgram {

 /**
 * @param args the command line arguments
 */
 public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new FrameBrowse().setVisible(true);
}
});
 }
}

Running The Program…..Browse file CSV yang akan diload dan klik Load Data to MySQL untuk memasukkan data ke database….Jika muncul pesan sukses, maka data telahs sukses diload. Lihat pada table database untuk melihat hasilnya.

Mudah bukan …!

Download NetBeans Project untuk Load Data CSV to MySQL.

  1. 31 May 2014 at 10:12

    Maaf mau tanya, saya ada erorr mas.
    Data truncation: Data too long for column ‘jalur_masuk’ at row 1
    maksudnya apa ya? sedangkn di delimeternya sy sdh ganti “;” mjd “,” soale punya sy pke” ,”
    mhn bantuannya

    • 31 May 2014 at 21:38

      biasanya kalo pesan error “Data too long for column …”, karna panjang data di database tidak bisa sesuai dengan panjang data yang mau disimpan.

      • 1 June 2014 at 14:03

        Oh ya, mau tanya lg mas sy load data .csv k mysql kn, tp ketika tampil di DB kok datanya kepotong ya mas, yg kepotong tu di data gaji mas yaitu Rp.500.000,00 s.d 1000.000,00 ; kalau sy cb hapus koma yg ada di data itu , datanya gk kepotong.bgmn mas lo tetap pke Rp.500.000,00 s.d 1000.000,00 tapi gk kepotong?

      • 2 June 2014 at 20:31

        datanya bisa diatur dulu di MS Excel mas, menjadi number.. klo masih dalam bentuk string, tetap terbaca koma nya dan terpotng, karna pemisahnya koma.. lagipula, di database biasanya data gaji disimpan dalam bentuk integer (nominal)….

  2. Fadli
    1 October 2013 at 09:23

    oh iyak gan,
    mau nanya lagi,
    koq ada tanda petik (“) diawal field dan diakhir field yakk saat masuk ke mysql nya,
    mohon pencerahannya,
    makasih ~

  3. Fadli
    1 October 2013 at 09:12

    keren gan tutornya,
    mau nanya nih gan,
    kalo ada field yang tipe data-nya date gimana gan di load ke mysql-nya,
    mohon pencerahannya gan,
    makasih ~

    • 1 October 2013 at 09:55

      kalo type data nya date, tinggal disesuaikan dengan type data di mysql.
      Klo di MySQL format date nya yyyy-mm-dd, kalo di excel format standarnya mm/dd/yyyy atau format lainnya.

      Tinggal di ganti aja format nya gan… berez tuh…

      • Fadli
        1 October 2013 at 11:24

        maksudnya gan,
        cara nge-parse string ke date yang dari file csv ke database-nya,

        satu lagi nih gan,
        koq ada tanda petik (“) yakk pas udah udah di load di database-nya,
        maaf ngerepotin,
        makasih ~

  4. alan
    19 February 2013 at 02:25

    saya erorr mas.
    Data truncation: Data too long for column ‘kode’ at row 1
    maksudnya apa ya?

    • alan
      19 February 2013 at 02:31

      owh di delimeternya mas ternyata , kalau di saya make ” , ” bukan ” ; “

      • 19 February 2013 at 21:54

        iya mas, saya pake “;”…

  5. wawan
    31 July 2012 at 20:56

    keren…🙂

    kalo aplikasi diatas di embed ke html / jsp bisa ga ya?
    kalo bisa bagi source code-nya dong.. terima kasih🙂

    • 1 August 2012 at 15:46

      kalo di embade di JSP bisa…tentu dengan sedikit modifikasi dan diintegrasikan sesuai dengan JSP nya….
      btw, link source code ada di bagian bawah artikel…trims….

  6. fin
    17 June 2011 at 14:32

    mas saya udah coba berhasil klo mysql servernya di localhost tp klo ke servernya kyae ga bisa,
    btw thanks sharingnya..

    • Zhein
      17 June 2011 at 14:41

      sama2…
      moga bermanfaat

  7. zaqi
    15 March 2011 at 17:29

    Mas klo datanya 1 juta record lebih bisa? & ada 23 field

    • Zhein
      16 March 2011 at 10:04

      kalo datanya sebanyak itu bisa aja langsung di load..
      lebih baik di bagi menjadi beberapa bagian. jadi sekali load, msalnya kita cukup meload data skitar ribuan record saja, agar record nya gak terlewat waktu di import. pengalaman saya pernah meload data sekitar 500 record dan berhasil koq…

  8. craz
    23 February 2011 at 22:32

    tanks mas….

    mau tak coba tak praktekin..

    • Zhein
      24 February 2011 at 18:46

      sama2 gan…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: