Learn how to format a file for a successful import.
Save time, prevent error, update numerous records at one time and avoid manual data entry with the CSV import process.
One of the most important tasks involved in a new ERP system is pulling clean and accurate data into the system. Users can perform imports by utilizing a Microsoft Excel file, Integrations using APIs or a Comma-separated value file. CSV imports are the best way to transfer small to medium-sized data sets from other applications into NetSuite.
Before importing data into NetSuite, make sure to set up your CSV files carefully by reviewing their formatting to avoid errors and verify that the data is imported with correct values.
Common use cases for CSV import involve:
- Bulk updates to current records
- Integrating external systems
- Bulk creation of records
- Processing large transactional records
How to Prep Files for CSV Import
When getting files ready for CSV import into NetSuite, it's helpful to use some handy tools to make the process smoother. Start by using data cleaning tools or spreadsheet software to thoroughly check and clean the data, making sure it follows NetSuite's import requirements and doesn't have any extra characters or formatting issues. For an organized import, use field mapping tools to align CSV columns with their corresponding NetSuite fields, reducing the chances of data errors. Include unique identifiers in the CSV, like internal IDs or custom fields, to prevent duplicate records.
Keep an eye on date and time formats to match NetSuite standards ('MM/DD/YYYY HH:MM:SS AM/PM'). Breaking down large datasets into smaller batches can improve import performance and make it easier to spot errors. Regularly review import summaries and error logs provided by NetSuite to quickly identify and fix any issues. Test the import process in a sandbox account before doing it in your live NetSuite environment for smoother troubleshooting. Always back up your data to have a safety net in case of unexpected problems.
How to Import CSV Files with the Import Assistant:
Most CSV imports can be made with the Import assistant. If you have the import CSV File permission the import Assistant can be located at Setup > Import/Export > Important Tasks > Import CSV Records.
The roles and permissions in your enabled features determine the kind of data that you can import with the Import Assistant.
STEP 1: SCAN AND UPLOAD
Choose an Import Type: select the category of the record to import from the dropdown list.
Select a Record Type for import from the dropdown list. These record types are separated into categories and listed in order.
Supported Record Types for CSV Import:
The Import Assistant incorporates an Import Type list that allows you to choose the category of data for import. First select the import type and then select the record type of data to import.
The Import Assistant supports the below record types:
• Accounting Import type
• Activities Import Type
• Classification Import Type
• Commumications Import Type
• Custom Records Import Type
• Customization Import Type
• Employees Import Type
• Items Import
• Payment Instruments Import Type
• Relationships Import Type
• Rules Setup Import Type
• Subscription Billing Import Type
• Supply Chain Import Type
• Support Import Type
• Transactions Import Type
• Website Import Type
Note: The data that you can import is based on your permissions, accounts enabled features and your role. In order to use the Import Assistant, you must have the Import CSV File permission.
Choose Import Character Encoding:
On the Scan & Upload File of the Import Assistant, select the type of character encoding for your import. Encoding is based on the type of file you want to import. NetSuite supports the following character encoding:
- (Unicode) UTF-8 encoding – UTF-8 character encoding is the most broadly used format for importing a CSV file internationally.
- Western (Windows 1252) encoding – NetSuite users create CSV files for import in Excel Western (Windows 1252) is the default for the U.S edition.
- Western (ISO-8859-Q) encoding – This encodes Latin alphabet no.1, with 191 characters from the Latin script.
- Chinese Simplified (GB18030) – This is the most widely used character set for the Chinese language.
- Chinese Simplified (GBK) encoding extension – This is used in China and Singapore.
- Traditional Chinese (Big5) encoding – This is used in Macau, Hong Kong and Taiwan.
- Japanese (Shift-JIS) encoding – This is the most widely used format in Japanese for a CSV import file.
- (Western) MacRoman – If you create a CSV file inside Excel on a Mac it will default to MacRoman character encoding.
- Korean (ISO-2022-KR) encoding – This is the character code for Korean text.
- Korean (EUC-KR) encoding – One of the broadly used legacy character encodings.
Choose CSV Column Delimiter:
On the Import Assistant, choose the delimiter used to split the columns in the imported CSV file:
– Tab
– Space
– Comma (,)
– Semicolon (;)
– Pipe(I)
Select a file for import:
On the Scan & Upload CSV page of the Import Assistant, choose which files are the source of the CSV data. Select the option button to indicate whether you are uploading one or more files with a limit of 25,000 records per upload. To select one or more, click the Select button. This will allow you to browse the files you want to import. If you have selected one file for upload, it will only provide one button.
Sublist Data Import:
Linked files might include Sublist data stored apart from the file (for example, an alternative address for a customer or an order record that contains a set of items). You can also use the Import Assistant to import sublist data for some record types and can be included in the same file or a separate file for each sublists data.
If your CSV file was uploaded with an error, a message will appear with the type and number of errors to easily go back and fix the file. It will also provide you with a zip file of the errors. If the file is without error move to step 2.
STEP 2: IMPORT OPTIONS FOR DATA HANDLING
Select Data Handling for Import
Add: Choose the new import records inside NetSuite
Update: Choose this option if the records are currently inside NetSuite and the new import is modifying the records.
Add or Update: Choose these options if imported records involve new and exsisting records inside NetSuite.
Required Permissions for CSV Imports:
In order to import CSV data, it is mandatory that the file permission is assigned to a role. To assign the import permission to the role take these steps:
• Setup > Users/Roles > User Management > Manage Roles
• Select the required role that you would like to change. Choose Edit or Customize.
• Next, head to the permissions tab and select Setup.
• Under the Permissions tab choose Import CSV file.
• Choose the Level dropdown list and select the needed permission level.
• Select Add and Save.
Advanced CSV Import Options
Expand the advanced options menu to set preferences for the current import. The following advanced options are:
1. Log System Notes for Custom Fields: This is a displayed default that allows you to create notes during an import of data from custom fields. Disabling this function for custom fields increases performance and is a suggested update for imports that add data.
2. Overwrite Missing Fields: Enable this function to clean up missing fields mapped to CSV file fields without data associated to them. This is a good idea especially during data updates, this is defaulted as disabled.
3. Validate Mandatory Custom Fields: Enable this option to require custom field data to be included before creating new records. Disable this option if you want to create records without all of the custom field data included. This option is defaulted to enabled.
4. Overwrite Sublists: This feature affects imports of sublist data when these data handling options are selected: Update, Add or update. This is a disabled option by default.
- Overwrite Sublists = True: If this is enabled the Import Assistant replaces all sublist values with CSV file sublist data.
- Overwrite Sublists = False: This feature varies if the sublist import supports selective updates on key values. If the Overwrite Sublist is disabled on keyed sublists, it will update existing data with key field values that match CSV file sublist rows. For non-keyed sublists, if the overwrite sublist is disabled the Import Assistant adds all CSV file data as new rows.
5. Ignore Read-Only Fields: Signify that read-only fields should be ignored during import job processing. This feature must be enabled for imports of entities or contacts together, leads and contacts together, as well as customers and contacts together.
6. Prevent Duplicate Records: Utilize this option to block duplicate import records. When this option is selected, the import of a record is automatically identified as a duplicate and creates a “Duplicate record was detected” error. On the other hand, the other records in the file will continue to process as normal.
7. Custom Multi-Select Value Delimiter: This is used to set a custom character as a delimiter instead of a pipe. The Import Assistant will then interpret the updated custom character to indicate a multi-select relationship for this import. This character will override any other account-level character that is previously set up in your preferences.
8. CSV Decimal Delimiter: If the CSV that is being imported has numbers that use a comma as its decimal mark, this is the page you will want to specify that on. The import assistant expects the period to be used as a decimal on the CSV files. The decimal mark you use will override any other account-level decimal mark within your preferences.
9. Custom Form: When importing data, the field mapping page includes all available NetSuite fields for the record type. However, your preferred form may not have all the fields required for you to import your data. To create a custom form, NetSuite provides the option to select an additional form in the Import Assistant. To view the custom forms available, go to the Advanced options section of the Import Assistant and then to the custom form dropdown.
10. Server Scripting and Workflow Execution: Administrators can select who has the ability to change SuiteScript and workflow triggers on CSV imports and is set to a company preference by default. If this option is available, the Run Server SuiteScript and Trigger Workflows box gives you the option to update the setting of an import and allows scripts and triggers to run during the import. If you clear the box it will disable all scripts and triggers during an import. After saving an import, the preference for this field will be saved.
11. Use Multi-Threading: This option enables multiple threads to process the job instead of the defaulted single thread process. Utilize this feature if the rows in your CSV do not need to be imported in order. * This is only available to NetSuite accounts that have a SuiteCloud Plus license.
12. Queue Number: Every import is allocated to queue 1. To improve performance many users, like to spread import processing between multiple queues 2-5 to increase performance. The queue selected is shown in the queue column on the import job status page. If you would like to edit the import queue number, you need to cancel the job and resubmit with the desired queue number.
Checking CSV Import status:
After a job is completed, a notification is then sent to the email used to log in when the import was submitted. To view the csv import job status page, go to setup > Issues > Issue Management > Import Issue Records> Status (Administrator).
STEP 3: FILE MAPPING
- Choose a primary file key column to signify the way that data is mapped within the file and others that are linked.
- Designate a key column from the linked files to map the primary key column. This step is mandatory if you are uploading multiple files for import.
STEP 4: FIELD MAPPING
This step will display the automatic field mappings of the CSV file fields to NetSuite fields. The field mappings will be in the center of the pane, the CSV file fields are on the left, and if there are blank spaces these will need to be mapped. If a field is required, it will be labeled with a Req.
CSV Field mapping tasks:
Highlight a line in the middle pane and click a field in the right or left pane. You can also drag fields from any of the panes to move them around.
Assigning default values during field mapping:
The import assistant creates dependent field values when related field values are configured and can also be automatically populated in the user interface. NetSuite provides the ability to assign default values for required fields as a consistent value for a record reference field across all imported records.
To create a default value for a field, go to the field mapping page, click edit, select Provide Default Value, and choose a value from the dropdown list. This list depends on the number of values you have set. To edit your Maximum Entries list, go to Home > Set Preferences.
Sublist Data Import:
NetSuite records can include different sets of associated data called sublists, as each sales order record consists of a set of items. With the import assistant, NetSuite allows you to import sublist data for specific record types and gives you the option to be included in the same file.
If you prefer, you can upload separate files for each sublist’s data as well. If fields are not mapped for a sublist, data will not be imported, even after setting a default value. Make sure you fill out the required fields for sublist import mapping.
Single File Upload: Required fields do not show up in the center pane of the Field Mapping page and are not required for import. The import Assistant assumes that you are not uploading sublist data.
Multiple File Upload:Required fields show will be displayed in the center pane of the Field Mapping page. One primary file for the record type and a linked file (one or more) for the subsists are required to move forward.
Customizing and Creating NetSuite Roles:
To customize a role go to Setup > Users/Roles > User Management > Manage Roles
Select Customize. This role inherits all the normal permission and gives you the ability to make changes as needed.
In order to create a new role without permissions go to Setup > Users/Roles > User Management > Manage Roles > New (Administrator).
Basic Role Information:
• Enter the name of a custom role (one that is easily recognizable to assign to others) in the Name field. You can also enter an ID used for this role if you use scripting.
• If you are creating a new role, the center type needs to be selected to base the role on. This sets default permissions and access that you can customize.
How to make a copy of an existing standard or custom role:
• Make sure you are in an Administrator role and go to Setup > Users/Roles > User Management > Manage Roles (Administrator).
• Select Customize or Edit on the role you want to duplicate
• Rename it in the Name field
• Click the drop down and click Save As.
Protelo’s award-winning NetSuite experts are here to assist you today!
Interested in finding out more about how NetSuite can help automate your import processes?
The truth is, we’ve only scratched the surface! Please contact us for on-demand support and the answers to your questions. Make sure to check out other helpful tips, tricks, and how-to's on our NetSuite tips blog.
Whether you are looking for a one-stop-shop for all things NetSuite, or simply need answers to your questions, our award-winning NetSuite experts are here to make your business better. We provide support, administration, and process optimization to ensure organizations get a return on their investment.
Contact Protelo’s NetSuite Team Today
RELATED NETSUITE PAGES
Have NetSuite questions?
We are not your typical NetSuite partner. Our services come with no minimum hours per month, no up-front costs, on-demand access to experts, and a fully U.S.-based team with extensive NetSuite experience.
We are leading NetSuite experts that can:
• Build Custom Workflows and SuiteScript
• Set-up Advanced Searches and Reporting
• Customize NetSuite & Automate Processes
• Provide Implementation Support
• Augment your NetSuite Team
• Build Custom Reporting
• And More
Follow us on Social for NetSuite tips, tricks and ERP fun:
YouTube, LinkedIn, Twitter, Pinterest, Instagram, and Facebook
