[Solved] Creating column breaks from text file

Discuss the spreadsheet application

[Solved] Creating column breaks from text file

Postby deliotk » Sat Sep 14, 2019 2:10 am

Hi, I'm new to spreadsheets. Not a fan, but... I need to extract names and email addresses from a text file. Data is separated in the text file by vertical lines; either single, double or triple vertical lines. I've read these are called column breaks, or rather they indicate column breaks for a spread sheet.

If I could get this text to put itself into separate columns, I should have an easier job of getting the email addresses along with the full names.

Then I'll have a smaller headache :knock:

Thanks for any help you could offer :super:
Last edited by deliotk on Sat Sep 14, 2019 5:57 pm, edited 1 time in total.
Apache Open Office 4.1.6

Windows 10
deliotk
 
Posts: 4
Joined: Sat Sep 14, 2019 1:52 am

Re: Creating column breaks from text file

Postby MrProgrammer » Sat Sep 14, 2019 2:21 am

deliotk wrote:I need to extract names and email addresses from a text file. Data is separated in the text file by vertical lines; either single, double or triple vertical lines.
[Tutorial] Text to Columns

Presumably double lines indicate an empty column. If not you could use Merge Delimiters. For further assistance attach a 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).

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

Re: Creating column breaks from text file

Postby deliotk » Sat Sep 14, 2019 2:44 am

Sample.txt
(269 Bytes) Downloaded 15 times
Ok, here's a small sample attached. Thanks Mr. Programmer
Apache Open Office 4.1.6

Windows 10
deliotk
 
Posts: 4
Joined: Sat Sep 14, 2019 1:52 am

Re: Creating column breaks from text file

Postby RusselB » Sat Sep 14, 2019 3:06 am

When you import the file, use the | (pipe) in the Separator options box marked Other
On my keyboard, that character is located above the backslash key... or you should be able to copy it from this post and paste it into the Text Import Dialog.
I also recommend checking the Detect Special Numbers.
OpenOffice 4.1.7 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5488
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Creating column breaks from text file

Postby MrProgrammer » Sat Sep 14, 2019 4:10 pm

deliotk wrote:«Sample.txt» Ok, here's a small sample attached.
OpenOffice will open a TXT file with Writer. You have three choices:
• Copy the text from Writer to the clipboard; open a spreadsheet; use Edit → Paste Special → Unformatted text
• Open a spreadsheet; use Insert → Sheet from file → Sample.txt
• If you know how, using your operating system, rename Sample.txt to Sample.csv; open the CSV file with OpenOffice.

Looking at the sample, I see the multiple bars. In the Text Import dialog use Separated by → Other → | (and you should probably uncheck Tab). Only you would know if || or ||| represent empty columns. If you don't want the empty columns use Merge Delimiters.

I also see data like 06/26/1900. Only you would know if this represents a date. If so, set the Column Type to Date (MDY) as explained in the Text to Columns tutorial, and you must use use Detect Special Numbers. Only you would know whether 000 represents a numeric zero or a three digit code where all of the digits must be retained. In the latter case, mark the Column Type as Text. You can probably use Column Type Standard for most of the fields, but you really should examine each one. Because it is preceded by FL, I can guess that 34000 is a ZIP code and should be marked as Text to avoid loss of leading zeros, but Calc can't tell so you have to set the Column Type for it.

deliotk wrote:I'm new to spreadsheets.
[Tutorial] Ten concepts that every Calc user should know

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

Re: Creating column breaks from text file

Postby deliotk » Sat Sep 14, 2019 5:24 pm

Sample.txt
(925 Bytes) Downloaded 7 times


Well, that seems to have worked just fine : ) Thanks.

I opened a spreadsheet and used the insert sheet from file, downloaded a special character pdf -- duh -- for the upright line in -other- checked merge delimiter and left tab unchecked.

Now I'm getting picky : ) the column headings appear on row 1 and so disappear when I scroll down. Also -- and this is surely the fault of the people who entered the data originally and/or the way it was transposed to text, but none of the emails are under the email heading and and seem to be in three different columns.

I should have included this originally I guess but I am now attaching the heading portion of the text file so you can see for yourself and maybe give me some advice to get it to sit above row 1 where it should be.

I also included a complete sample of what I assume is one row of data so you can see exactly what is what.

In any case the spreadsheet is now in a far more usable form for my purposes, thanks again for your time and assistance :super:
Apache Open Office 4.1.6

Windows 10
deliotk
 
Posts: 4
Joined: Sat Sep 14, 2019 1:52 am

Re: Creating column breaks from text file

Postby RusselB » Sat Sep 14, 2019 6:00 pm

When you scroll down the top lines will scroll off the screen.
To keep lines showing, go to the row under the bottom one you want showing, then go to Window->Freeze
OpenOffice 4.1.7 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5488
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Creating column breaks from text file

Postby RoryOF » Sat Sep 14, 2019 7:06 pm

RusselB wrote:When you scroll down the top lines will scroll off the screen.
To keep lines showing, go to the row under the bottom one you want showing, then go to Window->Freeze


That also works for column - position cursor in the first column beyond those you want to be always on display, and /Window /Freeze.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29559
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Creating column breaks from text file

Postby deliotk » Sun Sep 15, 2019 3:02 am

Thanks for the info Rory and Russel. Works perfectly. :super:
Apache Open Office 4.1.6

Windows 10
deliotk
 
Posts: 4
Joined: Sat Sep 14, 2019 1:52 am


Return to Calc

Who is online

Users browsing this forum: MSN [Bot] and 19 guests