Imported Characters

Discuss the spreadsheet application

Imported Characters

Postby keepitfunky » Wed Oct 04, 2017 1:31 am

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
keepitfunky
 
Posts: 4
Joined: Wed Oct 04, 2017 1:20 am

Re: Imported Characters

Postby RusselB » Wed Oct 04, 2017 3:38 am

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.3 and LibreOffice 5.1.3.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4041
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Imported Characters

Postby acknak » Wed Oct 04, 2017 5:44 am

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
User avatar
acknak
Moderator
 
Posts: 22607
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Imported Characters

Postby keepitfunky » Wed Oct 04, 2017 4:59 pm

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 10 times
LibreOffice 5.3.6.1
keepitfunky
 
Posts: 4
Joined: Wed Oct 04, 2017 1:20 am

Re: Imported Characters

Postby acknak » Wed Oct 04, 2017 5:27 pm

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
User avatar
acknak
Moderator
 
Posts: 22607
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Imported Characters

Postby keepitfunky » Wed Oct 04, 2017 8:42 pm

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
keepitfunky
 
Posts: 4
Joined: Wed Oct 04, 2017 1:20 am

Re: Imported Characters

Postby RoryOF » Wed Oct 04, 2017 9:04 pm

If you can upload an excel file that will be of assistance.
Apache OpenOffice 4.1.3 on Xubuntu 16.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 25054
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Imported Characters

Postby Zizi64 » Wed Oct 04, 2017 9:05 pm

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; LO4.4.7, LO5.3.6 on Win7x64Prof.
And the portable versions: LO3.3.0-LO5.4.1 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5892
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Imported Characters

Postby keepitfunky » Thu Oct 05, 2017 8:47 am

Thank you for the suggestion. I am unfamiliar with Macros. How would that work in this instance?
LibreOffice 5.3.6.1
keepitfunky
 
Posts: 4
Joined: Wed Oct 04, 2017 1:20 am

Re: Imported Characters

Postby RoryOF » Thu Oct 05, 2017 10:26 am

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.3 on Xubuntu 16.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 25054
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Imported Characters

Postby Zizi64 » Thu Oct 05, 2017 2:25 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
?)

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; LO4.4.7, LO5.3.6 on Win7x64Prof.
And the portable versions: LO3.3.0-LO5.4.1 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5892
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Imported Characters

Postby DiGro » Thu Oct 05, 2017 4:28 pm

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   Expand viewCollapse view
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.3 on Windows 10. Scanned with ZIGGO Extended security (F-Secure)
User avatar
DiGro
 
Posts: 51
Joined: Mon Oct 08, 2007 1:31 am
Location: Hoorn NH, The Netherlands


Return to Calc

Who is online

Users browsing this forum: DynV and 53 guests