Replacement characters

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
2 posts Page 1 of 1

tmecheski

Posts: 1
Joined: Thu Apr 30, 2015 9:18 am
Location: New Hampshire

Post by tmecheski » Thu Apr 30, 2015 10:16 am
I have what I am hoping is a simple question.

I am attempting to execute a project that reads a comma delimited file and I want to insert the data into an iSeries table. The problem, however, is that one of the columns in the inbound .csv data contains an apostrophe. Therefore, when I attempt to do an insert into the iSeries table the process fails.

I have tried using the iSeries scalar REPLACE function, but this is not working for me. I have also tried searching this forum for anyone else with a similar problem and, unfortunately, I was not successful in finding an answer.

Listed below is a modified log of what I am seeing:
Code: Select all
4/30/15 1:00:00 AM            INFO      Start Date and Time: 4/30/15 1:00:00 AM
4/30/15 1:00:00 AM            INFO      Job Number: 1392921668041 
4/30/15 1:00:00 AM            INFO      Project Name: /Directory/GA_Project
4/30/15 1:00:00 AM            INFO      Submitted By: USER1
4/30/15 1:00:00 AM            INFO      GoAnywhere Director 4.5.1 running on OS/400 V7R1M0 (PowerPC)
4/30/15 1:00:21 AM            INFO      Overriding variable 'Date' with value '20150429'
4/30/15 1:00:21 AM            INFO      Overriding variable 'DestDir' with value '/Directory2/Output'
4/30/15 1:00:21 AM            INFO      Overriding variable 'SourceLoc' with value 'from_vendor'
4/30/15 1:00:21 AM            INFO      Overriding variable 'SourceFile' with value 'FILE.DIV'
4/30/15 1:00:21 AM            INFO      Executing project 'GA_Project' 
4/30/15 1:00:21 AM            INFO      Project location: /linoma/goanywhere/userdata/projects/vendor/GA_Project.xml
4/30/15 1:00:21 AM            INFO      Executing module 'Main'
4/30/15 1:00:21 AM            INFO      Executing task 'createWorkspace 1.0'
4/30/15 1:00:21 AM            INFO      Workspace directory for this job is set to '/linoma/goanywhere/userdata/workspace/1392921668041'.
4/30/15 1:00:21 AM            INFO      Finished task 'createWorkspace 1.0'
4/30/15 1:00:21 AM            INFO      Executing task 'sftp 1.0 (Get a file)'
4/30/15 1:00:21 AM            INFO      Connecting to '###.###.##.##' at port '22' as user 'ftpuser' 
4/30/15 1:00:21 AM            INFO      Using proxy 'http://##.###.##.###:8080' 
4/30/15 1:00:22 AM            INFO      Executing sub-task 'list'
4/30/15 1:00:24 AM            INFO      Remote FileList variable 'filelist' was created containing 2 file(s)
4/30/15 1:00:24 AM            INFO      Finished sub-task 'list'
4/30/15 1:00:24 AM            INFO      Executing sub-task 'get'
4/30/15 1:00:24 AM            INFO      Setting the data type to BINARY
4/30/15 1:00:24 AM            INFO      Downloading '/from_vendor/FILE.DIV264.20150429' to '/Directory2/Output/FILE.DIV264.20150429'
4/30/15 1:00:25 AM            INFO      File '/from_vendor/FILE.DIV264.20150429' successfully downloaded to '/Directory2/Output/FILE.DIV264.20150429' (1,616 bytes)
4/30/15 1:00:25 AM            INFO      Downloading '/from_vendor/FILE.DIV265.20150429' to '/Directory2/Output/FILE.DIV265.20150429'
4/30/15 1:00:25 AM            INFO      File '/from_vendor/FILE.DIV265.20150429' successfully downloaded to '/Directory2/Output/FILE.DIV265.20150429' (202 bytes)
4/30/15 1:00:25 AM            INFO      2 file(s) were downloaded successfully 
4/30/15 1:00:25 AM            INFO      Finished sub-task 'get'
4/30/15 1:00:25 AM            INFO      Closed the FTP connection
4/30/15 1:00:25 AM            INFO      Finished task 'sftp 1.0 (Get a file)'
4/30/15 1:00:25 AM            INFO      Executing task 'readCSV 1.0'
4/30/15 1:00:25 AM            INFO      Data parsed successfully and the rowset variable 'filesout' was created
4/30/15 1:00:25 AM            INFO      Finished task 'readCSV 1.0'
4/30/15 1:00:25 AM            INFO      Entering loop 'forEachLoop'
4/30/15 1:00:25 AM            INFO      Opening file '/Directory2/Output/FILE.DIV264.20150429'
4/30/15 1:00:25 AM            INFO      Executing task 'sql 1.0'
4/30/15 1:00:31 AM            INFO      Executing sub-task 'query'
4/30/15 1:00:31 AM            INFO      Executing statement - insert into LIBRARY.FILENAME
                                        (
                                        COLUMN1,
                                        COLUMN2)
                                        )
                                        values
                                        (
                                        'COMPANY1[color=#FF0000]'S[/color]              ',
                                        'COMPANY2[color=#FF0000]'S [/color]             ')
4/30/15 1:00:31 AM            ERROR     [8098 - sql] [SQL0104] Token 'COMPANY1' was not valid. 
					Valid tokens: (. Cause . . . . . :   A syntax error was detected at token 'COMPANY1'.  
					Token 'COMPANY1' is not a valid token. 
 
					A partial list of valid tokens is (.  

					This list assumes that the statement is correct up to the token.  
					The error may be earlier in the statement, 
					but the syntax of the statement appears to be valid up to this point. 

					Recovery  . . . :   Do one or more of the following and try the request again: 

							    -- Verify the SQL statement in the area of the token 'COMPANY1'. 

							    Correct the statement.  

							    The error could be a missing comma or quotation mark, it could be a misspelled word, 
							    or it could be related to the order of clauses.

							    -- If the error token is <END-OF-STATEMENT>, 
							    correct the SQL statement because it does not end with a valid clause. 
The actual SQL being used to do the insert is as follows:
Code: Select all

insert into LIBRARY.TABLE

(
COLUMN1,
COLUMN2)

values
(
'${rowVar[1]}',
'${rowVar[2]}')
)
As I indicated, I also tried to replace the '${rowVar[1]}' with REPLACE('${rowVar[1]}','''', '''''), but that did not work either. In that case then it tells me that token S is not a valid token.

I am sure that this is a common problem, but unfortunately, I can't seem to figure it out. So, any help would be greatly appreciated.

Does anyone have any thoughts on what I might be able to do replace the apostrophe S within the .csv data with a blank before doing the insert? In this particular case it does not even matter if we have to replace the 'S with blanks.

Thanks,

Tim

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Mon Jun 08, 2015 5:18 pm
There are 2 options that I can think of ...

1. Read the CSV file and create your Rowset Variable. Use the ModifyRowset to Change the field to the Replace of the field. In doing this, make sure you create a variable called "quote" and set the value of the variable to ' (single quote). Then, use that in your Replace function...

${replace(myfield,quote,system.emptystring)}

or something similar. Then, Insert the full (modified) rowset into your DB2 Table.

2. In the ForEach loop, you could use the Replace function (as mentioned in #1 above) inside the insert statement within the ForEach.

Insert into MyLib.MyTable (F1, F2, F3)
Values( 'Value 1', '${replace(myfield,quote,system.emptystring)}', Value 3 )
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
2 posts Page 1 of 1