Discussions

Hi everyone,

Can any of you share with me how you navigated migrating your realm to UTF-8?

We've only had QB for 2 years, but apparently, UTF-8 wasn't enabled when we started.  Over the past year, I'm watching errors pile up because a lot of our data is in written form (vs. hard numbers).

For example, we'll have survey data and even the questions being asked contain apostrophe's and parentheses.  QB isn't handling them well, and all of these characters are generating errors.

To correct this, QB Tech Support had me install and run an encoding scanner to identify the errors in our realm, and it looks to be numbering in the thousands; nearly every record and a majority of our fields are flagged as problematic.

Some of the issues I see are that the encoding scanner doesn't tell me "what's" wrong with our data, just that this record or that field is an issue.

QB Tech Support said that to turn UTF-8 on will affect the entire realm AND I need to have all the errors fixed before they do this.

Our staff is inputting data daily; so the number of errors grows daily.

We cannot afford to spend thousands on an external company to help us scrub the special characters.  I was debating telling our staff to pause for a long weekend, so I can download the data to excel and do a find and replace function and then re-upload the data...but I'm just worried about breaking or losing data that we need.

Can any of you recommend a better way?  What did you do to manage this?

Thank you!!!

Jen

Hi Jennifer,

We are currently on a similar journey. When UTF-8 is enabled and you are using another 3rd party cloud service, like Workato, any non-acsii characters sent to the third party service will cause errors, this is the reason to clear the non-ascii characters. Here is what we are doing to repair the data. 

I made a rich text formula field and entered the formula in the link below. The link below describes the formula and also the reference ascii Website where this information can be found. I have since removed several of the ascii characters in that formula that do not throw the error but this will get you started.

What the formula does is display all the characters that will cause an error. You then can build a report and use one of three tools to clean up the data namely: Grid Edit, Find/Replace or (Export-correct-Import). You can test by turning UTF-8 on one App at a time and looking for the black diamond with the question mark. This process has to be done for each table.

Of course if you're not using third party cloud services UTF-8 can be enabled anytime and then the data can be cleaned up when it is discovered but you won't know is hiding behind the black diamond question mark thingy. 

Finding non- windows-1252 Characters in Text fields using a formula

https://community.quickbase.com/discussion/finding-non-windows-1252-characters-in-text-fields-using-a-formula#bm7085aeaa-a2e5-46e1-98f5-0185cb476521

Good luck.

Hi everyone,

Can any of you share with me how you navigated migrating your realm to UTF-8?

We've only had QB for 2 years, but apparently, UTF-8 wasn't enabled when we started.  Over the past year, I'm watching errors pile up because a lot of our data is in written form (vs. hard numbers).

For example, we'll have survey data and even the questions being asked contain apostrophe's and parentheses.  QB isn't handling them well, and all of these characters are generating errors.

To correct this, QB Tech Support had me install and run an encoding scanner to identify the errors in our realm, and it looks to be numbering in the thousands; nearly every record and a majority of our fields are flagged as problematic.

Some of the issues I see are that the encoding scanner doesn't tell me "what's" wrong with our data, just that this record or that field is an issue.

QB Tech Support said that to turn UTF-8 on will affect the entire realm AND I need to have all the errors fixed before they do this.

Our staff is inputting data daily; so the number of errors grows daily.

We cannot afford to spend thousands on an external company to help us scrub the special characters.  I was debating telling our staff to pause for a long weekend, so I can download the data to excel and do a find and replace function and then re-upload the data...but I'm just worried about breaking or losing data that we need.

Can any of you recommend a better way?  What did you do to manage this?

Thank you!!!

Jen

Hi Jim,

Thank you so much for replying!  I think your reply was buried in my inbox with all of the other QB discussion threads, and I apologize for the delay in responding to you.

I wasn't aware I can implement UTF-8 on an app-by-app basis; QB Tech Support told me they had to do it at the realm level - which made the project seem far more daunting (because then everything has to be perfect with no new imperfect data coming in).

While I'm not using 3rd party apps that are directly importing to QB; we do use forms on our website to collect data, that I am building into Sync Files in Google Drive and syncing that data weekly.  

Once I have enabled UTF-8, when importing data that contains non-ascii characters such as quotation marks, apostrophe's, or parentheses (these seem to be the biggest challenges in our data), will they continue to cause issues (in your experience and opinion)?

Thank you again! This gives me the ability to develop a clear plan of action on how to tackle what otherwise felt like an overwhelming and impossible task.

Take care,

Jen

Hi Jennifer,

We are currently on a similar journey. When UTF-8 is enabled and you are using another 3rd party cloud service, like Workato, any non-acsii characters sent to the third party service will cause errors, this is the reason to clear the non-ascii characters. Here is what we are doing to repair the data. 

I made a rich text formula field and entered the formula in the link below. The link below describes the formula and also the reference ascii Website where this information can be found. I have since removed several of the ascii characters in that formula that do not throw the error but this will get you started.

What the formula does is display all the characters that will cause an error. You then can build a report and use one of three tools to clean up the data namely: Grid Edit, Find/Replace or (Export-correct-Import). You can test by turning UTF-8 on one App at a time and looking for the black diamond with the question mark. This process has to be done for each table.

Of course if you're not using third party cloud services UTF-8 can be enabled anytime and then the data can be cleaned up when it is discovered but you won't know is hiding behind the black diamond question mark thingy. 

Finding non- windows-1252 Characters in Text fields using a formula

https://community.quickbase.com/discussion/finding-non-windows-1252-characters-in-text-fields-using-a-formula#bm7085aeaa-a2e5-46e1-98f5-0185cb476521

Good luck.

Hi everyone,

Can any of you share with me how you navigated migrating your realm to UTF-8?

We've only had QB for 2 years, but apparently, UTF-8 wasn't enabled when we started.  Over the past year, I'm watching errors pile up because a lot of our data is in written form (vs. hard numbers).

For example, we'll have survey data and even the questions being asked contain apostrophe's and parentheses.  QB isn't handling them well, and all of these characters are generating errors.

To correct this, QB Tech Support had me install and run an encoding scanner to identify the errors in our realm, and it looks to be numbering in the thousands; nearly every record and a majority of our fields are flagged as problematic.

Some of the issues I see are that the encoding scanner doesn't tell me "what's" wrong with our data, just that this record or that field is an issue.

QB Tech Support said that to turn UTF-8 on will affect the entire realm AND I need to have all the errors fixed before they do this.

Our staff is inputting data daily; so the number of errors grows daily.

We cannot afford to spend thousands on an external company to help us scrub the special characters.  I was debating telling our staff to pause for a long weekend, so I can download the data to excel and do a find and replace function and then re-upload the data...but I'm just worried about breaking or losing data that we need.

Can any of you recommend a better way?  What did you do to manage this?

Thank you!!!

Jen

Hi Jennifer,

Enable UTF-8 per App by going to the App properties=>Advanced Settings and check the UTF-8 checkbox.

Once UTF-8 is enabled all "newly entered" non-ascii characters display normally. The problem is dealing with the "old" non-ascii characters currently in the text fields of your Apps. All those characters need to be replaced or removed. 

Hi Jim,

Thank you so much for replying!  I think your reply was buried in my inbox with all of the other QB discussion threads, and I apologize for the delay in responding to you.

I wasn't aware I can implement UTF-8 on an app-by-app basis; QB Tech Support told me they had to do it at the realm level - which made the project seem far more daunting (because then everything has to be perfect with no new imperfect data coming in).

While I'm not using 3rd party apps that are directly importing to QB; we do use forms on our website to collect data, that I am building into Sync Files in Google Drive and syncing that data weekly.  

Once I have enabled UTF-8, when importing data that contains non-ascii characters such as quotation marks, apostrophe's, or parentheses (these seem to be the biggest challenges in our data), will they continue to cause issues (in your experience and opinion)?

Thank you again! This gives me the ability to develop a clear plan of action on how to tackle what otherwise felt like an overwhelming and impossible task.

Take care,

Jen

Hi Jennifer,

We are currently on a similar journey. When UTF-8 is enabled and you are using another 3rd party cloud service, like Workato, any non-acsii characters sent to the third party service will cause errors, this is the reason to clear the non-ascii characters. Here is what we are doing to repair the data. 

I made a rich text formula field and entered the formula in the link below. The link below describes the formula and also the reference ascii Website where this information can be found. I have since removed several of the ascii characters in that formula that do not throw the error but this will get you started.

What the formula does is display all the characters that will cause an error. You then can build a report and use one of three tools to clean up the data namely: Grid Edit, Find/Replace or (Export-correct-Import). You can test by turning UTF-8 on one App at a time and looking for the black diamond with the question mark. This process has to be done for each table.

Of course if you're not using third party cloud services UTF-8 can be enabled anytime and then the data can be cleaned up when it is discovered but you won't know is hiding behind the black diamond question mark thingy. 

Finding non- windows-1252 Characters in Text fields using a formula

https://community.quickbase.com/discussion/finding-non-windows-1252-characters-in-text-fields-using-a-formula#bm7085aeaa-a2e5-46e1-98f5-0185cb476521

Good luck.

Hi everyone,

Can any of you share with me how you navigated migrating your realm to UTF-8?

We've only had QB for 2 years, but apparently, UTF-8 wasn't enabled when we started.  Over the past year, I'm watching errors pile up because a lot of our data is in written form (vs. hard numbers).

For example, we'll have survey data and even the questions being asked contain apostrophe's and parentheses.  QB isn't handling them well, and all of these characters are generating errors.

To correct this, QB Tech Support had me install and run an encoding scanner to identify the errors in our realm, and it looks to be numbering in the thousands; nearly every record and a majority of our fields are flagged as problematic.

Some of the issues I see are that the encoding scanner doesn't tell me "what's" wrong with our data, just that this record or that field is an issue.

QB Tech Support said that to turn UTF-8 on will affect the entire realm AND I need to have all the errors fixed before they do this.

Our staff is inputting data daily; so the number of errors grows daily.

We cannot afford to spend thousands on an external company to help us scrub the special characters.  I was debating telling our staff to pause for a long weekend, so I can download the data to excel and do a find and replace function and then re-upload the data...but I'm just worried about breaking or losing data that we need.

Can any of you recommend a better way?  What did you do to manage this?

Thank you!!!

Jen

Hi everyone,

Can any of you share with me how you navigated migrating your realm to UTF-8?

We've only had QB for 2 years, but apparently, UTF-8 wasn't enabled when we started.  Over the past year, I'm watching errors pile up because a lot of our data is in written form (vs. hard numbers).

For example, we'll have survey data and even the questions being asked contain apostrophe's and parentheses.  QB isn't handling them well, and all of these characters are generating errors.

To correct this, QB Tech Support had me install and run an encoding scanner to identify the errors in our realm, and it looks to be numbering in the thousands; nearly every record and a majority of our fields are flagged as problematic.

Some of the issues I see are that the encoding scanner doesn't tell me "what's" wrong with our data, just that this record or that field is an issue.

QB Tech Support said that to turn UTF-8 on will affect the entire realm AND I need to have all the errors fixed before they do this.

Our staff is inputting data daily; so the number of errors grows daily.

We cannot afford to spend thousands on an external company to help us scrub the special characters.  I was debating telling our staff to pause for a long weekend, so I can download the data to excel and do a find and replace function and then re-upload the data...but I'm just worried about breaking or losing data that we need.

Can any of you recommend a better way?  What did you do to manage this?

Thank you!!!

Jen

Just thinking out loud, but if I were to approach this, I would probably use the API to loop through all my apps, and within each app, loop through all my tables, and just convert the string fields of each table to utf-8 on the fly before inserting back into the table. If you have someone on your team that's familiar with python or could hire someone for cheap, they could probably whip something up in an hour or two.  Depends on how many tables you have and many records are in each, but the runtime for this could potentially be pretty quick.

Original Message:
Sent: 03-01-2023 11:50
From: Jennifer Juhasz
Subject: Transitioning to UTF-8

Hi everyone,

Can any of you share with me how you navigated migrating your realm to UTF-8?

We've only had QB for 2 years, but apparently, UTF-8 wasn't enabled when we started.  Over the past year, I'm watching errors pile up because a lot of our data is in written form (vs. hard numbers).

For example, we'll have survey data and even the questions being asked contain apostrophe's and parentheses.  QB isn't handling them well, and all of these characters are generating errors.

To correct this, QB Tech Support had me install and run an encoding scanner to identify the errors in our realm, and it looks to be numbering in the thousands; nearly every record and a majority of our fields are flagged as problematic.

Some of the issues I see are that the encoding scanner doesn't tell me "what's" wrong with our data, just that this record or that field is an issue.

QB Tech Support said that to turn UTF-8 on will affect the entire realm AND I need to have all the errors fixed before they do this.

Our staff is inputting data daily; so the number of errors grows daily.

We cannot afford to spend thousands on an external company to help us scrub the special characters.  I was debating telling our staff to pause for a long weekend, so I can download the data to excel and do a find and replace function and then re-upload the data...but I'm just worried about breaking or losing data that we need.

Can any of you recommend a better way?  What did you do to manage this?

Thank you!!!

Jen

------------------------------
Jennifer Juhasz
FamilySmart
BC, Canada
------------------------------

Hi everyone,

Can any of you share with me how you navigated migrating your realm to UTF-8?

We've only had QB for 2 years, but apparently, UTF-8 wasn't enabled when we started.  Over the past year, I'm watching errors pile up because a lot of our data is in written form (vs. hard numbers).

For example, we'll have survey data and even the questions being asked contain apostrophe's and parentheses.  QB isn't handling them well, and all of these characters are generating errors.

To correct this, QB Tech Support had me install and run an encoding scanner to identify the errors in our realm, and it looks to be numbering in the thousands; nearly every record and a majority of our fields are flagged as problematic.

Some of the issues I see are that the encoding scanner doesn't tell me "what's" wrong with our data, just that this record or that field is an issue.

QB Tech Support said that to turn UTF-8 on will affect the entire realm AND I need to have all the errors fixed before they do this.

Our staff is inputting data daily; so the number of errors grows daily.

We cannot afford to spend thousands on an external company to help us scrub the special characters.  I was debating telling our staff to pause for a long weekend, so I can download the data to excel and do a find and replace function and then re-upload the data...but I'm just worried about breaking or losing data that we need.

Can any of you recommend a better way?  What did you do to manage this?

Thank you!!!

Jen