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!
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
