• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

import data from large csv file. Help.

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am working on a project where I have to import the data from a large (50meg) Excel file into a JavaDB.
First I converted the .xls file to a semicolon delimited .cvs file.

Originally I was using a buffered queue to read the file line by line.
Each line being a row.
Like this:

public String[] loadSpreadSheet(String filename){

try
{
String currentLine;
String temp[] = new String[1024];

BufferedReader buffReader = new BufferedReader(new FileReader(filename));

while((currentLine = buffReader.readLine() ) != null)
{
temp[] = currentLine.split(",") ;

/* debug */
for(int i = 1; i<= temp.length; i++){
System.out.println(temp[i]); // to check that the file was getting read correctly, it was.
}
/* /debug */
}
buffReader.close();
return temp;

catch etc.
}

So the function returns a string array which I woudl then parse through in another function to fill in
field names for a JavaDB (derby) database. Which would be done row by row until this is read in.

In the function that called this,
I would use this to input the text field data into an SQL query string


I realize this will take some time to do.
I was given the hint to read it in page by page.

So I wondered if there was a built in function that would do this instead and came up with this:


public void importData(Connection conn,String filename)
{
Statement stmt;
String query;

try
{
String[] dbString = this.loadSpreadSheet(filename);
stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

String importTableQuery = "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE" +
"(null, 'EXCELSPREADSHEET2007', 'filename', ';', ',', null,0)";

stmt.executeUpdate(importTableQuery);

}

catch{ //etc.}
}

Anyhow, this is throwing all kinds of stuff.

So I am moving back to my original idea of row by row.

However, the rows contain random types of data in the .csv file. it oculd be 100 integers in a row, or a bunch of VARCHAR interspersed with INTEGERS.
I know I am missing something obvious.




 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
CSV -as simple as it looks- is more complicated than your code reflects. You should use one of the available CSV libraries that deals with those complexities; see http://faq.javaranch.com/java/AccessingFileFormats for a list.
 
james hagbard
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK so I am able to read in the csv file without problem.

I am now running into another problem

here is the code I am using to insert the data into my table.

And when I try runnign the program it pukes like this:

java.sql.SQLSyntaxErrorException: Syntax error: Encountered ")" at line 1, column 59.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Statement.executeUpdate(Unknown Source)


If I change my string to read like this:

it pukes like this:
ava.sql.SQLSyntaxErrorException: Syntax error: Encountered "\'institution_id\'" at line 1, column 35.

Any input?
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Only the value to be inserted should be quoted, not the column name.
 
james hagbard
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK,

so now I am getting this working a bit better.

Now, I am having an issue with importing the columns.
I am reading the first line of the file into a Stringarray variable with a CSVreader object and putting it into a variable called
String[] columnName, which I am passing to my create table method.

I am taking the first line and parsing that into my column names.
When I print out the string array to the console, it's showing me the necessary names
However, when I try to wrie the column names to the DB, only 4 are getting inserted into the DB.

Here is my code:



the length of the string array is 243 strings. (perhaps its only getting 243 characters?)
However, it's only keeping 5 entries, but only inserting 4 of them...

THanks for you help so far!
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In future please use the Code button to put code tags around Java code. (Look just above the box where you are typing your post.)

Here's the standard Java idiom for processing each element of an array:

Notice how that's different from the code you have?
 
james hagbard
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:In future please use the Code button to put code tags around Java code. (Look just above the box where you are typing your post.)

Here's the standard Java idiom for processing each element of an array:

Notice how that's different from the code you have?



I didn't see the code button.

I've tried it both ways. I don't have the code in front of me now, but I will try it that way and check to see if it works.
Thanks for the heads up.
 
james hagbard
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I changed the array parser as suggested.
Made absolutely no difference. The table just gets 4 of the values in the column name string.

When I look at the Printstacktrace, the insert is puking when the SQL statement gets to the 'year' value of the string array.
I am inserting the code through a for loop. And the previous 4 values were inserted without an issue.

Here is my console printout:

 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
'year' may be a reserved word in the Derby SQL dialect. If you want to use it in an ALTER TABLE statement you may have to put it in quotes.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic