Skip to main content

Importing Excel data to PostgreSQL

When it comes to transfer data from excel worksheet to PostgreSQL, the task is not that easy.
I tried to export file to CSV but it doesn't work because there were some columns which were not containing the data!!!
And even one problem exist, Column type. In Java using ResultSet I successfully fetched data from excel as ODBC database but in some column where it contains text in some rows while in some rows it contained numbers!! So Java 'getString(column)' method did not worked!!
So finally I imported that excel file into MS-Access and it worked, and then from there I read that data and inserted into PostgreSQL.
To fetch column from excel file as ODBC backend, following code will be useful.
select * from [WorksheetName$]
To fetch only some data
select * from [WorksheetName$A7:F10]
or
select First_row_data from [WorksheetName$]
or if your excel file contains data in all columns then you can export it as CSV file and then can use PostgreSQL's COPY command.

e.g.
COPY FROM FILE 'c:/data/excel.csv' USING DELIMITERS ',';

Comments

  1. Hi Sachin
    Here is one more Way

    /*This Is Directly data save into database Program is Partialy Run.But Some versions may be not Run.*/
    public class CSVFileSave{

    public static void main (String[] args){
    Connection conn =null;
    try {
    Class.forName("com.mysql.jdbc.Driver");

    conn = DriverManager.getConnection("jdbc:mysql://192.168.1.150:3306/TestDB","dev","");

    conn.setAutoCommit(false);
    System.out.println(conn.isClosed());
    Statement stmt = conn.createStatement();

    String filename = "C:\\Documents and Settings\\SS2\\Desktop\\ProductList.csv";
    System.out.println("LOAD DATA INFILE '" +filename+ "' INTO TABLE CSVTest FIELDS TERMINATED BY ','");
    stmt.executeUpdate("LOAD DATA INFILE '" +filename+ "' INTO TABLE CSVTest FIELDS TERMINATED BY ','");

    System.out.println("THE DATA IS INSERTED PROPERLY ....!!!!");

    conn.close();

    } catch (Exception e) {
    e.printStackTrace();
    }
    finally{
    //conn.close();
    System.out.println("Finally the DATA ....!!!!");
    try {
    if(conn != null)
    conn.close();
    } catch(SQLException e) {}
    }

    }

    }//class end

    ReplyDelete
  2. Thanks Raj for this information.

    ReplyDelete

Post a Comment

Popular posts from this blog

Spring AOP : JDK Proxy vs CGLib Proxy

Spring framework is one of the most widely used framework in Enterprise application development. It has so many features such as Dependency injection, Data access integration, MVC, AOP which takes care of most of the boilerplate part of project, and developers can then focus on business logic only.

One of the important feature in Spring is AOP. It is used by almost every enterprise application which is being developed using Spring.

AOP So what is AOP? Definition of AOP -
Aspect oriented programming is a programming paradigm which aims to increase modularity by allowing the separation of cross cutting concerns. Well, my understanding of AOP is - AOP allows us to introduce/join new modules in your project at pre-specified dynamic locations without having to code for it. Traditional examples of AOP is Transaction management, logging etc. You can read more about AOP at here.

This article is for those who know AOP, have used AOP in their projects but do not know how Spring or other DI fra…

Java Tools Plugin for Notepad++

This post and the plugin is outdated. Please use plugin from this link which is updated and allows customization of shortcut key mappings and much more.
Notepad++ is a great free editor. I like notepad++ because its a light weight editor and loads instantly. I use notepad++ for editing many files everyday. One plugin I always wanted was a plugin which lets me compile my java files and execute them from editor itself, but I couldn't find it so I wrote a myself. This plugin can be downloaded from this link.
I am working on this plugin to make it more effective, so that if user have more than one installations of JDK or JRE then user should be able to choose which one to use etc.
This is a simple plugin which has two commands-
Compile - compiles a java file.
Run - executes a java file.
For using this plugin Java must be installed on the system.

Notepad++ Compile and Run Java Programs

Notepad++ is a great file editor. It has many features. The most important feature which I like about Notepad++ is its light weight. It loads up so quickly, that's great. It also provides syntax highlighting for many languages. I use notepad++ to edit general files as well as my simple Java programs.
Although notepad++ provides functionality to run external programs, I prefer NppJavaTools plugin to compile and run Java programs using notepad++.
You can download NppJavaTools plugin from this page - NppJavaTools.

Installation
Installation of plugins in notepad++ is very simple process. All you have to do is copy plugin dll into plugins folder of notepad++ installation directory and restart the notepad++.

Features
This plugin provides following features,


Compile and run your Java files within Notepad++Set custom hotkeys for compiling and running Java CodeLibrary support Compile and RunThis function allows you to run your Java programs to compile and run from Notepad++. Set Custom hotkeysThi…