Java: Opening a zip file and parsing a csv file

[webster_boy]

Hi,

I need to read a zip file, which contains a csv file (i.e. Excel) using Java.

Does anyone have examples of opening a zip file and reading a csv (spreadsheet) file so that I can save the data to a database table???

Thanks.

W

[webster_boy]

I also noticed that when I create a ZipFile object the constructor accepts a FileName. I take it I must put in the path name as well as the file???

When I do put a path in it, it does not seem to like the '\' symbol. How do I get round this.

[manjunathkk]

Try putting the path as , for eg:

c:\\dir1\\zipfile.zip

[sjalle1]

I've ripped & plucked this from an old project of min, w. afew mods. It unzips but does not parse spreadsheets unless they are stored as tab-delimited text files (and there is no db-store functionality)

import javax.swing.*;
import java.awt.*;
import java.util.*;
import java.util.zip.*;
import java.io.*;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;

/***********************************************************************
 * This class only works for tabs/newline delimited stringbuffers, like
 * the format delivered by excel when worksheets are saved as
 * tab-delimited text files.
 * Parses string buffer as newline-delimited strings.  Each lineString
 * is parsed as a tab-delimited data-row (of strings).
 */

class ColumnParser {
  String [] columnNames=null;
  ArrayList list=new ArrayList();
  /**
   * Whole parse operation is done by constructor
   * @param content, the stringbuffer
   */
  public ColumnParser(String content) {
    try {
      BufferedReader bR = new BufferedReader(new StringReader(content));
      String line = bR.readLine();
      int lineCount = 0;
      do {
        if (lineCount == 0) {
          columnNames = getRowData(line);
        } else {
          String [] dataRow = getRowData(line);
          list.add(dataRow);
        }
        lineCount++;
      } while ( (line = bR.readLine()) != null);
    } catch (Exception ex) {
      JOptionPane.showMessageDialog(new JFrame(),"Decode failed:"+ex.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
      ex.printStackTrace();
    }
  }
  /**
   * Parses a line of data as a tab-delimited string.
   * @param line
   * @return
   */
  private String [] getRowData(String line) {
    StringTokenizer st=new StringTokenizer(line,"\t",false);
    int n=st.countTokens();
    String [] row=new String[n];
    int cn=0;
    while (st.hasMoreElements()) {
      row[cn++]=((String)st.nextElement()).trim();
    }
    return row;
  }
  /**
   * Matrix columns count
   * @return int count
   */
  public int getColumnCount () { return this.columnNames.length; }
  /**
   * Column names, (first line in stringbuffer)
   * @return rowdata as String []
   */
  public String [] getColumnNames () { return this.columnNames; }
  /**
   * Returns Table data matrix as a string matrix.
   * Columns names are not included.
   * @return data matrix as String [][]
   */
  public String [][] getDataMatrix() {
    String data [][] = new String[list.size()][this.getColumnCount()];
    for (int i=0;i<list.size();i++) {
      data [i]=(String [])list.get(i);
    }
    return data;
  }
}

/***********************************************************************
 * Convenience extension, only basename of zipfile entry
 * is shown in jlist.
 */

class CVSEntry extends java.util.zip.ZipEntry   {
  public CVSEntry (ZipEntry zEnt) {
    super(zEnt);
  }
  public String toString() {
    String path=this.getName();
    int ix=path.lastIndexOf("/");
    if (ix < 0) return path;
    return path.substring(ix+1);
  }
  public ColumnParser getParser(String sBuf) {
    return new ColumnParser(sBuf);
  }
}

/***********************************************************************
 * The zipfile framewindow
 */

public class ZipToDB extends JFrame implements ActionListener, ListSelectionListener  {
  String zipFilePath=null;

  BorderLayout borderLayout1 = new BorderLayout();
  JPanel jPanel1 = new JPanel();
  JScrollPane contScroll = new JScrollPane();
  JButton closeBtn = new JButton("Close");
  JButton getZipBtn = new JButton("Get Zip File");
  JButton storeDBBtn = new JButton("Store");
  JTextArea contentTA = new JTextArea();
  JScrollPane entryScroll = new JScrollPane();
  JList entryList = new JList();
  JCheckBox decodeAsCVSCBX = new JCheckBox();
  JButton clearBtn = new JButton();
  JLabel entryLbl = new JLabel();

  /**
   * Main, just show the ZipToDB frame
   * @param args
   */
  public static void main(String[] args)  {
    ZipToDB zz = new ZipToDB("CVS Extraction And Store");
    zz.setBounds(20,20,800,400);
    zz.setVisible(true);
  }
  /**************************************************
   ********************* CONSTRUCTOR ****************
   *
   * @param title
   */
  public ZipToDB(String title) {
    super(title);
    jbInit();
  }
  /**
   * Show zipfile entries in a jlist
   * @throws Exception
   */
  private void showEntries () throws Exception {
    ZipFile zF=new ZipFile(new File(this.zipFilePath));
    Enumeration enum = zF.entries();
    Vector listVect=new Vector();
    while (enum.hasMoreElements()) {
      CVSEntry zEnt = new CVSEntry((ZipEntry)enum.nextElement());
      // ************ include this test to filter the zipentries
      //if (!zEnt.getName().toLowerCase().endsWith(".cvs")) continue;
      listVect.addElement(zEnt);
    }
    this.entryList.setListData(listVect);
  }
  /**
   * Unzip an entry
   * @param zEnt
   * @param parseCVS, parses unsipped buffer for row/column values
   * @throws Exception
   */
  private void unzipData (CVSEntry zEnt, boolean parseCVS) throws Exception {
    ZipFile zF=new ZipFile(new File(this.zipFilePath));
    InputStream in= zF.getInputStream(zEnt);
    byte [] b=new byte[in.available()];
    in.read(b);
    in.close();
    if (parseCVS) {
      // only displays column names in textArea....
      ColumnParser cp = zEnt.getParser(new String(b));
      String[] cNames = cp.getColumnNames();
      for (int i = 0; i < cNames.length; i++) {
        this.contentTA.append(cNames[i] + "\r\n");
      }
    } else {
      this.contentTA.setText(new String(b));
    }
  }
  /**
   * Respond to jlist selections
   * @param e
   */
  public void valueChanged(ListSelectionEvent e) {
    if (e.getValueIsAdjusting()) {
      CVSEntry zEnt=(CVSEntry)this.entryList.getModel().getElementAt(this.entryList.getSelectedIndex());
      try {
        entryLbl.setText(this.zipFilePath+" : "+zEnt.toString());
        unzipData(zEnt,decodeAsCVSCBX.isSelected());
      } catch (Exception ex) {
        JOptionPane.showMessageDialog(this,"Unzip failed:"+ex.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
        ex.printStackTrace();
      }
    }
  }
  /**
   * Initialize components etc.
   */
  private void jbInit()  {
    this.getContentPane().setLayout(borderLayout1);

    closeBtn.addActionListener(this);
    getZipBtn.addActionListener(this);
    storeDBBtn.addActionListener(this);
    clearBtn.addActionListener(this);

    contentTA.setToolTipText("");
    contentTA.setText("");
    contentTA.setColumns(2);
    entryScroll.setPreferredSize(new Dimension(250, 132));
    decodeAsCVSCBX.setText("Decode as CVS");
    clearBtn.setText("Clear");
    entryLbl.setHorizontalAlignment(SwingConstants.CENTER);
    entryLbl.setHorizontalTextPosition(SwingConstants.LEFT);
    entryLbl.setText("(no selection)");
    jPanel1.add(decodeAsCVSCBX, null);
    this.getContentPane().add(jPanel1, BorderLayout.SOUTH);
    jPanel1.add(closeBtn, null);
    jPanel1.add(getZipBtn, null);
    jPanel1.add(clearBtn, null);
    jPanel1.add(storeDBBtn, null);
    this.getContentPane().add(contScroll, BorderLayout.CENTER);
    this.getContentPane().add(entryScroll, BorderLayout.WEST);
    this.getContentPane().add(entryLbl, BorderLayout.NORTH);
    entryScroll.getViewport().add(entryList, null);
    contScroll.getViewport().add(contentTA, null);
    this.entryList.addListSelectionListener(this);

  }
  /**
   * Respond to button clicks
   * @param e
   */
  public void actionPerformed(ActionEvent e) {
    String cmd=e.getActionCommand();
    if (cmd.equals("Close")) {
      System.exit(0);
    } else if (cmd.equals("Get Zip File")) {
      FileDialog fd=new FileDialog(this,"Select Zip File",FileDialog.LOAD);
      fd.setVisible(true);
      String fileName=fd.getFile();
      if (fileName==null) return;
      this.zipFilePath=fd.getDirectory()+fileName;
      try {
        showEntries();
        entryLbl.setText(this.zipFilePath);
      } catch (Exception ex) {
        JOptionPane.showMessageDialog(this,"Entry List failed:"+ex.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
        ex.printStackTrace();
      }
    } else if (cmd.equals("Store")) {
      JOptionPane.showMessageDialog(this,"Not implemented, but data is available in\r\nthe object matrix: Object [][] data"+
                                    "\r\n(When implemented properly in the ColParser class)" ,
                                    "Do it yourself", JOptionPane.INFORMATION_MESSAGE);
    } else if (cmd.equals("Clear")) {
      this.contentTA.setText("");
    }



  }

}