I'm attempting to automate an SMS Mobile number import into marketing cloud from an external database.
We have a CSV file that has the columns ContactID, Locale (null), MobileNumber. The export from our DB does not contain locale, so we add it to the CSV manually. From here we import directly into MobileConnect and add these contacts with US locales. The problem around automating arises when we have individuals who live in Canada, and their locale is CA. Marketing cloud sends us an Import completion email with records that failed to import due to incorrect locale. We take the errored records, change the locale to CA, and import the Canadian records into MobileConnect.
We're struggling to automate the part where we reprocess failed records because of having a CA locale and not US. Is there a way to use SSJS or SQL to determine if a Mobile number is Canadian or US solely based on the Fixed-length bell system format? (223)-456-7890 Or is there another method to determine?