2

I'm moving user/customer details from my mother's old web store. Only problem is that exported .csv-file has name details in the same column like this:

[        col A     ]
lastname, firstname
lastname, firstname
lastname, firstname

I would like to move them into separate columns like this, I just don't know how :P :

[ col A ] [ col B]
lastname   firstname
lastname   firstname
lastname   firstname

Also, how to convert symbol � to what it should be (ÄÖ in this case)?

using LibreOffice on Mac

Nifle
  • 34,998
Anonymous_1112137281
  • 21

1 Answers1

1

Problem One - Splitting the names

I've used two methods, here's the easiest to do, tho' it's not efficient. Also, I'll note that Macs can run the Mac specific distro, and pre-M1, at least, can also run the unix x11 version. Command shorcuts differ between them.

  1. open a blank text document. (do not close it, leave it in the background)
  2. open the spreadsheet
  3. select the column of data to be split
  4. insert two columns after the data
  5. switch to the text window (do NOT close either window)
  6. paste the data into the text window. it should show up as a single column table; if it shows as text skip to 9
  7. select the table
  8. from the menu, Table→Convert→Table to Text.
  9. select all to select the resulting text
  10. from the menu: Table→Convert→text to table (don't click ok yet)
    1. click the radiobutton for "other"
    2. copy the delimiter (yours indicates a comma) into the textbox next to the Other radio button
    3. click ok. This should result in a two column table.
  11. The data has an issue
    1. select the right column (by any of several choices)
    2. select Edit→Find and Replace Text.
    3. In the top box, put a space
    4. click the box for inside selection
    5. leave the second field blank (we don't want anything in place of the space).
    6. click the replace-all button.
  12. click into the table in a cell with content
  13. select all twice, so as to select only the table, and copy it
    1. once selects the cell's content,
    2. the second the table,
    3. Don't do a third as it grabs the context the table is in, which is more than we want.
  14. switch back to the spreadsheet
  15. select the first cell of the first blank column you created earlier
  16. paste. this should fill the two columns.

I've done this a lot; it works well, and doesn't lose bold/italic/underline

Problem 2: improperly displaying characters

in short form:

  1. select the annoying character. Copy just in case
  2. menu: edit→find and replace
  3. the upper field, Find, should have the annoying character
    1. if it doesn't, paste it in.
  4. type the correct character in the second field (replace)
    1. if you can't type it, use ctrl-shift-s or command-shift-s to get the insert symbol dialogue, find it, and press insert.
  5. click the replace all button.

More fully:

The question-in-lozenge symbol represents a character which, in the current encoding and font, does not exist, and, often, one that is in a code point (specific numeric representation of what letter is supposed to be there) for non-printing characters. This often happens because prior to switching to unicode for the system general encoding, each operating system dealt with encodings in any of several ways. Windows, through Win-98, didn't use Unicode. Many apps for later windows versions still used the older code page encoding systems, where every character in use for non-oriental languages was one byte, and thus each code page had at most 255 symbols, and getting more than 253 displayable was problematic (null, and del being the ones usually not displayable). Most European sets kept the same 128 as ASCII, and put the language specific characters in 129-254 range. So, given the hundreds of code-pages (definitions for what goes in 129-254, or sometimes, for 32 to 254, or less frequently, 32-65535 for the two-byte encodings), each has different meanings from Unicode.

For reference, WordPerfect still doesn't do Unicode... at least as of 2019. And it doesn't use the standard code page system, either. So it's not just an "old files to new files" issue.

So, here's where it turns into a logic puzzle.

Find one with the question-in-lozenge that you know the correct character for. Figure out how to insert the correct one using either keyboard deadkeys, or via the Edit→Insert Symbol menu item. Once you can get it, select the offending lozenge, hit command-h or control-h (I don't know which it is on the mac, I quit using mine in 2019, as it died), and the lozenge should show in the Find field of the find and replace dialogue. Click the replace field and type in, paste in, or use control-shift-s or command-shift-s (again, I'm not certain which for mac) for the insert symbol dialogue, hit replace all.

Usually, only some of them will go away. That's usually correct; it knows it's looking for a specific character, even if it can't show it to you. Repeat for the next extended latin letter.

It's usually best to do it case-sensitive, so Å doesn't become å and vice versa.

William Hostman
  • 21
  • 1