4 Replies Latest reply on Jan 22, 2015 5:58 AM by 2831007

    In CloverETL how to read an xlsx file with a column of "long" datatype

    2831007

      Hi All,

       

      Let me know how to read an xlsx(Excel) file with columns of "long" datatype.

       

      I have used XLSDataReader for reading the xlsx file. There is column names "Comments" which exceeds the linits of 256 characters so i cant use "string" datatype for this.

       

      Im using "long" datatype and the graph throughs below error.

       

      Any pointers on this will be greatly appreciated.

       

      INFO  [main] - ***  CloverETL framework/transformation graph, (c) 2002-2013 Javlin a.s, released under GNU Lesser General Public License  ***
      INFO  [main] - Running with CloverETL library version 3.4.1 build#010 compiled 01/07/2013 18:38:12
      INFO  [main] - Running on 4 CPU(s), OS Windows 7, architecture amd64, Java version 1.7.0_07, max available memory for JVM 913920 KB
      INFO  [main] - Loading default properties from: defaultProperties
      INFO  [main] - Graph definition file: graph/SurveyResponses.grf
      INFO  [main] - Graph revision: 1.16 Modified by: 470707 Modified: Tue Jan 20 16:13:52 IST 2015
      INFO  [main] - Checking graph configuration...
      INFO  [main] - Graph configuration is valid.
      INFO  [main] - Graph initialization (SurveyResponses)
      INFO  [main] - Initializing phase 0
      INFO  [main] - Phase 0 initialized successfully.
      INFO  [WatchDog_0] - Starting up all nodes in phase [0]
      INFO  [WatchDog_0] - Successfully started all nodes in phase!
      INFO  [XLSDATA_READER1_0] - Reading data from sheet 0 (Sheet1).
      ERROR [WatchDog_0] - Component [XLSDataReader:XLSDATA_READER1] finished with status ERROR.
      Field Comment(long) cannot be set to value "Comment"; doesn't match the specified format "" with reason "Incomplete parsing"; in record 1, field 23 ("Comment"), metadata "Survey_Responses_Sheet1"; value: 'Comment' (note that for ParallelReader or Server parallel transformation run the record number might be incorrect)
      ERROR [WatchDog_0] - Error details:
      org.jetel.exception.JetelRuntimeException: Component [XLSDataReader:XLSDATA_READER1] finished with status ERROR.
      at org.jetel.graph.Node.createNodeException(Node.java:535)
      at org.jetel.graph.Node.run(Node.java:514)
      at java.lang.Thread.run(Thread.java:722)
      Caused by: org.jetel.exception.BadDataFormatException: Field Comment(long) cannot be set to value "Comment"; doesn't match the specified format "" with reason "Incomplete parsing"; in record 1, field 23 ("Comment"), metadata "Survey_Responses_Sheet1"; value: 'Comment' (note that for ParallelReader or Server parallel transformation run the record number might be incorrect)
      at org.jetel.data.LongDataField.fromString(LongDataField.java:465)
      at org.jetel.data.parser.XLSXDataParser.parseNext(XLSXDataParser.java:492)
      at org.jetel.data.parser.XLSParser.getNext(XLSParser.java:136)
      at org.jetel.util.MultiFileReader.getNext(MultiFileReader.java:432)
      at org.jetel.component.XLSReader.execute(XLSReader.java:562)
      at org.jetel.graph.Node.run(Node.java:485)
      ... 1 more
      Caused by: java.lang.NumberFormatException: Incomplete parsing
      at javolution.text.TypeFormat.parseLongString(Unknown Source)
      at javolution.text.TypeFormat.parseLong(Unknown Source)
      at javolution.text.TypeFormat.parseLong(Unknown Source)
      at javolution.text.TypeFormat.parseLong(Unknown Source)
      at org.jetel.util.formatter.JavolutionNumericFormatter.parseLong(JavolutionNumericFormatter.java:74)
      at org.jetel.data.LongDataField.fromString(LongDataField.java:462)
      ... 6 more

      INFO  [WatchDog_0] - Execution of phase [0] finished with error - elapsed time(sec): 4
      ERROR [WatchDog_0] - !!! Phase finished with error - stopping graph run !!!
      INFO  [WatchDog_0] - -----------------------** Summary of Phases execution **---------------------
      INFO  [WatchDog_0] - Phase#            Finished Status         RunTime(sec)    MemoryAllocation(KB)
      INFO  [WatchDog_0] - 0                 ERROR                              4            175279
      INFO  [WatchDog_0] - ------------------------------** End of Summary **---------------------------
      INFO  [WatchDog_0] - WatchDog thread finished - total execution time: 4 (sec)
      INFO  [main] - Freeing graph resources.
      ERROR [main] -
      --------------------------------------------------------------------------------------------------------------------------------------------------------- Error details ----------------------------------------------------------------------------------------------------------------------------------------------------------
        Component [XLSDataReader:XLSDATA_READER1] finished with status ERROR.
         Field Comment(long) cannot be set to value "Comment"; doesn't match the specified format "" with reason "Incomplete parsing"; in record 1, field 23 ("Comment"), metadata "Survey_Responses_Sheet1"; value: 'Comment' (note that for ParallelReader or Server parallel transformation run the record number might be incorrect)
      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ERROR [main] - Execution of graph failed !