Understanding & Improving the CSV Import & Export Process in NetSuite
NetSuite CSV imports and exports allow users to update large quantities of existing records or add new ones to NetSuite. Without the ability to do this, NetSuite users would have to add or edit data line by line, which would be very manual, inefficient, and time-consuming. CSV is a file format that stores tabular data in plain-text form. CSV files consist of rows and columns that contain data. Each row represents a record and each column represents a field of that record.
What are NetSuite CSV Imports & Exports?
If NetSuite users need to transfer small or medium volumes of data from other applications into NetSuite, CSV imports are typically used. To implement a CSV import, first, the user needs to confirm the permissions required to do so. Data is put into CSV format, fields are mapped to the corresponding NetSuite fields or set to be ignored, and the import is run.
CSV exports for NetSuite work by running a saved search and saving the results in a CSV file. The user can customize the data they would like to include in the exported file. Users can choose to export data from a multitude of NetSuite modules, such as Inventory, Accounting, CRM, and more. The export can contain sales orders, transactions, customer records, purchase orders, or any other type of data available in the user’s NetSuite instance.
Common Reasons for CSV Imports
- Migrating to NetSuite: When a company is migrating to NetSuite and has data that resides in other systems, it must have a mechanism to get the data into NetSuite. This is often completed via CSV import. Customers, vendors, and even past transactions can be imported, although importing past transactions is not recommended.
- Company Acquisitions: When companies acquire another company, they inherit another data set that needs to be integrated with their own. CSV imports can be used to ensure the data gets into NetSuite in the appropriate format.
- Keeping Items Up-to-Date: For items that change often, such as prices or descriptions, CSV imports can be used to address them faster than it would be possible to do manually.
Common Reasons for CSV Exports
- Migrating Data from NetSuite to Another Platform: If a company has a need to utilize data that originates from NetSuite in another platform, such as another ERP system, it may use a CSV export to get the data out of NetSuite.
- Sharing Data with Third-Party Applications: When a business uses third-party applications to consume or analyze data that originates from NetSuite, CSV exports can help them access the data within those applications.
- Generating Reports: CSV exports can be used to generate reports in another application, such as Excel. The drawback to this and other reasons for exporting CSVs is that the data is static. If an analysis needs to be completed more than once, all of the steps for exporting will need to be completed again.
Common Issues and Errors with CSV Imports
One of the key features of NetSuite is its ability to import data from various sources using CSV files. However, importing data can be a challenging task, and errors can occur during the process. These are some of the most common NetSuite CSV import error messages and how to correct them.
- Size of file: A file that is too large is one of the most common CSV import issues. The reasons can vary, from too many columns or rows to too many fields in the file. The best course of action is to take the file and separate it into smaller files and try to import it again.
- Invalid date or amount format: The second common error message you may encounter is “Invalid Date” or ”Invalid Amount.” This error occurs when the amount in the CSV file is not in the correct format. To fix this error, you need to ensure that the amount in your CSV file is in the correct format. In NetSuite, the currency format is set in Setup > Company > General Preferences > Accounting > Currency or Setup > Company > General Preferences > Date & Time. You need to ensure that the format in your CSV file matches the format set in NetSuite.
- Invalid email address or country code: The third common error message you may encounter is “Invalid Email Address” or “Invalid Country Code.” This error occurs when the email address or country code in the CSV file is not in the correct format. To fix this error, you need to ensure that the email address and country code in your CSV file is in the correct format. The correct format for a country code is a two-letter code such as “US” for the United States. The correct format for an email address is “[email protected].”
- Mapping Errors: Errors can happen if column names are missing altogether or if they do not match what is in NetSuite. The name must be present and match what is in the database to fix this.
- Error handling: If there are errors in your CSV upload you will not know until the upload is complete and you receive an email with an Excel attachment that contains your errors. These then need to be fixed and then the upload process needs to be repeated.
- Missing data: The final common error on imports is when there is missing data. If a zip code is missing but a city and state are present, it could cause an error. The same can happen if a month is there but a day or year is missing. The best way to fix this is to ensure the data is complete and accurate before importing.
Common Errors with CSV Exports
Since CSV exports often originate with a saved search, if there are issues with the saved search, it may cause issues with the output of the CSV file. If errors occur it can be difficult and time-consuming to figure out why they happened in the first place.
CSV exports also require that users manually download their search results either as a CSV file or as an XLM spreadsheet. Adding this data to existing Excel workbooks for analysis requires manually repeating the process and a fair amount of copying and pasting data.
CSV Import & Export Alternative
CSV imports and exports are commonly used for extracting data from NetSuite but they come with some drawbacks. The first drawback is that there are many steps. From taking the time to assemble the data, perform calculations, save as a CSV, login into NetSuite, upload the file (for imports), map the fields, run the import, monitor the queue, and check for errors. At any one of these steps, there could be issues. Validation of the data does not come until the very last stage of this process which can also be problematic.
Using an Excel-based application that integrates with NetSuite, such as CloudExtend Excel for NetSuite, allows users to bypass all of these steps. By connecting dynamic data in NetSuite with Excel, CSV imports and exports are no longer needed. Saved searches can be streamed directly into Excel and data can be updated right from the Excel interface. Data is validated from Excel so the user is aware in advance if the data produces an error. Try it here.