Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
-
pshuey
- Posts: 3
- Joined: Tue Dec 30, 2014 1:31 pm
I am trying to update a database file based on data in a .csv file I am getting from the ifs. Below is the xml from my project. Can you tell me why my sql does not update the file? The accounts in the .csv are in the database file.
Code: Select all<project name="Acct-REP-REP1" mainModule="Main" version="2.0" logLevel="verbose">
<description>Update sales rep and alternate rep for an account</description>
<module name="Main">
<createWorkspace version="1.0" />
<ftp label="Connect to FTP server" resourceId="iSeries" version="1.0" disabled="false">
<get label="Get CSV File" destinationDir="/updates/Account_SalesRep_AltRep/archive" whenFileExists="rename" destinationFilesVariable="csvFile">
<fileset dir="/updates/Account_SalesRep_AltRep">
<wildcardFilter>
<include pattern="*.csv" />
</wildcardFilter>
</fileset>
</get>
</ftp>
<readCSV label="Read CSV File" inputFilesVariable="${csvFile}" outputRowSetVariable="csvData" skipFirstRow="true" recordDelimiter="CRLF" version="1.0">
<data trim="both" dateFormat="MM/dd/yyyy" timeFormat="HH:mm:ss" timestampFormat="MM/dd/yyyy HH:mm:ss.SSS">
<column index="1" name="ACT" size="9" type="NUMERIC" />
<column index="2" name="REP" size="5" type="NUMERIC" />
<column index="3" name="REP1" size="5" type="NUMERIC" />
</data>
</readCSV>
<sql label="Connect to DB" resourceId="iSeriesData" autoCommit="true" user="QSECOFR" password=" " version="1.0" logLevel="normal">
<query label="Update Data" inputRowSetVariable="${csvData}">
<statement>UPDATE DASTEST.IMCUSTFL SET MCREP# = ?, MCREP1 = ? WHERE MCACT# = ?</statement>
<param index="1" mapFrom="2" />
<param index="2" mapFrom="3" />
<param index="3" mapFrom="1" />
</query>
</sql>
<delete label="Delete File from IFS" file="${csvFile}" version="1.0" disabled="true" />
<deleteWorkspace version="1.0" />
</module>
</project>
-
Support_Rick
Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Patti,
Please send me the JobLog with the error identified. If you can run it in Debug Mode, please do.
I can't tell from just the Project XML what your issue might be. If you can include the File Field Descriptions that would be helpful as well.
You can post them here, or you can email them to
[email protected]e.com and put the work *FORUM* in the Subject line. Then I'll review it.
Thanks!
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
-
pshuey
- Posts: 3
- Joined: Tue Dec 30, 2014 1:31 pm
Rick,
There is no error when I run it, it just does not update any rows. Do you have any sample programs that update a database file based on a .csv?
-
Support_Rick
Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Patti,
If it's not updating, then I would look at the values it's reading and what's it updating against.
For example... I created this Project:
Code: Select all<project name="Acct-REP-REP1" mainModule="Main" version="2.0" logLevel="verbose">
<description>Update sales rep and alternate rep for an account</description>
<module name="Main">
<readCSV label="Read CSV File" inputFile="C:\Users\relliott\Documents\Customers\Forum\Numeric Data.csv" outputRowSetVariable="csvData" skipFirstRow="false" recordDelimiter="CRLF" version="1.0">
<data trim="both" dateFormat="MM/dd/yyyy" timeFormat="HH:mm:ss" timestampFormat="MM/dd/yyyy HH:mm:ss.SSS">
<column index="1" name="ACT" size="9" type="NUMERIC" />
<column index="2" name="REP" size="5" type="NUMERIC" />
<column index="3" name="REP1" size="5" type="NUMERIC" />
</data>
</readCSV>
<sql label="Connect to DB" resourceId="MSSQL Local" autoCommit="true" version="1.0" logLevel="normal">
<query label="Update Data" inputRowSetVariable="${csvData}">
<statement>UPDATE [GAWork].[dbo].[NumericData]
SET [REP] = ?
,[REP1] = ?
WHERE ACT = ?</statement>
<param index="1" mapFrom="2" />
<param index="2" mapFrom="3" />
<param index="3" mapFrom="1" />
</query>
</sql>
</module>
</project>
*Note* .. this is MSSQL, but process is the same.
2 Records in Table:
ACT REP REP1
123456789 111 112
234567890 221 222
And 2 Records in the CSV file:
123456789,11555,12555
234567890,21555,22555
Running the project above, both records are updated with Corresponding CSV Values.
I would look into the actual values that are being updated to make sure. If not, please let me know and we can take a look at it through GotoMeeting and see what's happening.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
