[Solved] Creating column breaks from text file

[deliotk]

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

Thanks for any help you could offer

[MrProgrammer]

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.

[deliotk]

Sample.txt

(269 Bytes) Downloaded 158 times

Ok, here's a small sample attached. Thanks Mr. Programmer

[RusselB]

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.

[MrProgrammer]

«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.

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.

[deliotk]

Sample.txt

(925 Bytes) Downloaded 171 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

[RusselB]

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

[RoryOF]

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.

[deliotk]

Thanks for the info Rory and Russel. Works perfectly.