Updating database file based on .csv
<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 Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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 Support@Goanywhere.com and put the work *FORUM* in the Subject line. Then I'll review it.
Thanks!
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
- Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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:
<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>
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.
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696