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
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:
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
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
The actual SQL being used to do the insert is as follows: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.
Code: Select all
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.
insert into LIBRARY.TABLE
(
COLUMN1,
COLUMN2)
values
(
'${rowVar[1]}',
'${rowVar[2]}')
)
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 Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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 )
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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
2 posts
Page 1 of 1