• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Java program to import data into SQL not working

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
GracefBase
Asked:
GracefBase
  • 9
  • 6
  • 2
  • +3
1 Solution
 
CEHJCommented:
You can import csv directly - you don't need a Java program. Unless the data need preparation. Please post error
0
 
ksivananthCommented:
may be the escape characters are not read properly... post the exception stack!
0
 
GracefBaseAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
CEHJCommented:
>>...this is where the error message is from (error reading line...),

You still haven't posted that
0
 
ksivananthCommented:
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
 
ValeriCommented:
>> 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
 
CEHJCommented:
>>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
 
objectsCommented:
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
 
Pramod KumarCommented:
Could you please post below classes as well:

Consignment
OutputBox
0
 
GracefBaseAuthor Commented:
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
 
GracefBaseAuthor Commented:
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
 
CEHJCommented:
0
 
GracefBaseAuthor Commented:
Thanks a lot CEHJ
0
 
GracefBaseAuthor Commented:
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
 
CEHJCommented:
Can you please post current error
0
 
GracefBaseAuthor Commented:
The same error as before 'error reading line ...' for every line.  
0
 
CEHJCommented:
You need to post the actual, full, exception stack trace
0
 
GracefBaseAuthor Commented:
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
 
GracefBaseAuthor Commented:
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
 
GracefBaseAuthor Commented:
Sorry points to Objects who actually posted the solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 9
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now