Updating database file based on .csv

[pshuey]

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]

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 Support@Goanywhere.com and put the work *FORUM* in the Subject line. Then I'll review it.

Thanks!

[pshuey]

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]

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.