Re: Import .csv with duplicates to AR Form via AIE
Since you can't alter the source of the CSV. How about a Script (Perl Script is my favorite) that runs to insert unique keys prior to slurping the data with AIE? OR a small little java api program that can run to insert the Data into your forms, an api program won't care about unique keys. On Feb 7, 6:19 pm, strauss stra...@unt.edu wrote: Off the wall - our AIE implementation for CTM:People gets updates from LDAP into a separate SQL Server db all day (and night) as changes post in PeopleSoft. When things are popping (student registration and new employee hires at the beginning of each semester), there may be many updates to the same person's record over several hours, sometimes within the AIE processing cycle of 1 hour. They are transparent to AIE because the duplicate updates post to a single record in the SQL Server table and AIE only sees one record with an update flag. AIE then pushes to a custom form like you described, updating or creating records as appropriate. You may need to use an external db table that you can push your .csv file through in order to combine the duplicate entries into a single row. Just an idea; for scale, we currently maintain over 297,000 records this way (external SQL table - custom form - CTM:People/User/CTM:People Permission Groups). Christopher Strauss, Ph.D. Call Tracking Administration Manager University of North Texas Computing IT Centerhttp://itsm.unt.edu/ From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Nathan Aker Sent: Thursday, February 02, 2012 8:46 AM To: arsl...@arslist.org Subject: Import .csv with duplicates to AR Form via AIE ** Greetings Listers, I'm stuck on an AIE limitation that I can't seem to get around. I want to do a nightly file import from a .csv file to a target custom AR form. Problem is the .csv file contains duplicate entries and there is no way to get a completely unique key (even using a composite key from multiple columns in the .csv file). After much testing, frustration, and retesting I have found that AIE cannot (successfully) import from a .csv file without a unique key. Period. It's not that it simply will overwrite records where duplicates are encountered, it gets completely confused on the number of records to import and only processes approx. 1/3 of the total data. I've tried every combination of configurations/queries/keys in the AIE mapping and exchange entries. For reasons I won't detail, I don't have the option of altering the source of the .csv file to insert a unique ID or to filter duplicates out. I'm racking my brain to figure out some script or batch file I can create to run prior to the exchange which inserts a unique column (a counter basically) into the .csv file so that AIE can import it. I've validated that I can manually add a unique column to the .csv and everything imports as expected. Anyone have a clever way to insert a unique column to a .csv file programmatically, or a way to get Remedy to import a .csv nightly which contains non-unique data? I'm trying to avoid a less desireable solution involving running the import tool from workflow as I believe it would require me to pass Admin credentials in the command line to run the import tool. The security implications there concern me. Thanks. Nate. Nathan Aker ITSM Solution Architect McAfee, Inc. 5000 Headquarters Drive Plano, TX 75024 _attend WWRUG12www.wwrug.comhttp://www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org attend wwrug12www.wwrug12.comARSList: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Import .csv with duplicates to AR Form via AIE
Off the wall - our AIE implementation for CTM:People gets updates from LDAP into a separate SQL Server db all day (and night) as changes post in PeopleSoft. When things are popping (student registration and new employee hires at the beginning of each semester), there may be many updates to the same person's record over several hours, sometimes within the AIE processing cycle of 1 hour. They are transparent to AIE because the duplicate updates post to a single record in the SQL Server table and AIE only sees one record with an update flag. AIE then pushes to a custom form like you described, updating or creating records as appropriate. You may need to use an external db table that you can push your .csv file through in order to combine the duplicate entries into a single row. Just an idea; for scale, we currently maintain over 297,000 records this way (external SQL table - custom form - CTM:People/User/CTM:People Permission Groups). Christopher Strauss, Ph.D. Call Tracking Administration Manager University of North Texas Computing IT Center http://itsm.unt.edu/ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Nathan Aker Sent: Thursday, February 02, 2012 8:46 AM To: arslist@ARSLIST.ORG Subject: Import .csv with duplicates to AR Form via AIE ** Greetings Listers, I'm stuck on an AIE limitation that I can't seem to get around. I want to do a nightly file import from a .csv file to a target custom AR form. Problem is the .csv file contains duplicate entries and there is no way to get a completely unique key (even using a composite key from multiple columns in the .csv file). After much testing, frustration, and retesting I have found that AIE cannot (successfully) import from a .csv file without a unique key. Period. It's not that it simply will overwrite records where duplicates are encountered, it gets completely confused on the number of records to import and only processes approx. 1/3 of the total data. I've tried every combination of configurations/queries/keys in the AIE mapping and exchange entries. For reasons I won't detail, I don't have the option of altering the source of the .csv file to insert a unique ID or to filter duplicates out. I'm racking my brain to figure out some script or batch file I can create to run prior to the exchange which inserts a unique column (a counter basically) into the .csv file so that AIE can import it. I've validated that I can manually add a unique column to the .csv and everything imports as expected. Anyone have a clever way to insert a unique column to a .csv file programmatically, or a way to get Remedy to import a .csv nightly which contains non-unique data? I'm trying to avoid a less desireable solution involving running the import tool from workflow as I believe it would require me to pass Admin credentials in the command line to run the import tool. The security implications there concern me. Thanks. Nate. Nathan Aker ITSM Solution Architect McAfee, Inc. 5000 Headquarters Drive Plano, TX 75024 _attend WWRUG12 www.wwrug.comhttp://www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Import .csv with duplicates to AR Form via AIE
Greetings Listers, I'm stuck on an AIE limitation that I can't seem to get around. I want to do a nightly file import from a .csv file to a target custom AR form. Problem is the .csv file contains duplicate entries and there is no way to get a completely unique key (even using a composite key from multiple columns in the .csv file). After much testing, frustration, and retesting I have found that AIE cannot (successfully) import from a .csv file without a unique key. Period. It's not that it simply will overwrite records where duplicates are encountered, it gets completely confused on the number of records to import and only processes approx. 1/3 of the total data. I've tried every combination of configurations/queries/keys in the AIE mapping and exchange entries. For reasons I won't detail, I don't have the option of altering the source of the .csv file to insert a unique ID or to filter duplicates out. I'm racking my brain to figure out some script or batch file I can create to run prior to the exchange which inserts a unique column (a counter basically) into the .csv file so that AIE can import it. I've validated that I can manually add a unique column to the .csv and everything imports as expected. Anyone have a clever way to insert a unique column to a .csv file programmatically, or a way to get Remedy to import a .csv nightly which contains non-unique data? I'm trying to avoid a less desireable solution involving running the import tool from workflow as I believe it would require me to pass Admin credentials in the command line to run the import tool. The security implications there concern me. Thanks. Nate. Nathan Aker ITSM Solution Architect McAfee, Inc. 5000 Headquarters Drive Plano, TX 75024 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Import .csv with duplicates to AR Form via AIE
Meta-Update does this implicitly as you code an Update query and if it already exists and no data is changing then by default no update is issued. So, say you have two CSV records and the CI in question already exists before the CSV is actually processed. Then, when Meta-Update encounters the first record and processes all assignments, if nothing is changed no update is performed. When it encounters the second record and processes the assignments, again, no update will be performed if no values are changing. You can override this behaviour so that the update is always performed. If so, Meta-Update still functions correctly because the same record is update in both cases. The AIE limitation is due to the design of AIE. There is an adapter to make the CSV behave like a database and so a unique key must be specified. Further, a single AIE job can only process CSVs with CIs going in the same class and cannot add associations etc in addition to the CI. Meta-Update processes a CSV as a CSV and can update any class on a row by row basis (determining the class based on CSV row data), can reference other CSVs for value transformations (including determination of the target classes), and can also update addition data such as other classes, relationships, and associations. An example of a such a CSV was described at the Las Vegas RUG and can be looked at here: http://www.softwaretoolhouse.com/product/SthMupd/wwrug2010.html Trial licenses are limited only by time and not functionality or numbers of records. Scripts close to what you need probably exist and are no charge. Trial licenses come with a Live Install service where Meta-Update is installed and some samples are executed. Please feel free to contact us for further information. Cheers Ben Chernys Senior Software Architect Software Tool House Inc. Canada / Deutschland / Germany Mobile: +49 171 380 2329GMT + 1 + [ DST ] Email:mailto:Ben.Chernys%20_AT%20_%20softwaretoolhouse.com Ben.Chernys _AT_ softwaretoolhouse.com Web: http://www.softwaretoolhouse.com/ www.softwaretoolhouse.com Check out Software Tool House's free Diary Editor. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. http://www.softwaretoolhouse.com/ http://www.softwaretoolhouse.com/ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Nathan Aker Sent: February-02-12 15:46 To: arslist@ARSLIST.ORG Subject: Import .csv with duplicates to AR Form via AIE ** Greetings Listers, I'm stuck on an AIE limitation that I can't seem to get around. I want to do a nightly file import from a .csv file to a target custom AR form. Problem is the .csv file contains duplicate entries and there is no way to get a completely unique key (even using a composite key from multiple columns in the .csv file). After much testing, frustration, and retesting I have found that AIE cannot (successfully) import from a .csv file without a unique key. Period. It's not that it simply will overwrite records where duplicates are encountered, it gets completely confused on the number of records to import and only processes approx. 1/3 of the total data. I've tried every combination of configurations/queries/keys in the AIE mapping and exchange entries. For reasons I won't detail, I don't have the option of altering the source of the .csv file to insert a unique ID or to filter duplicates out. I'm racking my brain to figure out some script or batch file I can create to run prior to the exchange which inserts a unique column (a counter basically) into the .csv file so that AIE can import it. I've validated that I can manually add a unique column to the .csv and everything imports as expected. Anyone have a clever way to insert a unique column to a .csv file programmatically, or a way to get Remedy to import a .csv nightly which contains non-unique data? I'm trying to avoid a less desireable solution involving running the import tool from workflow as I believe it would require me to pass Admin credentials in the command line to run the import tool. The security implications there concern me. Thanks. Nate. Nathan Aker ITSM Solution Architect McAfee, Inc. 5000 Headquarters Drive Plano, TX 75024 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
