[Solved] Import text file to current spreadsheet

Discuss the spreadsheet application

[Solved] Import text file to current spreadsheet

Postby nomen » Tue Nov 14, 2017 8:26 pm

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
nomen
 
Posts: 11
Joined: Tue Nov 06, 2012 6:52 pm

Re: Import text file to current spreadsheet

Postby Villeroy » Tue Nov 14, 2017 8:41 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26410
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby ozoneSelf » Mon Dec 03, 2018 9:38 am

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
ozoneSelf
 
Posts: 2
Joined: Mon Dec 03, 2018 9:13 am

Re: Import text file to current spreadsheet

Postby Villeroy » Mon Dec 03, 2018 8:07 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26410
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Import text file to current spreadsheet

Postby ozoneSelf » Tue Dec 04, 2018 3:42 pm

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
ozoneSelf
 
Posts: 2
Joined: Mon Dec 03, 2018 9:13 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests