aspose file tools*
The moose likes Struts and the fly likes reading CSV file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Struts
Bookmark "reading CSV file" Watch "reading CSV file" New topic
Author

reading CSV file

a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Hi All,

I try to upload one CSV file from JSP and read in my Action Class and store in DB. While im doing that im getting error like "java.util.NoSuchElementException." and this error im getting while reading first line. still it didnt go to end of the first line.

If anyone having sample code please post it here.

FormFile myFile = myForm.getThefile();
String contentType = myFile.getContentType();
String fileName = myFile.getFileName();
int fileSize = myFile.getFileSize();
byte[] fileData = myFile.getFileData();


bcuz i couldnt attach either .CSV or .TXT, here im posting my file details:

account.main_card, account.name, account.agreement_status, account.cycle_day, account.trn_balance.date, account.trn_balance.amt, account.last_statement_date, account.last_statement_amt, account.phone.mobile, account.phone.home, account.phone.permanent, account.phone.fax, account.phone.office, account.opening_date, account.cancel_date, account.total_min_pyt, account.credit_limit, account.current, account.days_x, account.days_30, account.days_60, account.days_90, account.days_120, account.days_150, account.days_180, account.days_210, account.old_ic, account.ic, account.employer, account.address.home.address, account.address.home.address2, account.address.home.postcode, account.address.home.city, account.address.home.state, account.address.alternate.address, account.address.alternate.address2, account.address.alternate.postcode, account.address.alternate.city, account.address.alternate.state, account.address.other.address, account.address.other.address2, account.address.other.postcode, account.address.other.city, account.address.other.state, account.address.mail.address, account.address.mail.address2, account.address.mail.postcode, account.address.mail.city, account.address.mail.state, account.comm_rate, account.abort_date, account.overdue_min_pyt, account.total_due, account.last_collection_date, account.due_date, account.product_code

4509200001383896,ALEXANDER BEH BOON HOCK,15,23,,8881.43,23-07-2009,8893.99,0162626264,0392850936,,,0342962626,,,,0.00,0.00,0.00,820.00,0.00,0.00,0.00,0.00,0.00,0.00,,830222075343,C/O ALEXANDER BEH BOON HOCK FIRST IMPRESSION TRAVEL & TOURS SB ,17 LORONG BURUNG BAYAN 2,TAMAN BUKIT MALURI,52100,KUALA LUMPUR,WILAYAH PERSEKUTUAN ,,,,,,C/O ALEXANDER BEH BOON HOCK FIRST IMPRESSION TRAVEL & TOURS SB ," AY-2-8 AYERS TOWER JLN KOSAS UTAMA ,TAMAN KOSAS",68000,AMPANG,SELANGOR,17 LORONG BURUNG BAYAN 2,TAMAN BUKIT MALURI,52100,KUALA LUMPUR,WILAYAH PERSEKUTUAN ,14%,24-08-2009,1470.00,2095.00,23-08-2009,,PC30

5218870000188436,BEK THIAM LEONG,15,25,,5391.79,25-07-2009,5391.79,0162226606,0389442793,,,0389613866,,,,0.00,0.00,0.00,257.00,251.00,0.00,0.00,0.00,0.00,0.00,7336887,640529107875,C/O BEK THIAM LEONG SPEED DIMENSION (M) SDN BHD ,NO 3 JALAN PP 8,"TAMAN PINGGIRAN PUTRA ,SEKSYEN 2",43300,SERI KEMBANGAN,SELANGOR,NO 3 JALAN PP 8,TAMAN PINGGIRAN PUTRA SEKSYEN 2,43300,SERI KEMBANGAN,SELANGOR,C/O BEK THIAM LEONG SPEED DIMENSION (M) SDN BHD , LOT 6594 KAW PERINDUSTRIAN KG BARU BALAKONG,43300,SERI KEMBANGAN,,NO 3 JALAN PP 8,"TAMAN PINGGIRAN PUTRA ,SEKSYEN 2",43300,SERI KEMBANGAN,SELANGOR,14%,26-08-2009,772.00,1042.00,25-08-2009,,PC60

4509190000487212,CH'NG KIAN YONG,15,18,,8088.08,18-07-2009,8093.79,0123698608,069533458,,,,,,,0.00,0.00,0.00,892.00,0.00,0.00,0.00,0.00,0.00,0.00,A2687516,740410016173,,457 JALAN SRI 26,TAMAN SRI BAKRI 3,84200,MUAR,JOHOR,457 JALAN SRI 26,TAMAN SRI BAKRI 3,84200,MUAR,JOHOR,,,,,,457 JALAN SRI 26,TAMAN SRI BAKRI 3,84200,MUAR,JOHOR,14%,19-08-2009,1529.00,2145.00,18-08-2009,,PC30

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,


Note: i inserted one line space for each line for clarity, but in actual file there is no space like that.

thank you in advance,
kishore

[AG: edited a line that was screwing the layout]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
Post the complete stack trace and the code that causes it; be sure to indicate the statement where the exception occurs.

In general, I recommend to use a ready-made library for reading/writing CSVs. The http://faq.javaranch.com/java/AccessingFileFormats page lists several such projects.
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Hi Ulf,

thanks for replying. Here is how i read my CSV and sstore to DB. Now after i change my code a little i get the NullPointer error: java.lang.NullPointerException

FormFile myFile = myForm.getThefile();
String contentType = myFile.getContentType();
String fileName = myFile.getFileName();
int fileSize = myFile.getFileSize();
byte[] fileData = myFile.getFileData();
System.out.println("contentType: " + contentType);
System.out.println("File Name: " + fileName);
System.out.println("File Size: " + fileSize);

String strFileContent = new String(fileData);
System.out.println("File content : ");
//System.out.println(strFileContent);
String token1 = null;
String token2 = null;

StringTokenizer st1 = new StringTokenizer(strFileContent,"\n");
while(st1.hasMoreTokens())
{
token1 = st1.nextToken("\n");
System.out.println("token1: "+token1);
StringTokenizer st2 = new StringTokenizer(token1,",");
while(st2.hasMoreTokens())
{
//token2 = st2.nextToken(",");
//System.out.println("token2: "+token2);
pstmt.setString(1, st2.nextToken(",").trim());
pstmt.setString(2, st2.nextToken(",").trim());
pstmt.setString(3, st2.nextToken(",").trim());
pstmt.execute();
}
}

Am i doing correctly? Can you explain what went wrong?
or better to use ready-made library only?

please suggest

thanks,
kishore
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
Ulf Dittmer wrote:Post the complete stack trace and the code that causes it; be sure to indicate the statement where the exception occurs.

Also, if there's a NullPointerException, tell us which object is null.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
StringTokenizer st1 = new StringTokenizer(strFileContent,"\n");

This is incorrect. CSV files can contain newlines in the middle of a row. That's why I advise to use a CSV library in which all the file format rules are coded.

Also, I'm fairly certain that you want to use "nextElement()" instead of "nextToken("\n")".
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
NullPointer was with my Connection object, i fixed that. Now im getting like:
java.util.NoSuchElementException
java.util.StringTokenizer.nextToken(StringTokenizer.java:332)
java.util.StringTokenizer.nextToken(StringTokenizer.java:360)
com.myapp.struts.FileUploadAction.execute(FileUploadAction.java:76)

But the first line of my CSV file elements are stored EXCEPT last 2 elements in it.

Am I clear? or making a mess?

thank you,
kishore
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
The code contains multiple nextToken statements; if you don't tell us which one is causing the exception then it's very hard to help you.

nextToken(",")

This is also incorrect. You seem to assume that commas can not occur within a cell, but they most certainly can. Again, using a CSV library avoids this problem, as well as some lingering other ones that your code does not address.
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Sorry Ulf,

I dont understand where and why that exception is coming from. that is why im asking your suggestion.

My actual file processing is this:

1. Upload a CSV file from JSP(using struts)
2. In Action Class i read that CSV and store into DB.

when i select and click upload button in jsp, im getting this error in jsp page.

I dont know how to explain you more. My code and my CSV file content, i already posted.
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
little more adding to that,

in my DB until the element "account.last_collection_date" is storing. but actually i want to skip this first line while storing.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
I dont understand where and why that exception is coming from.

The first step is to find out the "where". Then you can start thinking about the "why".

If you're unfamiliar with debuggers (which would point to the exact location of the problem), then the easy solution to this is to add lots of System.out.println statements to your code, before and after each statement that you suspect might cause the problem. That way you can determine in which line of code the problem occurs.

One problem i see is that there's a cell with this content: "TAMAN PINGGIRAN PUTRA ,SEKSYEN 2" In a CSV, that's a single cell, but your code treats it as two cells (because it assumes that a comma separates two cells). Once again, you're much better off using a ready-made library than to code all CSV rules yourself; CSV is not as simple as it looks at first.
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
yes, im following the same now.

i put S.O.P in my code and it is like this now.
while(st1.hasMoreTokens())
{
System.out.println("in while.....1");
token1 = st1.nextToken("\n");
System.out.println("token1: "+token1);
StringTokenizer st2 = new StringTokenizer(token1,",");
while(st2.hasMoreTokens())
{
System.out.println("in while....2");
//token2 = st2.nextToken(",");
//System.out.println("token2: "+token2);
pstmt.setString(1, st2.nextToken(",").trim());
pstmt.setString(2, st2.nextToken(",").trim());
pstmt.setString(3, st2.nextToken(",").trim());
pstmt.execute();
System.out.println("end of while....2");
}
System.out.println("end of while.....1");
}

Error is same: java.util.NoSuchElementException

what i observed is, once control entered in while..1 and then entered in while..2.
then in while..2, it loops like

in while....2
end of while....2

for 18 times and final SOP is
in while....2

I feel that control entered inner while and struck there. Is that correct?

thanks

Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
Are you sure that the number of cells in each row is divisible by 3? I'm asking because you're calling "nextToken" 3 times for each call to "hasMoreTokens". That's bad practice (and causes the exception in this case) - you should call "hasMoreTokens" before each call to "nextToken".

You will also need to address the issue I mentioned in my previous post about cells being enclosed in double quotes.

So far you haven't responded to my various comments about using a CSV library; can I ask why that is? It's now obvious that your CSV-handling code needs to become much more elaborate before it'll work. Can you justify the time spent on that when free (and working) solutions are readily available?
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
You are absolutely correct. My cells are not exactly multiplied by 3. So it stuck in while2. Thank you.

About cells enclosed in double quotes, are you mentioning about the data in my CSV file?

Im not much aware of how CSV libraries work. I can make use of them if you suggest me a good one here.


thank you
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
And Ulf, how to skip the first row? bcuz i dont want to store first row elements in my DB.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
About cells enclosed in double quotes, are you mentioning about the data in my CSV file?

Yes, the example I mentioned was taken straight from the data example you posted.

Im not much aware of how CSV libraries work. I can make use of them if you suggest me a good one here.

My first post contained a link to a page that lists several such libraries.

And Ulf, how to skip the first row? bcuz i dont want to store first row elements in my DB.

In that case, just do an extra nextToken call right after creating st1 (before entering the loop). Be sure to guard it with a hasMoreTokens call.
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
ulf,

I tried this one. Is there any better way for skipping first row?

while(st1.hasMoreTokens())
{
st1.nextToken();
while(st1.hasMoreTokens())
{
System.out.println("in while.....1");
token1 = st1.nextToken("\n");
System.out.println("token1: "+token1);
StringTokenizer st2 = new StringTokenizer(token1,",");
while(st2.hasMoreTokens())
{
System.out.println("in while....2");
//token2 = st2.nextToken(",");
//System.out.println("token2: "+token2);
pstmt.setString(1, st2.nextToken(",").trim());
pstmt.setString(2, st2.nextToken(",").trim());
pstmt.setString(3, st2.nextToken(",").trim());
pstmt.execute();
System.out.println("end of while....2");
}
System.out.println("end of while.....1");
}
}
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
It will throw an exception if the input is empty, but apart from that it should work.
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Thank you alot Ulf,


I hope if anyone need to upload CSV and read in java, they can use my code.

i will come again soon with another problem.

thank you all,
kishore
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
I hope if anyone need to upload CSV and read in java, they can use my code.

Why would anyone do that when it's apparent that the code has several unresolved problems, and functional alternatives are readily available?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: reading CSV file