[Solved...ish] Export/Import Question

[Inservio Letum]

Hi there,

A colleague has compiled a list of contact information in our company over the last few years, and did so in ODT for simplicity's sake as the document also contains the emergency procedures and regulations of each department. I've now been asked to transfer this document into ODS, and am only now realising there appears to be no easy import option. When I save the document as TXT and import with tab seperation, only the first column lines up, while the rest of the data is peppered over the next 3-10 columns depending on surname length, because shorter surnames required more tabs to line the phone numbers up in the ODT.

So my question : how do I import ODT into ODS, or export ODT to ODS, in such a way that I get the same columns in the ODS as in the ODT?

[MrProgrammer]

When I save the document as TXT and import with tab seperation, only the first column lines up, while the rest of the data is peppered over the next 3-10 columns depending on surname length, because shorter surnames required more tabs to line the phone numbers up in the ODT.

Review [Tutorial] VLOOKUP questions and answers. Perhaps you can use the Merge Delimiters option. If you need additional assistance attach your original Writer document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Indicate clearly what your goal is — what data in what columns. If volunteers have to guess at your objective, you may be disappointed.

Edit: Oops. Intended link was [Tutorial] Text to Columns

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Bill]

...the rest of the data is peppered over the next 3-10 columns depending on surname length, because shorter surnames required more tabs to line the phone numbers up in the ODT.

That's probably because the default tab stops were used instead of user-defined tab stops. When a user-defined tab stop is inserted in a paragraph, the default tab stops to the left of the user-defined tab stop are removed and only one tab needs to be inserted between the columns.

[robleyd]

You could try using Text to Table to convert the relevant parts to a table, then simply copy the table into a spreadsheet.

[Inservio Letum]

Apologies for my aparent lack of reply, I was unaware waterfox apparently no longer refreshes tabs when it detects changes. Was wondering why nobody answered, entirely my bad.

Here is one page of the document, with names & numbers anonymised :

[Villeroy]

This is human readable information. Someone has to write a program in order to convert this into machine readable data sets. A machine readable data set would look like this:

Code: Select all

Name  Phone  Email Department  Position(column labels)
---------------------------------------------------------------- (data below labels)
name1  12345  x@y.org  Wijkvereniging  Administratie
name2  54321  a@b.foo  Wijkvereniging  Leden-administratie
name3  98765  d@c.com  Commissie      Voorzitter

From this most simple, machine readable data layout it would be even possible to automatically generate something like your text document.

[jrkrideau]

Apologies for my aparent lack of reply, I was unaware waterfox apparently no longer refreshes tabs when it detects changes. Was wondering why nobody answered, entirely my bad.

Here is one page of the document, with names & numbers anonymised :

Please do not attack the person who created the document. They knew no better.

A very crude method is to go through the document with Find & Replace, Regular expressions enabled, two or three times.
First pass
Find \t\t\t\t
Replace \t

Second pass
Find \t\t\t
Replace
\t

Third pass
Find \t\t
Replace
\t

This will not solve all the problems but a manual edit either in Writer or Calc should be fairly easy unless you have hundreds or thousands of pages.

I hope someone has a better solution.

[Inservio Letum]

Mr. Curtain, I'm not sure where you surmised I was on the offensive (let alone attacking anyone) but I'll chalk that up to a misunderstanding.
In the absence of alternative idea's I tried your F&R solution on a copy of the document, which was surprisingly effective, if alarming to the naked eye prior to import. As this effectively alleviates the problem I think I can mark this as solved, although it would be nice --if and when someone does come across a way to do this as a simple import/export-- to see such solutions posted here.

Thank you all for your help thus far

[jrkrideau]

Mr. Curtain, I'm not sure where you surmised I was on the offensive (let alone attacking anyone) but I'll chalk that up to a misunderstanding.
In the absence of alternative idea's I tried your F&R solution on a copy of the document, which was surprisingly effective, if alarming to the naked eye prior to import. As this effectively alleviates the problem I think I can mark this as solved, although it would be nice --if and when someone does come across a way to do this as a simple import/export-- to see such solutions posted here.

Thank you all for your help thus far

Sorry about the "attack" comment. I have a weird Canadian sense of humour. It was a joke.

I am glad that my approach works. It is a brute force approach but sometimes they are the best. It would be nice to see better approachs but I am not sure they exist.