Page 1 of 1

Imported Characters

Posted: Wed Oct 04, 2017 1:31 am
by keepitfunky
Hello,

I am regularly importing lists into Calc, that contain one of the following "corrupted" characters. In the below list, the "¿" and "?" are both meant to be the "degree" symbol. Is there anything at my disposal that would allow me to automatically translate those characters into "º" or "-deg."? I never deal with question marks in Calc so I don't fear running the risk messing up something else.

Thanks for the help!

BRANCH (45¿), DN50
BRANCH (87¿), DN50
BEND (87¿), DN50
PIPE, ONE SOCKET, STLS DN 50
PIPE, ONE SOCKET, DN 50 1000MM
SEALING ELEMENT, DN50
BRANCH, REDUCING (45¿), DN1=100, DN2=50
BRANCH, REDUCING (87?), DN100
BEND STLS 90¿ DN50
BRANCH, REDUCING (87?), DN70/50

Re: Imported Characters

Posted: Wed Oct 04, 2017 3:38 am
by RusselB
This sounds to me like a mistake in the import language chosen when you import the file.
It may also have to do with your current locale settings, which are controlled by your OS (Operating System)
Can you attach a copy of one of the files that you are actually importing, so that we can test for ourselves? See How to Attach a Document for details.

Re: Imported Characters

Posted: Wed Oct 04, 2017 5:44 am
by acknak
It's almost certainly a mismatched character set, but I can't tell what you have from the garbled sample.

Can you copy a few lines to a new file and attach that here? The forum will accept zip files if you rename from .zip to .odt.

Re: Imported Characters

Posted: Wed Oct 04, 2017 4:59 pm
by keepitfunky
Thanks for the offer of support! I've attached the offending file, but I had to edit it to remove some more sensitive data. Is there any risk of that edit/save affecting what you're looking for?

Re: Imported Characters

Posted: Wed Oct 04, 2017 5:27 pm
by acknak
Sorry, I wasn't clear. We need to have a sample of the original data before it was imported into Calc. The import process has changed the text and I'm not sure what to do with it. The long-term solution will be to find the appropriate import settings, not to modify the imported data—although you could do that, just Find/Replace the corrupted result with the correct character.

Re: Imported Characters

Posted: Wed Oct 04, 2017 8:42 pm
by keepitfunky
Perhaps I used the term "import" a little more casually than you're interpreting. What I shared above is as close to the source data as I can get--it gets exported from another software (SmartSupplier) as an excel file, which I'm then opening in LibreOffice. I'm also contacting the support from that software developer, but wondering if there's some way to have all the "corrupted" characters replaced when I open the file, without manually doing a "Find and Replace". Is that possible?

Re: Imported Characters

Posted: Wed Oct 04, 2017 9:04 pm
by RoryOF
If you can upload an excel file that will be of assistance.

Re: Imported Characters

Posted: Wed Oct 04, 2017 9:05 pm
by Zizi64
What I shared above is as close to the source data as I can get--it gets exported from another software (SmartSupplier) as an excel file,
I think the error is in the exporting procedure. Perhaps it is related to some character code table incompatibility. You can correct it manually in the AOO/LO by usage of the Find and replace, or you can automatize the job by a macro.

Re: Imported Characters

Posted: Thu Oct 05, 2017 8:47 am
by keepitfunky
Thank you for the suggestion. I am unfamiliar with Macros. How would that work in this instance?

Re: Imported Characters

Posted: Thu Oct 05, 2017 10:26 am
by RoryOF
A sample of the excel file might allow us solve the problem without recourse to macros. In OpenOffice macros have a very steep learning curve.

Re: Imported Characters

Posted: Thu Oct 05, 2017 2:25 pm
by Zizi64
In OpenOffice macros have a very steep learning curve.
Yes it is true generally, but in this specific case you can operate with a simply recorded macro too:

Code: Select all

sub ChangeToDegree
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(17) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 0
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.Backward"
args1(4).Value = false
args1(5).Name = "SearchItem.Pattern"
args1(5).Value = false
args1(6).Name = "SearchItem.Content"
args1(6).Value = false
args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false
args1(8).Name = "SearchItem.AlgorithmType"
args1(8).Value = 0
args1(9).Name = "SearchItem.SearchFlags"
args1(9).Value = 65536
args1(10).Name = "SearchItem.SearchString"
args1(10).Value = "¿"
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = "°"
args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255
args1(13).Name = "SearchItem.ChangedChars"
args1(13).Value = 2
args1(14).Name = "SearchItem.DeletedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.InsertedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1280
args1(17).Name = "SearchItem.Command"
args1(17).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Visible"
args2(0).Value = false

dispatcher.executeDispatch(document, ".uno:SearchResultsDialog", "", 0, args2())

end sub
And you can modify the macro for searching the

Code: Select all

?)
characters, if you needed, just make a copy of the source of the macro, and modify the 'search' string.
You can type-in the ° character by pressing ALT-248 (Hold down the ALT key and press 248 on the numeric part of the keyboard)

Re: Imported Characters

Posted: Thu Oct 05, 2017 4:28 pm
by DiGro
This macro will replace ¿ and ? for you.

You can expand the array aFind with other characters if you want by adding a comma and copying them and placing them between quotes.
Don't forget then to add an extra item to aReplace though :-)

The three lines:
FandR.SearchCaseSensitive = false
FandR.SearchWords = false ' 1 to A but not 11 to AA
FandR.SearchRegularExpression = false
don't have a function here, so they could be removed if you want.

Code: Select all

Sub CalcFindAndReplace()
Dim oDoc,aFind,aReplace,aRayCount,FandR,oSheet
oDoc = ThisComponent
aFind = Array("¿","?")
aReplace = Array("°","°")  ' ° = ALT + 248
aRayCount = 0
oSheet = oDoc.CurrentSelection.Spreadsheet
FandR = oSheet.createReplaceDescriptor
FandR.SearchCaseSensitive = false
FandR.SearchWords = false ' 1 to A but not 11 to AA
FandR.SearchRegularExpression = false
While aRayCount <= uBound(aFind)
FandR.setSearchString(aFind(aRayCount))
FandR.setReplaceString(aReplace(aRayCount))
aRayCount = aRayCount + 1
oSheet.ReplaceAll(FandR)
Wend
End Sub