[Solved] Import text file to current spreadsheet

Discuss the spreadsheet application
Post Reply
nomen
Posts: 11
Joined: Tue Nov 06, 2012 6:52 pm

[Solved] Import text file to current spreadsheet

Post by nomen »

At work, I downloaded and installed LibreOffice 5.3.6.1 and am running it on a Windows 7 PC. I am looking at using Calc instead of Excel. I am trying to compare functionality for what we do with our Excel spreadsheets to see if/how to do the same in Calc.

One of the things we do a ton of is to create template spreadsheet files that contain imported text files. On other sheets/tabs in the file, formulas, pivot tables, etc. use that imported data to produce the desired results. These templates are then distributed to the users who simply open the template, import new data, and save the new results.

I have found in LO the idea of File|Open to open a text file. This works, but it always opens to a new file. I have looked at all the menu options, but there does not seem to be a way to open/import the text file to the current spreadsheet file. I have tried searching this forum for ideas, but none seem to go in this direction.

Am I missing or misunderstanding something in the menus? Or is there a better approach to accomplishing this task?
Last edited by Hagar Delest on Tue Dec 04, 2018 10:26 pm, edited 1 time in total.
Reason: tagged solved
LibreOffice 5.3.6.1 on PCLinuxOS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Import text file to current spreadsheet

Post by Villeroy »

See menu:Sheet>Insert from file...

Since csv data are always database data, you can also use the Base component to connect the office suite with a directory of similar csv files (same delimiters, same encoding etc).
Then you hit F4, and drag csv tables (or queries representing subsets of csv tables) into your template. This will also preserve formatting, update formula references, charts and pivot tables.

[Example] Loading CSV into preformatted spreadsheets
[Tutorial] Using registered datasources in Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ozoneSelf
Posts: 2
Joined: Mon Dec 03, 2018 9:13 am

[SOLVED - thanks]Re: Import text file to current spreadsheet

Post by ozoneSelf »

I have used the Insert sheet from file to create a sheet from a csv/txt file. I choose the various formats to place each row information in separate cells and format them.
This part works well.
I now want to refresh this sheet from another similar csv/txt file which should replace information in this sheet based on the new file.
I am unable to find a way to get this done. The insert sheet from file works, but creates another sheet rather than doing the same in the current sheet.
Something akin to refresh available in excel.
Is there a way to accomplish this.
The F4 and drag csv table creates a single cell per row with ';' inserted between fields. I need them in the individual cells of the row as before
Last edited by ozoneSelf on Tue Dec 04, 2018 3:44 pm, edited 1 time in total.
Open Office 4, Windows 8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Import text file to current spreadsheet

Post by Villeroy »

ozoneSelf wrote:The F4 and drag csv table creates a single cell per row with ';' inserted between fields. I need them in the individual cells of the row as before
Open the database document (F4, right-click>Edit Database...)
Call menu:Edit>Database>Properties... and set ; as column delimiter.
Save the database, restart the office suite.
Open the spreadsheet and refresh the database range.

The dumb links can be refreshed via menu:Edit>Links... [Update]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ozoneSelf
Posts: 2
Joined: Mon Dec 03, 2018 9:13 am

Re: Import text file to current spreadsheet

Post by ozoneSelf »

Thanks a ton. That works!

in the meantime, since my query was on hold for moderation and I was a bit restless, I downloaded the WPS office suite from Kingsoft and that worked like a breeze.
I can refresh the contents on the same sheet as in excel.
Just for Info.
Open Office 4, Windows 8
Post Reply