Get tab separated text into columns?

Discuss the spreadsheet application

Get tab separated text into columns?

Postby lesbentley » Wed Jun 13, 2018 4:00 pm

Get tab separated text into columns?

I have a plain text file containing two contiguous tab separated columns of data. These need to go into two contiguous columns, specifically E and F, in my spreadsheet.

At the moment I open the text file in a text editor, do "Select All">"Copy", then switch to the spreadsheet, select E1 and "Paste" etc.

Is there a way to bypass the text editor, and "Import" the text file directly?
OpenOffice 4.1.3 on Windows 10
lesbentley
 
Posts: 10
Joined: Sat Jun 09, 2018 6:10 pm

Re: Get tab separated text into columns?

Postby Villeroy » Wed Jun 13, 2018 4:19 pm

File>Open...
Navigate to the right folder
File Type: Text (*.csv)
Enter the file name regardless of the name suffix being ".csv" or anything else.

Fill out the import dialog.
Choose the right encoding and separator according to the preview.
Check the correct import language. Comma decimals require another language than point decimals, "31/12/1999" is an English date whereas "12/31/1999" is a US-English date, "31.12.1999" is a German date. Month names are interpreted in the respective language.
Always check "Detect special numbers", otherwise dates, times, percent, currency values will be imported as text.
Your settings are preserved for multiple imports.
--------------
Insert>Sheet_From_File with link option inserts a sheet which is linked to a file which can be a csv file. The import settings are stored in the embedding document.
--------------
Having many similar files in one directory, you may consider to connect a Base document to the directory and use this pseudo-database with spreadsheets and text documents.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25834
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Get tab separated text into columns?

Postby lesbentley » Wed Jun 13, 2018 5:13 pm

Following your instructions just seems to open the txt file in a new sheet, not import it into E1. This is no better than opening it in a text editor, as i still have to cut and paste it to get it into E1 of my own sheet.

Am I doing something wrong?
OpenOffice 4.1.3 on Windows 10
lesbentley
 
Posts: 10
Joined: Sat Jun 09, 2018 6:10 pm

Re: Get tab separated text into columns?

Postby MrProgrammer » Wed Jun 13, 2018 6:34 pm

lesbentley wrote:Is there a way to bypass the text editor, and "Import" the text file directly?
I think not. The best way I know is to open the document with a text editor (which could be OpenOffice Writer), Edit → Select All, select the target cell, say E1, in the spreadsheet, Edit → Paste Special → Unformatted text → Separated By → Tab, optionally set the column field types → OK.

There are (or were, since I thankfully no longer use Windoze and don't know what's changed) tools which would copy the text in a selected file directly to the clipboard. If you can find one of those via a web search, that would replace opening the file in a text editor and selecting all the text. You'd still need to Paste Special in the spreadsheet.

lesbentley wrote:Following your instructions just seems to open the txt file in a new sheet, not import it into E1
However you can have formulas in E1:Fn which copy those cells from the new sheet.

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 Mac OS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3500
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Get tab separated text into columns?

Postby lesbentley » Wed Jun 13, 2018 6:48 pm

However you can have formulas in E1:Fn which copy those cells from the new sheet.


That would cut out some of the work, how do I do that?
OpenOffice 4.1.3 on Windows 10
lesbentley
 
Posts: 10
Joined: Sat Jun 09, 2018 6:10 pm

Re: Get tab separated text into columns?

Postby nomen » Wed Jun 13, 2018 6:57 pm

I have been thinking about how to ask the same question as the OP. Thanks lesbentley for asking.

I work for a small-ish company that I was hoping to convert to either OO or LO. It is too bad that files cannot be imported into the current file/sheet/cell. We do a lot of that here.
OpenOffice 3.2 on PCLinuxOS
nomen
 
Posts: 7
Joined: Tue Nov 06, 2012 6:52 pm

Re: Get tab separated text into columns?

Postby Villeroy » Wed Jun 13, 2018 7:18 pm

If it really has to be E1:
Put the file(s) in a dedicated directory.
File>New>Database...
[X] Connect to existing db
Type: Text
Specify the import details
[X] Register this database
Save the database.
No data will be imported, converted or copied. The data remain in the text file(s).
-----------------------------------------
In Calc hit F4 for the data source window, drop down the name of your database and the "Tables". Then drag the table icon which represents your file onto E1.
The resulting import range at E1 is a dynamic link to the database table (which is a plain text file in the case). See menu:Data>Define... and use menu:Data>Refresh to refresh the linked area when the file has changed.

Within the database document you can produce reports (formatted Writer tables) and simple queries. Simple queries let you select any subset of rows and columns in any order of rows and columns. Queries can be used with Calc just like the tables. Drag from data source window to E1 and you get filtered and/or sorted row sets. Input forms are not particularly useful since you can not edit any data with this setup.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25834
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Get tab separated text into columns?

Postby MrProgrammer » Thu Jun 14, 2018 6:49 pm

lesbentley wrote:That would cut out some of the work, how do I do that?
lesbentley wrote:These need to go into two contiguous columns, specifically E and F, in my spreadsheet.
Villeroy's solution above is better. But to do it without a database let's say the sheet with your imported data is Sheet5. Go to E1 in the sheet where you want to copy the imported data. Type formula =Sheet5.A1 and press Enter. Fill that formula down column E. Then fill column E into column F.

[Tutorial] How do I specify the formula for a column?

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 Mac OS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3500
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Get tab separated text into columns?

Postby Villeroy » Thu Jun 14, 2018 7:25 pm

A range of references requires maintainance when the size of the referred range changes.
Linked database ranges adjust automatically with every refresh.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25834
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 52 guests