[Solved...ish] Export/Import Question

Discuss the spreadsheet application

[Solved...ish] Export/Import Question

Postby Inservio Letum » Thu Feb 13, 2020 5:00 pm

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?
Last edited by Inservio Letum on Wed Feb 26, 2020 11:56 am, edited 2 times in total.
Inservio Letum
 
Posts: 4
Joined: Wed Nov 30, 2016 7:01 pm

Re: Export/Import Question

Postby MrProgrammer » Thu Feb 13, 2020 5:09 pm

Inservio Letum wrote: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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3958
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Export/Import Question

Postby Bill » Thu Feb 13, 2020 5:58 pm

Inservio Letum wrote:...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.
AOO 4.1.7 and LO 6.3.2.2 on Manjaro KDE
Bill
Volunteer
 
Posts: 7635
Joined: Sat Nov 24, 2007 6:48 am

Re: Export/Import Question

Postby robleyd » Thu Feb 13, 2020 11:22 pm

You could try using Text to Table to convert the relevant parts to a table, then simply copy the table into a spreadsheet.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3195
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Export/Import Question

Postby Inservio Letum » Wed Feb 19, 2020 2:28 pm

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 :
Attachments
Nightmare.odt
(19.14 KiB) Downloaded 14 times
Inservio Letum
 
Posts: 4
Joined: Wed Nov 30, 2016 7:01 pm

Re: Export/Import Question

Postby Villeroy » Wed Feb 19, 2020 2:43 pm

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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27888
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Export/Import Question

Postby jrkrideau » Wed Feb 19, 2020 8:25 pm

Inservio Letum wrote: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.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3759
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Export/Import Question

Postby Inservio Letum » Wed Feb 26, 2020 11:54 am

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 :D
Inservio Letum
 
Posts: 4
Joined: Wed Nov 30, 2016 7:01 pm

Re: Export/Import Question

Postby jrkrideau » Wed Feb 26, 2020 4:53 pm

Inservio Letum wrote: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 :D


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.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3759
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada


Return to Calc

Who is online

Users browsing this forum: No registered users and 20 guests