*
The moose likes JDBC and the fly likes import data from large csv file. Help. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "import data from large csv file. Help." Watch "import data from large csv file. Help." New topic
Author

import data from large csv file. Help.

james hagbard
Greenhorn

Joined: Sep 27, 2009
Posts: 8
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.




Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41096
    
  44
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.


Ping & DNS - my free Android networking tools app
james hagbard
Greenhorn

Joined: Sep 27, 2009
Posts: 8
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
Marshal

Joined: Mar 22, 2005
Posts: 41096
    
  44
Only the value to be inserted should be quoted, not the column name.
james hagbard
Greenhorn

Joined: Sep 27, 2009
Posts: 8
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!
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Sep 27, 2009
Posts: 8
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

Joined: Sep 27, 2009
Posts: 8
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
Marshal

Joined: Mar 22, 2005
Posts: 41096
    
  44
'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.
 
Don't get me started about those stupid light bulbs.
 
subject: import data from large csv file. Help.
 
Similar Threads
FTP Socket Programming
how to read the .csv file
Send a String to Server and server send it somewhere on network
Write and Update CSV files
array