How to remove special character in CSV file export from NetSuite

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

If we put special character in the field

Field with special character

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.

Exported CSV file from NetSuite

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

Direct open CSV file using Excel

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

In ACHD file, you should not contain any special character otherwise the file will not accepted.

So how can we remove special character quick and easy?

In the Saved Search column subtab, add another column

Formula(Text) column

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

open formula pop up window

Formula pop up window

in Formula field, 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

“, ‘, :, |, *, &, ^, %, $, #, @, ;, {, }, (, ), [, ], !, -, =, >, <, ,, ., \, /`

Don’t get nervous with such long script, it actually have 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.

Result — after remove special character

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

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

Using regexp_replace

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

Hope this article will help!

Ref:s

SQL Server REPLACE() Function