<img src="https://ws.zoominfo.com/pixel/5BFMW73xT6Cu70sN1cUt" width="1" height="1" style="display: none;">

How to Remove Special Character in CSV File Export from NetSuite

Posted by Lincecum Lin, NetSuite Developer on May 11, 2021 4:18:06 PM
Lincecum Lin, NetSuite Developer

pexels-photo-357514-1

In NetSuite, user can put almost everything in “name field, and NetSuite does a pretty good job on helping exported CSV file with correct format.

 

 

 

If we put special character in the field:

 

When we export this field into CSV file, it will actually add double quote at beginning and end, also escape double quote in the field.

This will help you won’t get a messy csv file and can we read normally from Excel.

But unfortunately, if you’re going to provide the CSV file with special character it may not work for some company like: ACHD file. 

A ACHD file, should not contain any special character otherwise the file will not be accepted.

So how can we remove special character quickly and easily?

In the Saved Search column subtab, add another column:

On formula column click following icon and will pop up a formula window:

in Formula, put following code.

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE({name},'"',''),':',''),'''',''),'|',''),'*',''),'&',''),'^',''),'%',''),'$',''),'#',''),'@',''),';',''),'{',''),'}',''),'[',''),']',''),'!',''),'(',''),')',''),'-',''),'=',''),'>',''),'<',''),',',''),'.',''),'\',''),'/','')

The above script can remove ", ', :, |, *, &, ^, %, $, #, @, ;, {, }, (, ), [, ], !, -, =, >, <, ,, ., \, / 

Dont get nervous with such long script, it actually has only one feature REPLACE , REPLACE function will replaces all occurrences of a substring within a string, with a new substring

REPLACE(field_name, old_string, new_string)

So for the innermost REPLACE({name}, '"', '') 

 {name} represent the field name, normaily field internal id warp by curly brackets, and " is the old string will going to replace to '' which means nothing or empty.

After that, the output of this REPLACE feature, will become the input of next REPLACE feature, until the outermost REPLACE feature.

If you’re familiar with regular expression, following script is much more easier for you.

REGEXP_REPLACE({name}, '[^0-9A-Za-z ]', '')

This will replace everything NOT 0~9, A~Z, a-z, (space) into empty.

Looking for more information? Hope this article will help! 

Ref: 

https://www.w3schools.com/sql/func_sqlserver_replace.asp

Wanted to discuss more about your company's NetSuite needs? Contact Concentrus today!

Contact Us

About Us

 

Concentrus is a complete NetSuite solutions provider that guides organizations through how to use NetSuite to reach highly focused business goals and objectives. We provide NetSuite implementation, developer, integration, and customization services to ensure that you have a long-term solution that is tailored to fit your systems, people, and processes.

 

Read About Our Implementation Methodology

 

Want more NetSuite Tips and Tricks? Check out our Short & 'Suite videos

 

 

 

Tags: NetSuite Partner, NetSuite How To's, NetSuite Tips