> >
CSV formatting for NetSuite||||||||||||||||||

Formatting CSV Files for Import Into NetSuite

Veuillez noter que ce billet est présentement disponible uniquement en anglais

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 make have shaked 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

Save as text file

2. Open the file with Notepad++

3. Remove the cover page information as that information is not needed

Open in notepad

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

  1. Company Name
  2. Address
  3. Phone
  4. City
  5. Fax
  6. Email
  7. Comment
  • n stands for new line
  • We will add our delimiter at every new line

Number of Fields

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

Remove return/enter key entries

7.  Break lines

  • Most entries end after the CR. This will be our break point 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.

Break lines

8. Remove headers & footers

  • Check bookmark line
  • Find lines to be deleted
  • Navigate to Search > Bookmark > Remove Bookmarked Lines

Remove headers and footers

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)

Open text file in excel

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.

result

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

pres9

13. Remove unused data

Use Find All/Replace all to remove unused data such as “Telephone:”, “Email:”, etc.

Remove unused info

14. Save file as a CSV

 

And, voila! the end result  – a beautiful CSV file ready for import into NetSuite!

CSV file to import into NetSuite

 

 

 

Stephane To
Stephane prides himself in his rockstar java and Windows server skills. Joining Big Bang ERP has given him the opportunity to grow his programming knowledge as well as extend his business acumen. As a Consultant, Stephane is ready to tackle any problem thrown his way.