Bulk Load

[anvi kon]

Is any one can help me to load 10k data in to the mysql database table using java?

I have tried the code below.

String file = "C:\\bulk\\example.csv";
try{
File filename = new File(file);
String path = filename.getAbsolutePath();
String tablename ="first_tbl";		
String statementText= "LOAD DATA LOCAL INFILE \"" + path + "\" INTO TABLE " + tablename + " FIELDS TERMINATED BY ','";	
	    
st = conn.createStatement();
st.executeUpdate(statementText);

}
catch(Exception e)
{e.printStackTrace();
st = null;
}

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]

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

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

[anvi kon]

thanks for you reply.

I have tried the below.

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

Still I am getting the same error.

[Paul Clapham]

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]

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]

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