Deleting trailing "spaces" that aren't spaces

#1

Question: How can I remove “spaces” after a string that are caused by certain key combinations?

Background:
(1) I am ingesting a “Name” field from an outside data source.
(2) When you look at the “Name” data - it looks like (for example) "John “. I am looking at the Name data (a) from the raw data set (b) After ingesting it into Mitto and © Querying it from our data warehouse once ingested
(3) However, when I do a LENGTH function on it, it returns a longer length. For example, a LENGTH of John should result in 4, but I am getting a result of 6.
(4) Even if I apply a TRIM function to the name field (e.g. trim(name)) - I am STILL getting a returned LENGTH of 6. E.g. length(trim(name)) = 6
(5) When I send this field to an FTP site, the FTP site is ingesting this field as “JOHN A”. The A has an accent hat on it.
(6) FYI1 - According to Google, the A with an accent hat is called a circumflex accent – How to Insert Symbols Above Letters With the Keyboard | Small Business - Chron.com
(7) FYI2 - You can see an A with an accent hat (in Word for example) using a combination of keys - – specifically Pressing “Ctrl-Shift” and the caret (” ^ ") key and then the letter to insert a circumflex accent
(8) I don’t have the ability to change the actual raw data, so I need to CLEAN the data by removing what looks like a SPACE after the name, but when ingested on an FTP site is actually a circumflex accented A.

#2

Hey @BlueFlamingo can you provide a simple csv file that has a few examples of these strings that end up with extra spaces?

Something like:

id,example_string
1,John Å
2,Carl Ø
#3

This is probably just a file encoding issue.
Are you using the correct file encoding (e.g. UTF-8, etc) when loading the input file? If so, does the data look correct in the output database?
Are you using the correct file encoding (e.g. UTF-8, etc) when creating the output file from the database?

#4

Hi Justin -
(1) The input file is coming from a partner system. I only have a choice to select XLS - and there isn’t a UTF-8 encoding option. Is there a job that I could write that would transform the encoding to UTF8?

(2) How would I go about creating an output file with UTF-8 encoding? I currently have two jobs involved in this sequence. (a) An IO job that uses SQL to transform a file to our Mitto box. (b) A CMD job that runs a Bash script to rename the file on our Mitto box and send it to an FTP site. Neither of these jobs have an obvious “encoding” line I could set to UTF-8…

#5

Hey @BlueFlamingo, I checked and Mitto doesn’t currently support specifying the file encoding of an Excel file input or the file encoding of any file output.
I’ve created two improvement requests for Mitto v3.0 to handle these use cases.
In the meantime, please create a support ticket so we can look at this specific Excel file and see what our current options are. Thank you!

#6

Thanks Justin.

(1) I have just submitted a support ticket.

(2) Do you have an ETA of v3.0?