Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Replacing double quotes in string

  1. #1
    Join Date
    Oct 2013
    Posts
    4

    Post Replacing double quotes in string

    Hi,
    I have been trying to use the JavaScript step to remove double quotes from a string field in a csv file. Needless to say that nothing I try seems to work. I'm Simy reading a field (string)and replacing any instances of double quote (") with blank. Unfortunately, even though it seems that the JavaScript step performs the transformation correctly, it doesn't correctly pass it to the next step (double quotes persist).

    Var string = inputfield;
    var new_string = replace (string, /\"/, "");

    Here's a sample file: https://docs.google.com/a/townhouses...p=docslist_api

    Here's the corresponding input: https://docs.google.com/a/townhouses...p=docslist_api

    From what I can tell, the Enclosure options in the CSV input don't work well when the string contains double quotes.

    Thanks for the help.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You better attach samples here, so we can access them now and forever.

    Regarding your JavaScript code, try this: var inputfield = replace(inputfield, "\"", "");
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Why don't you use "Replace in String" step?
    -- Mick --

  4. #4
    Join Date
    Oct 2013
    Posts
    4

    Default

    Quote Originally Posted by marabu View Post
    You better attach samples here, so we can access them now and forever.

    Regarding your JavaScript code, try this: var inputfield = replace(inputfield, "\"", "");
    Hello All -
    I have attached the files. Please rename the txt file to CSV as it is the input.
    I will try your suggestions shortly.

    Thanks!!!
    B
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2013
    Posts
    4

    Default

    Hi Marabu,

    Thanks for the suggestion. I tried modifying the javascript but that doesn't seem to help remove the double quotes from the inout string.
    My transformation reads the input CSV file attached, tries to remove the double quote and inserts/updates to a database (as-is).

    Any help to point me in the right direction would help. I have used CloverETL extensively in the past and am now learning Pentaho. I really like the tool but the learning curve is a little steeper than I thought. Thank you very much!

    import_to_db.ktr
    res_test.txt

    Note that the field in question is "RemarksFromClient"

    Here's the error I see:
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected error
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error in step, asking everyone to stop because of:
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error inserting/updating row
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Data truncation: Data too long for column 'Retirement' at row 1
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:318)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.lang.Thread.run(Thread.java:695)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error inserting/updating row
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Data truncation: Data too long for column 'Retirement' at row 1
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1411)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1325)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1313)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1296)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:116)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:301)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ... 2 more
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'Retirement' at row 1
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1328)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:838)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1360)
    2013/11/07 20:19:32 - Insert / Update - ListingResidential.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ... 7 more
    Last edited by brauliolam; 11-07-2013 at 09:29 PM. Reason: missing info

  6. #6
    Join Date
    Oct 2013
    Posts
    4

    Default

    Quote Originally Posted by Mick_data View Post
    Why don't you use "Replace in String" step?
    Hi Mick,
    Thank you for your reply. Much appreciated.
    Unfortunately it doesn't work for me...it doesn't replace the double quotes in the input file string with a blank

  7. #7
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Your main problem seems to be junk input.
    The application responsible for creating the CSV file did a terrible job.
    So long, and thanks for all the fish.

  8. #8
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    it doesn't replace the double quotes in the input file string with a blank
    I haven't looked at your KTR, but my guess is that you have to set Text File Input not to use Encloser (").
    Once you import your file and you can preview it with all quotes, then you can replace them.

    Another option: import your text file using a fake delimiter (like a tab, or }) and no encloser and no header.
    You end up with only one big field as String.
    Use the Replace in String for that field (basically doing a search and replace across the whole file) and then create a new Text File, without header.
    Now you can import your text file again using the proper delimiter and header.
    -- Mick --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.