Solved

Java program to import data into SQL not working

Posted on 2010-11-10
20
383 Views
Last Modified: 2013-11-23
Hi Experts,
I don't know if anybody can help but an ex-colleague has made a program in java (jar file)  to input data from a CSV file into an SQL database, but its stopped working.   I've looked through it but I'm not familiar with Java so I'm not sure what could be wrong.  Its opening the file and reading the data lines, but then it comes up with 'error reading line....' for every line.
Can anybody give me a clue?
Thanks
0
Comment
Question by:GracefBase
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 2
  • +3
20 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 34100471
You can import csv directly - you don't need a Java program. Unless the data need preparation. Please post error
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 34100562
may be the escape characters are not read properly... post the exception stack!
0
 

Author Comment

by:GracefBase
ID: 34100904
The program itself seems to be running fine - it logs in and out and runs the program but its just not doing what it should.  I don't know whether someone can tell me whether the csv is correctly formatted as the program is expecting,
please see attached csv file.
I have attached the part of the program called Datascanner as this is where the error message is from (error reading line...), Thank you

DATASCANNER:

package podchecker;

   import java.util.Scanner;
   import java.io.File;
   import java.io.FileNotFoundException;
   import java.util.*;
   import java.math.BigDecimal;
   public class DataScanner {

     public static Set<Consignment> readFile(File fileName, OutputBox aOut) {
       Set<Consignment> consignments = new TreeSet<Consignment>();
       OutputBox out = aOut;
       int lineNo = 0;
       try {
         Scanner scanner =
           new Scanner(fileName);
         scanner.useDelimiter
           (System.getProperty("line.separator"));
         while (scanner.hasNext()) {
           lineNo ++;
           try
           {
              consignments.add(parseLine(scanner.next()));
           }
           catch (NumberFormatException e) {
                out.print("Error reading line " + lineNo + ". ");
           }
         }
         scanner.close();
       } catch (FileNotFoundException e) {
         out.print("Could not open file.");
         e.printStackTrace();
       }
       return consignments;
     }

     private static Consignment parseLine(String line) throws NumberFormatException {
       Scanner lineScanner = new Scanner(line);
       lineScanner.useDelimiter(",");
       lineScanner.next();
       String connumber = lineScanner.next().replace('"', ' ');
       String mridText = removeChar(lineScanner.next());
       int mrid = Integer.parseInt(mridText);
       lineScanner.next();
       lineScanner.next();
       lineScanner.next();
       lineScanner.next();
       lineScanner.next();
       int boxes = Integer.parseInt(removeChar(lineScanner.next()));
       float weight = Float.valueOf(removeChar(lineScanner.next()));
       String podName = "", podDate = "", podTime = "";
       if (lineScanner.hasNext()) { lineScanner.next(); }
       if (lineScanner.hasNext()) { podName = removeChar(lineScanner.next()); }
       if (lineScanner.hasNext()) { podDate = removeChar(lineScanner.next()); }
       if (lineScanner.hasNext()) { podTime = removeChar(lineScanner.next()); }
       return new Consignment(mrid, connumber, podDate, podTime, podName, weight, boxes);
     }
     public static String removeChar(String s) {

        String r = "";
        char c = '\"';
        for (int i = 0; i < s.length(); i ++) {
            if (s.charAt(i) != c) r += s.charAt(i);
        }

        return r.trim();
     }
   }
Consignor-Report.csv
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 86

Expert Comment

by:CEHJ
ID: 34101086
>>...this is where the error message is from (error reading line...),

You still haven't posted that
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 34101282
you may want to use some CSV reader instead of fixing it yourself! check open CSV, its simple and you can parse csv into bean!
0
 
LVL 16

Expert Comment

by:Valeri
ID: 34101352
>> but its just not doing what it should.
what it should do, but it's not doiing it?
btw, the csv file looks ok
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34101463
>>you may want to use some CSV reader instead of fixing it yourself!

You should do, since if a comma is introduced into any of the fields (quite legitimate in csv) it will break your parser
0
 
LVL 92

Accepted Solution

by:
objects earned 500 total points
ID: 34107170
your data file doesn't match what your code is expecting

>     int boxes = Integer.parseInt(removeChar(lineScanner.next()));

thats expecting an int in the 9th field which is not the case
looks like an extra line has been added to the csv, so try adding a line to read an extra field

    lineScanner.next();   // <---add this
    int boxes = Integer.parseInt(removeChar(lineScanner.next()));

>     int mrid = Integer.parseInt(mridText);

also expects an int in the 3rd field which is not always the case
0
 
LVL 10

Expert Comment

by:Pramod Kumar
ID: 34109340
Could you please post below classes as well:

Consignment
OutputBox
0
 

Author Comment

by:GracefBase
ID: 34132655
Hi Objects,
Thanks for this, I'll try it.

Hi Pramodkrjsr,
Thanks for your question, the classes requested posted below

Consignment:
package podchecker;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.text.ParseException;

/**
 *
 * @author ronnie
 */

public class Consignment implements Comparable {
    private int mrid;
    private String connumber;
    private String deliveryDate;
    private String deliveryTime;
    private String podName;
    private Date deliveryDateTime;
    private float weight;
    private int boxes;
    private SimpleDateFormat format =
            new SimpleDateFormat("dd/MM/yyyy HH:mm");


    public Consignment(int aMrId, String aConNumber, String aDeliveryDate,
            String aDeliveryTime, String aPodName,
            float aWeight, int aBoxes)
    {
        mrid=aMrId;
        connumber = aConNumber.trim();

        deliveryDate = aDeliveryDate;
        deliveryTime = aDeliveryTime;
        if (!deliveryDate.equals(""))
        {
            try
            {

                deliveryDateTime = format.parse(deliveryDate + " " + deliveryTime);
            }
            catch(ParseException pe)
            {
                System.out.println(deliveryDate);
                System.out.println("ERROR: could not parse date in string \"" +
                    deliveryDate + " " + deliveryTime + "\"");
            }
        }
        podName = aPodName.trim();
        weight = aWeight;
        boxes = aBoxes;
    }
    public int getMrId()
    {
        return mrid;
    }
    public String getConnumber()
    {
        return connumber;
    }
    public String getDeliveryDate()
    {
        return deliveryDate;
    }
    public Date getDeliveryDateTime()
    {
        return deliveryDateTime;
    }
    public String getPodName()
    {
        return podName;
    }
    public float getWeight()
    {
        return weight;
    }
    public int getBoxes()
    {
        return boxes;
    }
    public boolean equals(Consignment other) {
        if (this.getMrId() != other.getMrId()) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "podchecker.Consignment[mrid=" + this.getMrId() + "]";
    }

    public int compareTo(Object anotherConsignment) throws ClassCastException {
    if (!(anotherConsignment instanceof Consignment))
      throw new ClassCastException("A Person object expected.");
    int anotherConsignmentMr = ((Consignment) anotherConsignment).getMrId();
    return this.getMrId() - anotherConsignmentMr;
  }


}

Output Box:
/*
 * OutputBox.java
 *
 * Created on 08-Jun-2010, 11:11:06
 */

package podchecker;
import org.jdesktop.application.SingleFrameApplication;
import java.awt.Color;

/**
 *
 * @author ronnie
 */
public class OutputBox extends javax.swing.JPanel {
    private final static String newline = "\n";

    /** Creates new form OutputBox */
    public OutputBox(SingleFrameApplication app) {
        initComponents();
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane1 = new javax.swing.JScrollPane();
        outBox = new javax.swing.JTextArea();

        outBox.setColumns(20);
        outBox.setEditable(false);
        outBox.setRows(5);
        jScrollPane1.setViewportView(outBox);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 400, Short.MAX_VALUE)
            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(layout.createSequentialGroup()
                    .addContainerGap()
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 380, Short.MAX_VALUE)
                    .addContainerGap()))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 300, Short.MAX_VALUE)
            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(layout.createSequentialGroup()
                    .addContainerGap()
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 278, Short.MAX_VALUE)
                    .addContainerGap()))
        );
    }// </editor-fold>//GEN-END:initComponents


    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTextArea outBox;
    // End of variables declaration//GEN-END:variables
    public void print(String text)
    {
        this.outBox.append(text + newline);
    }
    public String getLog()
    {
        return this.outBox.getText();
    }
}
0
 

Author Comment

by:GracefBase
ID: 34281823
Sorry everyone I've not been able to get down to site to check this yet.
Does anybody know of a good course or book to learn how the Java programming works?

Thanks
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34281835
0
 

Author Comment

by:GracefBase
ID: 34285154
Thanks a lot CEHJ
0
 

Author Comment

by:GracefBase
ID: 34489383
Hi experts thanks for help thus far, adding the extra line fixed the problem when I tested it in Netbeans, but silly question how do I save this new configuration as the jar file and do I have to give the users all the classes folders etc as well?  I went to run  and pressed clean and build new project.  I then saved the whole folder, but the users are still getting the error so what do I need to do?

Thanks a lot

Grace
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34489753
Can you please post current error
0
 

Author Comment

by:GracefBase
ID: 34489821
The same error as before 'error reading line ...' for every line.  
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34490001
You need to post the actual, full, exception stack trace
0
 

Author Comment

by:GracefBase
ID: 34491996
According to the Netbeans there is not a problem when I run this it says completed successfully.  The error message is in the GUI.  I just want to know what to do to be able to distribute this changed program to the users?
Thanks
0
 

Author Comment

by:GracefBase
ID: 34492860
Sorry CEHJ I think the user was using the old folder still not the new one!  I couldnt test it as my java on my PC stopped working!
I'll allocate the points you are a star thanks for your help
0
 

Author Comment

by:GracefBase
ID: 34492880
Sorry points to Objects who actually posted the solution.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

726 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