I am sure lots of us have been there, we have a client or coworker who wants to import non-formatted data into Netsuite. This data can come from various sources and files such as .pdf, websites (Html) and others. Until now, you may have shaken your head in dismay, not wanting to deal with the complexity of importing the data. But, have no fear, I’ve outlined some pretty simple steps to format the data to make importing the CSV file into NetSuite a breeze!
I used Notepad++ and Microsoft Excel to complete the task
Here we go…
1. Save the source file as a text (.txt) file
2. Open the file with Notepad++
3. Remove the cover page information as that information is not needed
4. Choose a CSV delimiter (Make sure your character is not present anywhere in the document and choose a character that is easy to see, example: *, %, @, &)
5. Set the Number of Fields
- Company Name
- n stands for a new line
- We will add our delimiter at every new line
6. Remove “Return/Enter” key entries
- r stands for the Enter key on the keyboard
- Replace all the r by “blank” so that we can decide when to break in the next step
7. Break lines
- Most entries end after the CR. This will be our breakpoint in this case.
- The result is the same as if you find all the CR in the file and press Enter on your keyboard manually.
8. Remove headers & footers
- Check bookmark line
- Find lines to be deleted
- Navigate to Search > Bookmark > Remove Bookmarked Lines
9. Save text file
10. Open text file in Excel
- Data > From Text > Delimited
- Choose “other” for delimiter and input your character (* in this demo)
11. View the result
Notice some columns are off due to the Company name taking 2 lines in the pdf. This needs to be fixed.
12. Separate Zip code & City
- Netsuite address fields are separated into different fields for address, city, zip code. We can use excel formulas to quickly separate them.
- You can simply drag the right corner to replicate the same formula for all the entries
13. Remove unused data
Use Find All/Replace all to remove unused data such as “Telephone:”, “Email:”, etc.
14. Save file as a CSV
And, voila! the end result – a beautiful CSV file ready for import into NetSuite!