NetSuite CSV import files, cleaned and deduped before the Import Assistant runs

NetSuite documents its CSV rules in the help center, and most failed jobs break one of a short list. The Import Assistant takes up to 25,000 records or 50 MB per job, and on a multiple file upload the files together have to stay under that. You reach it at Setup, Import/Export, Import CSV Records, with the Import CSV File permission, and it walks you through five steps: scan and upload, import options, file mapping for multi file jobs, field mapping, then save and run.

The rules that cause the most rework are about values rather than structure. Reference fields have to be written exactly as they appear in the dropdown on the NetSuite form. States have to use the short names your account is set up with, and NetSuite asks for country names rather than country codes. Dates are validated against the date format in the preferences of the user running the import, which is MM/DD/YYYY on a default US account. And if two columns share a header, only the second one is imported.

NetSuite has duplicate detection for customers, contacts, partners and vendors, and by default it looks for duplicates by email address. When the Duplicate Detection and Merge feature is on and at least one record type is set to be checked, page two of the Import Assistant shows a Prevent Duplicate Records box under Advanced Options. Tick it and any row NetSuite considers a duplicate fails with the error Duplicate record was detected. Leave it unticked and the row is created and marked as a duplicate, to be sorted out later under Lists, Mass Updates, Entity Duplicate Resolution.

Both outcomes rely on NetSuite recognizing the pair. A customer whose email is missing on one row, or who arrived as Keystone Fabrication Inc from one system and Keystone Fabrication from another, can pass an email check and land twice. Merging afterwards has its own limits too: NetSuite will only merge two records with the same tax registration number or none, and in OneWorld it will not merge records that belong to different subsidiaries. So a duplicate that gets in is not always one click to remove.

The cheaper place to catch it is the file. The engine here compares email after trimming and lowercasing, then name and company after normalizing legal suffixes such as Inc, LLC and Corp, and proposes each group as a cluster with its reason. That is the same fuzzy matching used on every other page of this site, pointed at the rows you are about to import.

Use Add for a first load into an empty record type, and Add or Update once records exist. Update and Add or Update match rows to existing records on a key field such as the internal ID or the External ID, so the key column has to be clean and unique. A duplicate in the key column updates one record twice and leaves its twin untouched.

That is why deduplication comes before the data handling choice rather than after it. A file with one row per real customer and one External ID per row behaves predictably under any of the three options. A file with duplicates behaves differently under each, and none of them warns you.

Customer files get the attention, but vendor imports carry the most expensive duplicates. Two vendor records for one supplier split the spend, split the 1099 totals and let the same invoice be paid twice, once against each record. The same pass works on a vendor list: supplier names normalized, emails trimmed, remit to states and ZIP codes put back into shape, and near duplicate suppliers grouped for review. One caution from our own testing: give the vendor number column a plain header such as ID, because a header like Vendor ID is read as a company column and weakens the matching. The longer write up on vendor files is vendor master data cleansing software.

Item imports are mostly a formatting job. Prices with currency symbols, SKUs that lost their leading zeros in Excel and units written three ways all fail or import wrongly. The engine fixes currency and leading zeros on columns it recognizes, and anything it is unsure of is shown to you with a confidence label rather than changed quietly. Keeping those lists clean after the go live is ordinary data hygiene.

If you are importing transactions with sublists, rebuilding a chart of accounts, or moving history for several subsidiaries, a NetSuite partner earns the fee. Their work covers mapping, sequencing and testing well beyond one file. If the same data has to flow into NetSuite every day from another system, an integration platform or a scheduled import through SuiteScript is the right tool, because a person should not be cleaning a file every morning.

DataUntangler fits the job in between: a customer, contact, vendor or item file that has to go in clean this week, where somebody will be asked afterwards what was changed. It does not connect to NetSuite and does not claim to. It is a data cleaning tool that gives you the cleaned CSV and a log of every approved change, on the Analyst plan at 59 dollars a month with unlimited files up to about 100,000 rows each. The broader version of this workflow, for any file headed into any system, is data cleaning software.