aspose file tools*
The moose likes JDBC and the fly likes Bulk Load Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Bulk Load" Watch "Bulk Load" New topic
Author

Bulk Load

anvi kon
Ranch Hand

Joined: Jan 08, 2010
Posts: 133
Is any one can help me to load 10k data in to the mysql database table using java?

I have tried the code below.



I got the below error:

java.io.FileNotFoundException
MESSAGE: C:ulkexample.csv (The filename, directory name, or volume label syntax is incorrect)

STACKTRACE:

java.io.FileNotFoundException: C:ulkexample.csv (The filename, directory name, or volume label syntax is incorrect)
at java.io.FileInputStream.open(Native Method)
at java.io.FileInputStream.<init>(FileInputStream.java:106)
at java.io.FileInputStream.<init>(FileInputStream.java:66)
at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:2820)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2074)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1451)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1787)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1402)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1317)
at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:186)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.resource.adapter.jdbc.remote.WrapperDataSourceService.doStatementMethod(WrapperDataSourceService.java:394)
at org.jboss.resource.adapter.jdbc.remote.WrapperDataSourceService.invoke(WrapperDataSourceService.java:223)
at sun.reflect.GeneratedMethodAccessor133.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.invocation.local.LocalInvoker$MBeanServerAction.invoke(LocalInvoker.java:169)
at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:118)
at org.jboss.invocation.InvokerInterceptor.invokeLocal(InvokerInterceptor.java:209)
at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:195)
at org.jboss.proxy.ClientMethodInterceptor.invoke(ClientMethodInterceptor.java:74)
at org.jboss.resource.adapter.jdbc.remote.StatementInterceptor.invoke(StatementInterceptor.java:58)
at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:100)
at $Proxy85.executeUpdate(Unknown Source)


thanks

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18656
    
    8

Well, that's interesting. Something is interpreting the "\b" which comes near the beginning of that file path as a "backspace" character, which then "backspaces" and removes the "\" which came before it. (Or something like that.)

I suggest trying

anvi kon
Ranch Hand

Joined: Jan 08, 2010
Posts: 133
thanks for you reply.

I have tried the below.

String file = "C:/bulk/example.csv";

Still I am getting the same error.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18656
    
    8

The error occurs here:

at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:2820)


So perhaps it's a bug in that code. On the other hand it's also possible that the syntax of the LOAD DATA command requires a file name and not a path name, and the JDBC driver is dropping characters that can't appear in a file name. (I haven't looked that up -- I suggest you do that.)

At any rate I think your workaround is to make sure your CSV file is in the current working directory, and then just use the file name and not the path.
anvi kon
Ranch Hand

Joined: Jan 08, 2010
Posts: 133
I have update the code below:

String statementText= "LOAD DATA LOCAL INFILE \"" + filename + "\" INTO TABLE " + tablename + " FIELDS TERMINATED BY ','";

Still I get the same error:

Do I have to use jdbc driver 5 here fro MYSQL?

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18656
    
    8

Paul Clapham wrote:At any rate I think your workaround is to make sure your CSV file is in the current working directory, and then just use the file name and not the path.


Sorry, that reply seems to have overestimated something. Let me explain the technical terminology I used there:

File name = example.csv

Path = C:\bulk\example.csv
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Bulk Load