Formatting CSV Files for Import Into NetSuite

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

Share this post

Share on facebook
Share on linkedin
Share on twitter
Share on email

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

pres 2

2. Open the file with Notepad++

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

pres 3

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 a new line
  • We will add our delimiter at every new line

 

pres step1

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

 

pres6 step2

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

pres step4

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)

pres 7

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.

pres 8

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

pres 9

13. Remove unused data

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

step 10

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

 

 

 

Big Bang 360

Reach us

Discover the Big Bang unbiased consultative approach with expertly trained and well-versed cloud specialists.

map-world
FaviCon-breadcrumb
FaviCon-breadcrumb
FaviCon-breadcrumb

How does your organization stack up?

Take the Big Bang 360° Business Assessment to uncover opportunities concerning your people, processes, or technology.

Formatting CSV Files for Import Into NetSuite

Complete the form to download your workbook today.

Become the master of your organization’s business systems – without a headache.

Formatting CSV Files for Import Into NetSuite

Complete the form to download your workbook today.

Become the master of your organization’s business systems – without a headache.

Why are you contacting us today?

A Big Bang representative will contact you within 24-hours.

Strategy and planning

Cloud solutions and consulting

Staffing and training

Grow my career

Not sure? Get in touch with an expert

What are you most interested in?

A Big Bang representative will contact you within 24-hours.

Applying for a job or internship

Exploring the life of a consultant

Not sure? Get in touch with an expert

Contact us

A Big Bang representative will contact you within 24-hours.

CERTIFICATIONS