CSV Import quoteCharacter issues

0
Hi Forum,   I am using the CSV Module to import and process a CSV file coming from an FTP server, but am running into a show-stopping problem. Even though I have defined the quoteCharacter as “~”, when my file contains an odd number of double quotes (“) the CountNumberOfLinesInCSV Java action will throw an error saying it encountered an unterminated quote field at the end of the line (see below). It will always do this for lines that contain an odd number of double quotes. When a line contains an even number of double quotes, the action will keep running, but the data will be screwed up. Files that do not contain any double quotes are imported without problems.   It looks to me like the Java action views the double quotes as quoteCharacter anyway, despite me having defined the ~ as such. Has anyone encountered something similar or has an idea how to bypass this? I tried to hardcode the ~ as quoteCharacter directly into the Java action, but lacked the knowledge to do so.   A peculiar thing to note is that when we edit the csv file to contain only a few lines, one of them being the line containing the double quotes, the CountNumberOfLinesInCSV action will run to the end and put extra double quotes around the imported line out of nowhere.   Thanks, kind regards,   Ruben Nuijten     An error has occurred while handling the request. [User 'demo_technicaladministrator' with session id '7e033670-XXXX-XXXX-XXXX-XXXXXXXX94be' and roles 'TechnicalAdministrator'] -------- com.mendix.core.CoreException: com.mendix.modules.microflowengine.MicroflowException: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.io.IOException: Unterminated quoted field at end of CSV line. Beginning of lost text: [~Std RT 2" x 10 swg            ~,~ET-UPC0313~,~SF_EXT0012000000Cvh53AAB~,~2019-10-25~,~Movex~ ~33...]     at MasterData.SUB_ProcessCSVFile (JavaAction : 'Count number of lines in CSV') Advanced stacktrace:     at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:389) Caused by: com.mendix.modules.microflowengine.MicroflowException: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.io.IOException: Unterminated quoted field at end of CSV line. Beginning of lost text: [~Std RT 2" x 10 swg            ~,~ET-UPC0313~,~SF_EXT0012000000Cvh53AAB~,~2019-10-25~,~Movex~ ~33...]     at MasterData.SUB_ProcessCSVFile (JavaAction : 'Count number of lines in CSV') Advanced stacktrace:     at com.mendix.modules.microflowengine.MicroflowUtil.processException(MicroflowUtil.java:146) Caused by: com.mendix.core.CoreException: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.io.IOException: Unterminated quoted field at end of CSV line. Beginning of lost text: [~Std RT 2" x 10 swg            ~,~ET-UPC0313~,~SF_EXT0012000000Cvh53AAB~,~2019-10-25~,~Movex~ ~33...]     at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:418) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.io.IOException: Unterminated quoted field at end of CSV line. Beginning of lost text: [~Std RT 2" x 10 swg            ~,~ET-UPC0313~,~SF_EXT0012000000Cvh53AAB~,~2019-10-25~,~Movex~ ~33...]     at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:159) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: java.io.IOException: Unterminated quoted field at end of CSV line. Beginning of lost text: [~Std RT 2" x 10 swg            ~,~ET-UPC0313~,~SF_EXT0012000000Cvh53AAB~,~2019-10-25~,~Movex~ ~33...]     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:36) Caused by: java.io.IOException: Unterminated quoted field at end of CSV line. Beginning of lost text: [~Std RT 2" x 10 swg            ~,~ET-UPC0313~,~SF_EXT0012000000Cvh53AAB~,~2019-10-25~,~Movex~ ~33...]     at com.opencsv.CSVReader.readNext(CSVReader.java:334)     at csv.actions.CountLines.executeAction(CountLines.java:59)     at csv.actions.CountLines.executeAction(CountLines.java:27)     at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:79)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:57)     at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:55)     at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:150)     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32)     at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:155)     at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:414)     at com.mendix.modules.microflowengine.actions.actioncall.JavaAction.execute(JavaAction.scala:56)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowObject.execute(MicroflowObject.java:47)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAfterBreakingIfNecessary(MicroflowImpl.java:201)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAction(MicroflowImpl.java:157)     at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:79)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:57)     at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:55)     at com.mendix.basis.actionmanagement.DefaultActionMonitor$.$anonfun$run$1(CustomMonitoredAction.scala:18)     at com.mendix.basis.actionmanagement.IMonitoredAction$$anon$1.execute(IMonitoredAction.scala:47)     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32)     at com.mendix.basis.actionmanagement.IMonitoredAction.monitor(IMonitoredAction.scala:49)     at com.mendix.basis.actionmanagement.IMonitoredAction.monitor$(IMonitoredAction.scala:25)     at com.mendix.basis.actionmanagement.CustomMonitoredAction.monitor(CustomMonitoredAction.scala:22)     at com.mendix.basis.actionmanagement.DefaultActionMonitor$.run(CustomMonitoredAction.scala:18)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.runMonitoredAction(CoreActionHandlerImpl.scala:68)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:55)     at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:55)     at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:150)     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32)     at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:155)     at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:414)     at com.mendix.core.Core.execute(Core.java:186)     at csv.actions.ImportCSV.executeAction(ImportCSV.java:87)     at csv.actions.ImportCSV.executeAction(ImportCSV.java:27)     at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:79)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:57)     at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:55)     at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:150)     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32)     at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:155)     at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:414)     at com.mendix.modules.microflowengine.actions.actioncall.JavaAction.execute(JavaAction.scala:56)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowObject.execute(MicroflowObject.java:47)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAfterBreakingIfNecessary(MicroflowImpl.java:201)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAction(MicroflowImpl.java:157)     at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:79)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:57)     at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:55)     at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:150)     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32)     at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:155)     at com.mendix.basis.component.InternalCoreBase.executeSync(InternalCoreBase.java:493)     at com.mendix.modules.microflowengine.actions.SubMicroflowAction.execute(SubMicroflowAction.scala:51)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowObject.execute(MicroflowObject.java:47)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAfterBreakingIfNecessary(MicroflowImpl.java:201)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAction(MicroflowImpl.java:157)     at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:79)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:57)     at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:55)     at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:150)     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32)     at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:155)     at com.mendix.basis.component.InternalCoreBase.executeSync(InternalCoreBase.java:493)     at com.mendix.modules.microflowengine.actions.SubMicroflowAction.execute(SubMicroflowAction.scala:51)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowObject.execute(MicroflowObject.java:47)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAfterBreakingIfNecessary(MicroflowImpl.java:201)     at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAction(MicroflowImpl.java:157)     at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:79)     at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:57)     at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:55)     at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:150)     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32)     at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:155)     at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:383)     at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:398)     at com.mendix.webui.actions.client.ExecuteMicroflowAction.runMicroflow(ExecuteMicroflowAction.scala:46)     at com.mendix.webui.actions.client.ExecuteMicroflowAction.apply(ExecuteMicroflowAction.scala:32)     at com.mendix.webui.actions.client.ExecuteMicroflowAction.apply(ExecuteMicroflowAction.scala:15)     at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$4(RegularClientAction.scala:46)     at com.mendix.webui.requesthandling.helpers.StateHandling.withState(StateHandling.scala:33)     at com.mendix.webui.requesthandling.helpers.StateHandling.withState$(StateHandling.scala:21)     at com.mendix.webui.actions.client.RegularClientAction.withState(RegularClientAction.scala:21)     at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$3(RegularClientAction.scala:44)     at scala.util.Try$.apply(Try.scala:213)     at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$2(RegularClientAction.scala:44)     at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$2$adapted(RegularClientAction.scala:42)     at com.mendix.webui.requesthandling.helpers.ContextHandling.$anonfun$inContext$4(ContextHandling.scala:41)     at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)     at com.mendix.basis.actionmanagement.IMonitoredAction$$anon$1.execute(IMonitoredAction.scala:47)     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32)     at com.mendix.basis.actionmanagement.IMonitoredAction.monitor(IMonitoredAction.scala:49)     at com.mendix.basis.actionmanagement.IMonitoredAction.monitor$(IMonitoredAction.scala:25)     at com.mendix.webui.requesthandling.helpers.ContextHandling$ClientMonitoredAction.monitor(ContextHandling.scala:49)     at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:41)     at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:27)     at com.mendix.webui.actions.client.RegularClientAction.inContext(RegularClientAction.scala:21)     at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:24)     at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:19)     at com.mendix.webui.actions.client.RegularClientAction.inContext(RegularClientAction.scala:21)     at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$1(RegularClientAction.scala:42)     at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)     at com.mendix.webui.requesthandling.helpers.ProfileHandling.profileRequest(ProfileHandling.scala:14)     at com.mendix.webui.requesthandling.helpers.ProfileHandling.profileRequest$(ProfileHandling.scala:10)     at com.mendix.webui.actions.client.RegularClientAction.profileRequest(RegularClientAction.scala:21)     at com.mendix.webui.actions.client.RegularClientAction.execute(RegularClientAction.scala:39)     at com.mendix.webui.requesthandling.ClientRequestHandler.handleAction(ClientRequestHandler.scala:124)     at com.mendix.webui.requesthandling.ClientRequestHandler.processRequest(ClientRequestHandler.scala:101)     at com.mendix.externalinterface.connector.RequestHandler.doProcessRequest(RequestHandler.java:40)     at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:72)     at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:69)     at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32)     at com.mendix.external.connector.MxRuntimeConnector.processRequest(MxRuntimeConnector.java:75)     at com.mendix.basis.impl.MxRuntimeImpl.processRequest(MxRuntimeImpl.java:909)     at com.mendix.m2ee.appcontainer.server.handler.RuntimeHandler.service(RuntimeHandler.java:42)     at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)     at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:867)     at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:542)     at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)     at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1588)     at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)     at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1345)     at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203)     at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:480)     at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1557)     at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201)     at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1247)     at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)     at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)     at org.eclipse.jetty.server.Server.handle(Server.java:502)     at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364)     at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260)     at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)     at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)     at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118)     at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333)     at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310)     at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168)     at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126)     at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366)     at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765)     at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683)     at java.lang.Thread.run(Thread.java:748)  
asked
1 answers
2

Hi Ruben,

Dave Timmer asked the same question yesterday, and I answered it here.

https://forum.mendix.com/link/questions/96639

Using the sample data provided, I was able to parse the data successfully. I included a copy of my solution in the answer to his question.

Hope this helps,

Rob

answered