Edit import data with VB Script

If the data to be imported does not have the correct layout, you can edit it using VB Script.

The fields to be imported might not have the correct layout. Some examples:

• Postal code and city are included in a single field, whilst Profit expects you to provide these data separately.
• You want to edit codes, for example, because you want to place the letter D before every (numerical) debtor code and the letter M before every employee code.

For a one-off import, it is often easier to pre-edit the data in Microsoft Excel, for example, as Excel offers lots of functionality to split or combine fields and to perform search-and-replace actions on columns.

For import actions that you perform on a regular basis, you can have actions performed by including a script in the Import wizard. For this purpose, you use the VB Script language.

Create VB script

Include a VB script in the import definition. This requires knowledge of VB.

Configure VB script:

1. Start the import function.
2. Complete the steps in the wizard until you get to the step for recording the script.
3. Select the Use script check box.
4. Write the script in the Script editor box.

   Insert the fields via the search lists with fields (top-left). You have two lists, one with fields in Profit and one with fields in the import file. If you insert fields via these lists, they are immediately inserted in the script editor with the correct syntax.

5. You can enter a comment if it is preceded by a " (double quotation mark).
6. Check the script with the Test script button. If the script contains an error, an error message is displayed, allowing you to correct the script. If you find no errors, you will also be notified of this.

   Note: 

   The Test script button is only used to perform a technical check. You cannot use it to check if the action actually yields the desired result. Always test an import definition in a test environment to check the result.

7. Reset script allows you to delete the changes to the script.
8. Complete the rest of the import procedure.

Example of a VB script:

The import file contains sales contacts with a numerical code; these codes are located in the second field in the file. However, you also want all the sales contacts in Profit to receive a code that starts with the letter D, followed by the code from the import file.

1. Place the cursor on the empty line under Sub Import()
2. Click DbId:Debiteurnummer (Verkooprelatie) in the (To fields) search list.
3. As a result, the ToFields("DbId").value field name is inserted in the script.
4. Place the cursor behind this field name
5. Enter: = D +
6. Open the (From fields) search list.
7. Click the field with the debtor code to be imported.
8. As a result, the FromFields("VELD_2").Value field name is inserted.

   The VB script is ready. The full text of the script is:

   Sub Import()

   ToFields("DbId").Value = "D" + FromFields("VELD_2").Value

   End Sub

Import memo field with several lines with script

It is possible to import several lines to a single memo field, placing the lines one after the other rather than adding them together. You can do this via a script in the import definition.

Import memo field with several lines with script:

1. Place a certain symbol at the end of the lines in the .csv file to be imported. Use a unique symbol for this that does not appear anywhere else in the file, for example, # or ^.
2. Enable the script option in the import definition via the import wizard.
3. You could, for example, include the following line in the script. This script will then replace the symbol in question by a carriage return (vbcrlf).

   Example: 

   Using # as a separator:

   <veld>=replace(<veld>,"#",vbCrLf)

   If the export only includes a line feed for a carriage return in a memo field (often shown as a little square in Profit), you can replace the line feed with crlf:

   <veld>=replace(<veld>,vbLf,vbCrLf)