用的是FROMEXCEL，FASTINSERT每次插入1000条，发现只导入了300多条，其中所有带换行的记录都无法导入，在POJO中有办法忽略换行吗？要不然只能对每条记录进行换行符的替换，会好慢的
问答
最近做EXCEL文档导入都快疯了：86个字段的EXCEL表，有些字段存在回车，结果无法导入数据库
标签:
无
20 回复
For example:> "INSERT INTO zwda.dbo.yhczazdylb(bdw,gdh,zdh,jrfs,sfzddbd,gzhm,ldlsh,yhmc,fgqdz,gdzt,ywlb,hbslsj,jx,gdly,gzlx,gzxxmx,gzyclxq,gzdx,gzdj,gdzt1,slr,zrbnsj,zrbn,zrg,zrr,yglb,gzsgsj,gzpdsj,gdclsx,gdjzsj,hdsj,hdr,hdrdh,hdrbm,gzxhyy,hdbz,gzhhls,sfcs,cssc,gdgdsj,gszls,myd,vipbs,cxbs,pp,yylxkhsj,sclysc,lxsfjs,sfyylxkh,lxdh,qy,hfmyd,cdcs,zdjdsj,stchs,sczpsj,zpczr,jjxm,adslsbh,pzczr,pzbm,pzmx,pzyqyy,zrylchs,bz,sczddbdsj,dbdsx,dbdjzsj,dbdsfyx,zddbyy,pfje,zshgdr,zshgdbm,sfqzlj,gzxpyy,sclxsj,shlxcs,sfsclxsb,tydcxywhm,tytcxlygd,dxpcsj,pdsj,psgw,hdsj1,lscs,drsj) VALUES('长沙分公司','20171120227466','2017112289445','FTTH','否','CSD1019367','GZ731171130397463','彭忠林','动物园宿舍3栋3单元1层楼梯间','CSD1019367障碍受理','FTTH障碍','2017-11-30 17:54:12.0','德雅路动物园宿舍(自建改)-13514510WFH','10000号平台','错误代码-678','错误代码-678用户来电反映宽带不能正常使用，光猫亮红灯，请师傅上门尽快处理，谢谢！诊断时长：28秒申告号码:CSD1019367 归属地:长沙本地网 登录工号:4947 受理人姓名:4947 受理人联电:17375867574联系电话:17788965448 省客户服务中心紧急支撑热线0731-85284041','未经预处理','','不紧急','归档','10000','金迅五一路代维班组','德雅村（金迅）装维班','德雅村（金迅）装维班外线施工岗','白加西（金五）','E','2017-11-30 17:53:42.0','2017-11-30 17:54:17.0','366','2017-11-30 23:59:59.0','2017-11-30 20:15:14.0','白加西（金五）','17373197611','德雅村（金迅）装维班','光纤原因','','141','否','','2017-12-01 08:21:54.0','868','满意','5星','城区','天翼,T1商旅卡','2017-11-30 18:01:32.0','90','及时','是','17788965448','长沙市','满意','','2017-11-30 17:54:18.0','0','','','','731KFU.DWYSS/FTTHOBD001/003','','','','','0','用户来电反映宽带不能正常使用，光猫亮红灯，请师傅上门尽快处理，谢谢！诊断时长：28秒申告号码:CSD1019367 归属地:长沙本地网 登录工号:4947 受理人姓名:4947 受理人联电:17375867574联系电话:17788965448 省客户服务中心紧急支撑热线0731-85284041速率：200M Android-工单预约： 预约开始时间：2017-11-30 19:00:00.0 预约结束时间：2017-11-30 21:00:00.0 工单催修：金迅收单岗在2017-11-30 19:57:17进行了催修;晚上12点逾限 请及时回单 Android回单： 证实（归档）：10000号平台证实归档','','','','否','未升级','','10000','湖南公司','否','默认','2017-11-30 18:01:32.0','否','否','17788965448','','','','','2017-11-30 20:15:14.0','1',NULL) "
17-12-17 20:38:03.212 DEBUG [http-nio-8089-exec-46] SQLException
java.sql.BatchUpdateException: 将截断字符串或二进制数据。
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1160)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeBatch(DruidPooledPreparedStatement.java:559)
at org.nutz.dao.impl.sql.run.NutDaoExecutor._runPreparedStatement(NutDaoExecutor.java:322)
at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:66)
at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:64)
at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:139)
at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:158)
at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:125)
at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:92)
at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:81)
at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:240)
at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:252)
at org.nutz.dao.impl.EntityOperator.exec(EntityOperator.java:55)
at org.nutz.dao.impl.NutDao.fastInsert(NutDao.java:193)
at main.zwdaAction.updatefile(zwdaAction.java:78)
at main.zwdaAction$FM$updatefile$5407e8eb5cf295b6d8291fb67ba2fce9.invoke(zwdaAction.java)
at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:31)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:30)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:58)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:123)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:44)
at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:67)
at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
at org.nutz.mvc.NutFilter.doFilter(NutFilter.java:202)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:783)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:798)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1434)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
17-12-17 20:38:03.306 WARN [http-nio-8089-exec-46] Error@/zwda/updatefile :
org.nutz.dao.DaoException: !Nutz SQL Error: 'INSERT INTO zwda.dbo.yhczazdylb(bdw,gdh,zdh,jrfs,sfzddbd,gzhm,ldlsh,yhmc,fgqdz,gdzt,ywlb,hbslsj,jx,gdly,gzlx,gzxxmx,gzyclxq,gzdx,gzdj,gdzt1,slr,zrbnsj,zrbn,zrg,zrr,yglb,gzsgsj,gzpdsj,gdclsx,gdjzsj,hdsj,hdr,hdrdh,hdrbm,gzxhyy,hdbz,gzhhls,sfcs,cssc,gdgdsj,gszls,myd,vipbs,cxbs,pp,yylxkhsj,sclysc,lxsfjs,sfyylxkh,lxdh,qy,hfmyd,cdcs,zdjdsj,stchs,sczpsj,zpczr,jjxm,adslsbh,pzczr,pzbm,pzmx,pzyqyy,zrylchs,bz,sczddbdsj,dbdsx,dbdjzsj,dbdsfyx,zddbyy,pfje,zshgdr,zshgdbm,sfqzlj,gzxpyy,sclxsj,shlxcs,sfsclxsb,tydcxywhm,tytcxlygd,dxpcsj,pdsj,psgw,hdsj1,lscs,drsj) VALUES('长沙分公司','20171120227466','2017112289445','FTTH','否','CSD1019367','GZ731171130397463','彭忠林','动物园宿舍3栋3单元1层楼梯间','CSD1019367障碍受理','FTTH障碍','2017-11-30 17:54:12.0','德雅路动物园宿舍(自建改)-13514510WFH','10000号平台','错误代码-678','错误代码-678用户来电反映宽带不能正常使用，光猫亮红灯，请师傅上门尽快处理，谢谢！诊断时长：28秒申告号码:CSD1019367 归属地:长沙本地网 登录工号:4947 受理人姓名:4947 受理人联电:17375867574联系电话:17788965448 省客户服务中心紧急支撑热线0731-85284041','未经预处理','','不紧急','归档','10000','金迅五一路代维班组','德雅村（金迅）装维班','德雅村（金迅）装维班外线施工岗','白加西（金五）','E','2017-11-30 17:53:42.0','2017-11-30 17:54:17.0','366','2017-11-30 23:59:59.0','2017-11-30 20:15:14.0','白加西（金五）','17373197611','德雅村（金迅）装维班','光纤原因','','141','否','','2017-12-01 08:21:54.0','868','满意','5星','城区','天翼,T1商旅卡','2017-11-30 18:01:32.0','90','及时','是','17788965448','长沙市','满意','','2017-11-30 17:54:18.0','0','','','','731KFU.DWYSS/FTTHOBD001/003','','','','','0','用户来电反映宽带不能正常使用，光猫亮红灯，请师傅上门尽快处理，谢谢！诊断时长：28秒申告号码:CSD1019367 归属地:长沙本地网 登录工号:4947 受理人姓名:4947 受理人联电:17375867574联系电话:17788965448 省客户服务中心紧急支撑热线0731-85284041速率：200M Android-工单预约： 预约开始时间：2017-11-30 19:00:00.0 预约结束时间：2017-11-30 21:00:00.0 工单催修：金迅收单岗在2017-11-30 19:57:17进行了催修;晚上12点逾限 请及时回单 Android回单： 证实（归档）：10000号平台证实归档','','','','否','未升级','','10000','湖南公司','否','默认','2017-11-30 18:01:32.0','否','否','17788965448','','','','','2017-11-30 20:15:14.0','1',NULL) '
PreparedStatement:
'INSERT INTO zwda.dbo.yhczazdylb(bdw,gdh,zdh,jrfs,sfzddbd,gzhm,ldlsh,yhmc,fgqdz,gdzt,ywlb,hbslsj,jx,gdly,gzlx,gzxxmx,gzyclxq,gzdx,gzdj,gdzt1,slr,zrbnsj,zrbn,zrg,zrr,yglb,gzsgsj,gzpdsj,gdclsx,gdjzsj,hdsj,hdr,hdrdh,hdrbm,gzxhyy,hdbz,gzhhls,sfcs,cssc,gdgdsj,gszls,myd,vipbs,cxbs,pp,yylxkhsj,sclysc,lxsfjs,sfyylxkh,lxdh,qy,hfmyd,cdcs,zdjdsj,stchs,sczpsj,zpczr,jjxm,adslsbh,pzczr,pzbm,pzmx,pzyqyy,zrylchs,bz,sczddbdsj,dbdsx,dbdjzsj,dbdsfyx,zddbyy,pfje,zshgdr,zshgdbm,sfqzlj,gzxpyy,sclxsj,shlxcs,sfsclxsb,tydcxywhm,tytcxlygd,dxpcsj,pdsj,psgw,hdsj1,lscs,drsj) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) '
CaseMessage=将截断字符串或二进制数据。
at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:104)
at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:66)
at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:64)
at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:139)
at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:158)
at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:125)
at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:92)
at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:81)
at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:240)
at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:252)
at org.nutz.dao.impl.EntityOperator.exec(EntityOperator.java:55)
at org.nutz.dao.impl.NutDao.fastInsert(NutDao.java:193)
at main.zwdaAction.updatefile(zwdaAction.java:78)
at main.zwdaAction$FM$updatefile$5407e8eb5cf295b6d8291fb67ba2fce9.invoke(zwdaAction.java)
at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:31)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:30)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:58)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:123)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:44)
at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:67)
at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
at org.nutz.mvc.NutFilter.doFilter(NutFilter.java:202)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:783)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:798)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1434)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.BatchUpdateException: 将截断字符串或二进制数据。
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1160)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeBatch(DruidPooledPreparedStatement.java:559)
at org.nutz.dao.impl.sql.run.NutDaoExecutor._runPreparedStatement(NutDaoExecutor.java:322)
at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
... 48 more
添加回复
请先登陆