NetSuite, Implementation, Data Migration, Planning
7 min Read
There are two ways to load customer and vendor addresses into NetSuite: import the addresses in the same file as the entity record, or import the entities first and attach the addresses in a second update import. Use the single-file method when every entity has an address. Use the two-step method when some do not, because NetSuite will otherwise create an empty address record for every entity on the file.
Entity addresses are usually the first thing a NetSuite implementation partner asks for, so getting the format right early keeps the rest of the project moving. Below is how to pull address data out of QuickBooks Online, how each import method works, and the cleanup steps that prevent the most common failures.
TL;DR
- QuickBooks Online stores two customer addresses (billing and shipping) and one vendor address, all on a single line of the contact report.
- Import addresses on the entity file when every record has an address. Import entities first, then attach addresses for those that do not have them.
- Country is a required NetSuite field, and the value has to match NetSuite's country list exactly.
- Use NetSuite's address override when you cannot cleanly split a legacy address into its components.
How does QuickBooks Online store customer and vendor addresses?
QuickBooks Online gives customers two addresses and vendors one. Customers have a bill-to address and a ship-to address, and QuickBooks puts both of them on a single line of the customer contact report. NetSuite handles this differently, which is the detail that most often trips up a first CSV template.
For every address in QuickBooks Online, the individual components are:
- Street
- City
- State
- Zip
- Country
How do you export customer and vendor addresses from QuickBooks Online?
Both exports come from the Reports page, and both require more than read-only access. The read-only permission level in QuickBooks Online does not include access to the contact list reports, so confirm your permission level before you promise a client a delivery date.
Exporting customer addresses
Go to the Reports page and find the Customer Contact List. The default version of that report includes the full billing and shipping address, with each component prefixed by either "Billing" or "Shipping." If you plan to import the individual components rather than a single address string, you have to add those columns to the report manually.
Exporting vendor addresses
Go to the Reports page and find the Vendor Contact List. The default version of this report does not break out the address components at all. The full address sits in a single field called "Address," so you will need to add each component column to the report yourself.
Should you import addresses with the entity record or as a separate update?
Both methods work. The choice comes down to whether every entity in your file actually has an address.
| Method | Best when | Trade-off |
|---|---|---|
| Single file: entity and address together | Every entity on the file has a real address | NetSuite creates an address record for every row, including entities with no address |
| Two step: entities first, then an address update | Some entities have no address, or you want one address serving as both default billing and default shipping | More file preparation and a second import to manage |
Method 1: Import entity and address information in a single file
Importing the entity and the address in a single file works well for QuickBooks-to-NetSuite migrations because QuickBooks already puts all address information on a single line. Map the billing address fields under "Customer - Address 1" and the shipping address fields under "Customer - Address 2." Set Address 1 as the Default Billing address and Address 2 as the Default Shipping address.
The benefit of the single-file approach is its simplicity. There is very little file manipulation involved.
The downside is that NetSuite will create an address record for every entity in the file, including those with no address. NetSuite reads the row and assumes you want an address populated. The cleanest workaround is to split the import into two files: one for entities with addresses and one for those without.
Method 2: Import entity information first, then update with addresses
Under the two-step method, you create the customer or vendor record first, then attach the address with a separate "Update" CSV import, linking the address to the entity by internal or external ID.
The benefit of importing entities first is a cleaner address list. You avoid duplicate addresses, and you avoid address records that contain nothing but the required country field. The downside is that it is more work.
This is the approach I use when importing client addresses. Worth being straight about the reason it is manageable for me: I have a Python script that cleans and formats the files into the structure NetSuite expects. Without that, the extra file preparation is real.
When should you use NetSuite's address override?
Use the address override when you cannot cleanly extract the individual address components from the legacy system. The override lets you import a single text string and set that string as the entity's address, instead of mapping Street, City, State, and Zip separately.
Here is the situation where it saved a project. I had a client whose vendor addresses were populated only in the Street field. QuickBooks Online lets you populate an Address 1 and an Address 2 field, but the standard report will not give you access to Address 2 on its own. QuickBooks returns both lines crammed into a single Address 1 field, with a line break inside the Excel cell.
Rather than manually splitting each address into components, you can set NetSuite to read that single field as the entity address. Map the field holding the full address string to the Address field, set Override to True, and populate the Country field. Country is still required even when you use the override.
How do you clean address data before importing to NetSuite?
Three cleanup steps prevent most of the failures I see on address imports:
- Collapse duplicate addresses. For customers whose billing and shipping addresses are identical, import the address once and set it as both Default Billing and Default Shipping. This requires the two-step method: entities first, addresses second.
- Map country values to NetSuite's list. Country is required in NetSuite, and the value has to be valid. Search "Countries" in the global search bar to pull the list of accepted values, then use VLOOKUP or XLOOKUP to map each legacy country to a valid NetSuite country.
- Protect ZIP codes from truncation. ZIP codes lose leading zeros inside a CSV file. Format the ZIP column as "Special" and select "ZIP Code" before you save.
Frequently asked questions
Do you need a paid QuickBooks Online permission level to export addresses?
You need more than read-only. The read-only permission level in QuickBooks Online does not include access to the Customer Contact List or the Vendor Contact List, which are the two reports the address export depends on.
Why did my ZIP codes lose their leading zeros?
ZIP codes get truncated when a CSV file treats them as numbers. Format the ZIP column as "Special" and select "ZIP Code" before saving the file, and the leading zeros will hold.
Can you import a vendor address when the legacy data is a single unsplit string?
Yes. Use NetSuite's address override: map the single text string to the Address field and set Override to True. You still have to populate Country separately, because NetSuite requires a valid country value regardless of how the rest of the address is loaded.
Getting addresses right before the first transaction load
Address data looks like a low-stakes early task, which is exactly why it tends to get rushed. The entity records built in week one carry every transaction loaded after them, so a duplicated address list or a country value NetSuite rejects turns into rework at the worst point in the project. Picking the right import method up front, based on whether your entities actually have addresses, costs an hour and saves a cleanup cycle.
If you are earlier in the process, my step-by-step guide to migrating from QuickBooks Online to NetSuite covers the full sequence, and this list of accounting cleanup items to address before implementing NetSuite covers what to fix in QuickBooks first. For the errors that show up mid-import, see my guide to common NetSuite CSV import errors.
If you would rather hand the entity and historical data load to someone who has run it before, contact me for a free quote.
