[Solved] Get tab separated text into columns?

[lesbentley]

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?

[Villeroy]

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.

[lesbentley]

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?

[MrProgrammer]

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.

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.

[lesbentley]

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?

[nomen]

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.

[Villeroy]

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.

[MrProgrammer]

That would cut out some of the work, how do I do that?

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.

[Villeroy]

A range of references requires maintainance when the size of the referred range changes.
Linked database ranges adjust automatically with every refresh.