Solved

Java program to import data into SQL not working

Posted on 2010-11-10
20
377 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now