Imported Characters

Discuss the spreadsheet application
Post Reply
keepitfunky
Posts: 4
Joined: Wed Oct 04, 2017 1:20 am

Imported Characters

Post 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
LibreOffice 5.3.6.1
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Imported Characters

Post 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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Imported Characters

Post 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.
AOO4/LO5 • Linux • Fedora 23
keepitfunky
Posts: 4
Joined: Wed Oct 04, 2017 1:20 am

Re: Imported Characters

Post 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?
Attachments
LibreOffice-degree.xls
(6.5 KiB) Downloaded 94 times
LibreOffice 5.3.6.1
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Imported Characters

Post 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.
AOO4/LO5 • Linux • Fedora 23
keepitfunky
Posts: 4
Joined: Wed Oct 04, 2017 1:20 am

Re: Imported Characters

Post 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?
LibreOffice 5.3.6.1
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Imported Characters

Post by RoryOF »

If you can upload an excel file that will be of assistance.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Imported Characters

Post 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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
keepitfunky
Posts: 4
Joined: Wed Oct 04, 2017 1:20 am

Re: Imported Characters

Post by keepitfunky »

Thank you for the suggestion. I am unfamiliar with Macros. How would that work in this instance?
LibreOffice 5.3.6.1
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Imported Characters

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Imported Characters

Post 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)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
DiGro
Posts: 173
Joined: Mon Oct 08, 2007 1:31 am
Location: Hoorn NH, The Netherlands

Re: Imported Characters

Post 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
____________
DiGro

AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
Post Reply