Automatically Replace Multiple Characters in Your Shopify Data Export With EZ Exporter

When generating a CSV/Excel export of your Shopify data to be sent to third parties such as a supplier or another platform, the recipient may require the content of certain fields to be formatted in a certain way.

For example, some systems may require fields such as the shipping address to include only official abbreviations. There may also be cases where the system receiving the data only supports ASCII characters and if your customer base is global, you'll most likely have data outside of the ASCII standard.

The replace_multiple_characters() function in EZ Exporter can help with this. All you have to do is specify the field that contains characters you need to replace and a key/value mapping of what to replace and the new value to assign to it.

Let's look at a few examples.

Example 1: Replace the street address suffix with the official USPS abbreviations

Shopify Field: shipping_address.address1

Replacement Map: '{"Avenue": "AVE", "Street": "ST", "Drive": "DR"}'

If the value of shipping_address.address1 is: 555 W 57 Street

Using the formula:

replace_multiple_characters({{ shipping_address.address1 }}, '{"Avenue": "AVE", "Street": "ST", "Drive": "DR"}')

Will output: 555 W 57 ST

And if the value of shipping_address.address1 is: 999 Deadend Avenue

The output will be: 999 Deadend AVE

Example 2: Replace part of the SKU with alternative codes

You may be using a pattern like this for your SKUs: <ID>-<MATERIAL>-<NUMBER>

For the material, you normally enter the full name but one of your partners require this part to be in a 3-letter code they specify.

Shopify Field: line_items.sku

Replacement Map: '{"-COTTON-": "-CTN-", "-SILK-": "-SLK-", "-LEATHER-": "-LTH-"}'

If the value of line_items.sku is: OB1-LEATHER-005

Using the formula:

replace_multiple_characters({{ line_items.sku }}, '{"-COTTON-": "-CTN-", "-SILK-": "-SLK-", "-LEATHER-": "-LTH-"}')

Will output: OB1-LTH-005

And if the value of line_items.sku is: R2D-SILK-700

The output will be: R2D-SLK-700

Example 3: Clean up phone numbers

You might want to remove certain characters from your customer's phone number.

For example, some customers might use periods or spaces to break up the numbers but you'd prefer a dash instead. Some might also use the "+1" for the country code but you'd prefer it to be "011" instead.

Shopify Field: phone

Replacement Map: '{".": "-", " ": "", "+1": "011" }'

If the value of phone is: +1 555.772.8849

Using the formula:

replace_multiple_characters({{ phone }}, '{".": "-", " ": "-", "+1": "011"}')

Will output: 011-555-772-8849

Example 4: Remove CSV control characters such as newlines, tabs, and quotes

If a CSV file contains characters such as newlines/line breaks, tab characters, and quotes, some systems might have trouble parsing the data. Shopify fields such as the order "note" might contain these characters as the input comes from the customer directly.

Shopify Field: note

Replacement Map: '{"\n": " ", "\t": " ", "\\"": ""}'

If the value of note is:

Happy Birthday Baby Yoda!

Here's a green "lightsaber" for you.


Using the formula:

replace_multiple_characters({{ note }}, '{"\n": " ", "\t": " ", "\\"": ""}')

Will output: Happy Birthday Baby Yoda! Here's a green lightsaber for you. Love, Mando

As you can see, there's quite a lot you can do here to quickly and automatically make replacements on your data instead of having to do a manual search and replace in Excel.

If you have a lot of things to replace and need it done regularly, then this could save you a huge amount of time as you just need to set it up once in an EZ Exporter template and click the "Run" button (or schedule it to run automatically).

Tags: ez exporter, howto, csv, excel, shopify data export