Updating database file based on .csv

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

pshuey

Posts: 3
Joined: Tue Dec 30, 2014 1:31 pm
Post by pshuey » Tue Dec 30, 2014 1:55 pm
Quote
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>
Top

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ
Post by Support_Rick » Wed Dec 31, 2014 11:58 am
Quote
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!
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Top

pshuey

Posts: 3
Joined: Tue Dec 30, 2014 1:31 pm
Post by pshuey » Wed Dec 31, 2014 12:15 pm
Quote
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?
Top

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ
Post by Support_Rick » Wed Dec 31, 2014 3:51 pm
Quote
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
Top
Post Reply
4 posts Page 1 of 1
Return to “Community Forum”