Difference between revisions of "Autoloader File Preparation"
(→Syntax) |
|||
| (16 intermediate revisions by 3 users not shown) | |||
| Line 2: | Line 2: | ||
==Use Cases== | ==Use Cases== | ||
| − | * Using ''Merge Control'', you can merge two or more columns in your csv upload file so that their data is | + | * Using ''Merge Control'', you can merge two or more columns in your csv upload file so that their data is loaded into one SmartSimple field. |
| − | * Using ''Constant'', | + | * Using ''Constant'', you can set a constant value to be uploaded to a specific field for all records. You can also use this feature with an [[autonumber]] field to add sequential autonumbers to added or updated records. |
| − | :[[Image:AutoFilePreparation.png]] | + | : [[Image:AutoFilePreparation.png]] |
| + | ==Merge Control== | ||
| + | Example: For an autoloader with four columns, where the first column updates and matches by the ''Owner'' standard field, the format of the uploaded file must be ''firstname [space] lastname''. However, the csv file being used for the upload contains two columns, one containing the first name and one containing the last. | ||
| + | |||
| + | It is a trivial matter to concatenate these two columns in Excel or other spreadsheet programs; however, the conversion process from Excel back to csv may introduce errors into the upload file. As well, if the file is uploaded to the SmartFolder automatically to trigger the autoloader process, there will not be an opportunity for this intervention. | ||
| + | |||
| + | [[Image:AutoLoaderColumnMapping.png|link=|400px]] | ||
| − | + | ||
| + | The solution is to use ''Merge Control'' to concatenate the first name and last name into one column. In this example, we've entered ''1= ,2,3''. This means that the first column will be replaced by the value of columns 2 and 3, separated by a space. | ||
| + | |||
| + | |||
| + | [[Image:AutoloaderFilePrepMergeControl.png|link=|400px]] | ||
===Syntax=== | ===Syntax=== | ||
| + | <!--see 139885 - Autoloader - Merge Control of columns is not working properly--> | ||
| + | : [column number to be replaced]=[separator],[column 1],[column 2],...,[column n] | ||
| + | Multiple Merge Controls can be used in the same autoloader, separated by double semicolons. | ||
| + | |||
| + | Examples: | ||
| + | |||
| + | '' '''Case 1''': 1= ,2,3 '' | ||
| + | |||
| + | '' ''If Column 2="FirstName" and Column 3="LastName" then Column 1 = "FirstName LastName" | ||
| + | |||
| + | where the separator used is a space | ||
| + | |||
| + | '' '''Case 2''': 1=-,2,3 '' | ||
| + | |||
| + | '' ''If Column 2="ABCD" and Column 3="123456" then Column 1 = "ABCD-123456" | ||
| + | |||
| + | where the separator used is a dash | ||
| + | |||
| + | '' '''Case 3''': 10=|,4,5,6'' | ||
| + | |||
| + | '' ''If Column 4="Value4", Column 5="Value5" and Column 6="Value6" then Column 10 = "Value4|Value5|Value6" | ||
| + | |||
| + | where the separator used is a pipe delimiter | ||
| + | |||
| + | '' '''Case 4''': 4=,1'' | ||
| + | |||
| + | '' ''If Column 1="123456" then Column 4 = "123456" | ||
| + | |||
| + | where there is no separator used | ||
| + | |||
| + | '' '''Case 5''': 10=-,1,2;;11=-,3,4'' | ||
| + | |||
| + | '' ''The file will have two new columns: Column 10 and Column 11. | ||
| + | |||
| + | '' ''If Column 1="222" and column 2="111" then Column 10 = "222-111" AND | ||
| + | |||
| + | If Column 3="Value3" and column 4="Value4" then Column 11 = "Value3-Value4" | ||
==Constant== | ==Constant== | ||
| + | An Autoloader Constant can be defined to update your autoloaded records to a single, constant value. As above, it is straightforward to create a single upload file with the same value in each row for a column. But when dealing with multiple uploads that do not already contain a constant value, or automatically-uploaded files, using the Autoloader Constant is the recommended method. | ||
===Syntax=== | ===Syntax=== | ||
| + | : [column number to be replaced]=constant value ''or'' [column number to be replaced]=~ ''(for autonumber increments)'' | ||
| + | : Multiple Merge Controls can be used in the same autoloader, separated by double semicolons. Example: ''2=~;;4=12345'' | ||
| + | ===Constant value=== | ||
| + | To populate your uploaded records with a constant value, simply indicate the column number of the destination field followed by the equals sign and your constant value. Example is to populate the Status of imported users to "Inactive" with statusid=12345. In the '''File Layout''' tab, map item 4 for '''* Status ID''' and in the '''Process''' tab under the '''File Pre-Processing''' section add in the '''Constant''' textbox: '''4=12345'''. | ||
| + | ===Autonumber=== | ||
| + | The Autoloader Constant can also be used to increment an [[autonumber]] field for each uploaded record. For example, if the second column mapping in your autoloader has an autonumber field selected as its destination, enter ''2=~'' into the ''Constant'' section of the Autoloader settings. | ||
| − | When a ''Merge Control'' or ''Constant'' is defined, | + | ===Current Date=== |
| + | <!--Ticket#88969 - Autoloader enhancement for HR Feed--> | ||
| + | * <span style="color: #ff0000;">Available after November 2019 upgrade.</span> | ||
| + | To populate your uploaded records with the current date, simply indicate the column number of the destination field followed by the equals sign and the current date: now(). Example: ''5=now()''. | ||
| + | |||
| + | ==Resulting Files== | ||
| + | When a ''Merge Control'' or ''Constant'' is defined, once the import file is uploaded to a SmartFolder, the file will be altered according to the rules established in the ''Merge Control'' or ''Constant'' settings and a new upload file will be created with the prefix "mod_". | ||
[[Image:AutoloaderModifiedUploadFile.png|link=|350px]] | [[Image:AutoloaderModifiedUploadFile.png|link=|350px]] | ||
| + | |||
| + | Both the original upload file and the file modified by the Autoloader File Preparation processes will be available for review in the SmartFolder. | ||
==See Also== | ==See Also== | ||
| − | *[[Autoloader]] | + | * [[Autoloader]] |
| − | [[Category: | + | [[Category:Data Import]] |
Latest revision as of 14:03, 16 February 2023
This section of the Autoloader settings page allows for manipulation of the data prior to the Autoloader actually loading the data onto the system.
Contents
Use Cases
- Using Merge Control, you can merge two or more columns in your csv upload file so that their data is loaded into one SmartSimple field.
- Using Constant, you can set a constant value to be uploaded to a specific field for all records. You can also use this feature with an autonumber field to add sequential autonumbers to added or updated records.
Merge Control
Example: For an autoloader with four columns, where the first column updates and matches by the Owner standard field, the format of the uploaded file must be firstname [space] lastname. However, the csv file being used for the upload contains two columns, one containing the first name and one containing the last.
It is a trivial matter to concatenate these two columns in Excel or other spreadsheet programs; however, the conversion process from Excel back to csv may introduce errors into the upload file. As well, if the file is uploaded to the SmartFolder automatically to trigger the autoloader process, there will not be an opportunity for this intervention.
The solution is to use Merge Control to concatenate the first name and last name into one column. In this example, we've entered 1= ,2,3. This means that the first column will be replaced by the value of columns 2 and 3, separated by a space.
Syntax
- [column number to be replaced]=[separator],[column 1],[column 2],...,[column n]
Multiple Merge Controls can be used in the same autoloader, separated by double semicolons.
Examples:
Case 1: 1= ,2,3
If Column 2="FirstName" and Column 3="LastName" then Column 1 = "FirstName LastName"
where the separator used is a space
Case 2: 1=-,2,3
If Column 2="ABCD" and Column 3="123456" then Column 1 = "ABCD-123456"
where the separator used is a dash
Case 3: 10=|,4,5,6
If Column 4="Value4", Column 5="Value5" and Column 6="Value6" then Column 10 = "Value4|Value5|Value6"
where the separator used is a pipe delimiter
Case 4: 4=,1
If Column 1="123456" then Column 4 = "123456"
where there is no separator used
Case 5: 10=-,1,2;;11=-,3,4
The file will have two new columns: Column 10 and Column 11.
If Column 1="222" and column 2="111" then Column 10 = "222-111" AND
If Column 3="Value3" and column 4="Value4" then Column 11 = "Value3-Value4"
Constant
An Autoloader Constant can be defined to update your autoloaded records to a single, constant value. As above, it is straightforward to create a single upload file with the same value in each row for a column. But when dealing with multiple uploads that do not already contain a constant value, or automatically-uploaded files, using the Autoloader Constant is the recommended method.
Syntax
- [column number to be replaced]=constant value or [column number to be replaced]=~ (for autonumber increments)
- Multiple Merge Controls can be used in the same autoloader, separated by double semicolons. Example: 2=~;;4=12345
Constant value
To populate your uploaded records with a constant value, simply indicate the column number of the destination field followed by the equals sign and your constant value. Example is to populate the Status of imported users to "Inactive" with statusid=12345. In the File Layout tab, map item 4 for * Status ID and in the Process tab under the File Pre-Processing section add in the Constant textbox: 4=12345.
Autonumber
The Autoloader Constant can also be used to increment an autonumber field for each uploaded record. For example, if the second column mapping in your autoloader has an autonumber field selected as its destination, enter 2=~ into the Constant section of the Autoloader settings.
Current Date
- Available after November 2019 upgrade.
To populate your uploaded records with the current date, simply indicate the column number of the destination field followed by the equals sign and the current date: now(). Example: 5=now().
Resulting Files
When a Merge Control or Constant is defined, once the import file is uploaded to a SmartFolder, the file will be altered according to the rules established in the Merge Control or Constant settings and a new upload file will be created with the prefix "mod_".
Both the original upload file and the file modified by the Autoloader File Preparation processes will be available for review in the SmartFolder.
