Re: [libreoffice-users] How to import a txt file into an existing Calc sheet?

Hi :)
I think i would approach this by using a database program rather than importing 
into a spreadsheet program.  A database program such as Base should be able to 
read the text-files directly.  Queries, Forms and Reports can be set-up to do 
the required calculations.  


Setting it up would not be trivial!  It could be quite tricky especially if you 
have never used a database program before.  I would guess that it was not 
trivial to set it up for Excel either.

Could you send me attachments off-list so i could have a copy of an original 
text-file, the blank you import the text into and an example of a completed 
sheet?  I might have enough time to have a quick poke around and perhaps show to 
some databasing experts over the next couple of weeks.  


Until you can get Calc or Base or something to do the task you will need to keep 
going back to Windows and MS Office.  Migrating away from systems you have spent 
years becoming familiar with often means returning for odd 1 or 2 tasks until 
you are ready.  There is no harm in running both systems alongside each other 
until you are ready to move over completely.

Good luck and regards from
Tom :)






________________________________
From: George R. Kasica <georgek@netwrx1.com>
To: users@global.libreoffice.org
Sent: Mon, 25 July, 2011 12:10:52
Subject: Re: [libreoffice-users] How to import a txt file into an existing Calc 
sheet?

> At 07:48 23/07/2011 -0500, George R. Kasica wrote:
> > I have a currently existing Excel 2007 sheet that I can do a (In 
> > Excel) Refresh All for \\APOLLO\e\WeatherLink\JACW3\download.txt ...
>
> So that's a file available to your system?
Yes, its a flat file created from some weather equipment.

> > ... which is a te[x]t file in the format of: [...] and is properly 
> > formatted automatically by a Text-To-Columns setup already in the MS 
> > Excel sheet so all I need to do each day is Refresh All and select 
> > the filename and it pulls in the data in the proper column.
> >
> > For the life of me I cannot see how to do this in Calc...and opening 
> > the existing sheet gets the right format, but I'm at a loss of how 
> > to get new data into it without redoing the whole text to columns 
> > every time ...
>
> Try this:
> o  Go to Insert | Sheet From File..., browse to your .txt file, and 
> click Insert.
> o  In the Text Import dialogue, under "Separator options", select 
> "Separated by", "Space", and "Merge delimiters".
> o  Click OK.
> o  In the Insert Sheet dialogue, select the position of your new 
> sheet and - crucially - tick Link.
> o  Click OK.
OK..that gets the file in, but the first 4 rows that are always locked
with Headers get replaced as well even if I say from Line 4, and I've
tried both before and after sheet options. Also there are some calc
lines at the bottom that in Excel are automatically moved down if
there is more data than what is there now. With Calc it blows them
away and replaces them with data, BIG problem as some of the formulas
are quite time consuming to reenter and there's at least one  and
possibly as much as 3 for each column (Max, Min, etc)

> Each time you open this spreadsheet, you will have the option to 
> update this new sheet; alternatively, you can go to Edit | Links... 
> and click Update.  Note that, since the entire sheet is updated, any 
> calculations you perform on that sheet will be lost - so you will 
> have to move your calculations to other sheets.
OK...so I have to make a 2nd sheet then to do my calcs? Let me look at
that today once.

> If this data is downloaded from the web, you may prefer instead to 
> use Insert | Link to External Data... and import directly from the web page.
No, its local, though it could be accessed by web as well. Let me look
at this as well.

> > ... (not an option due to time constraints here each day when this 
> > has to happen...data is available at 59 past the hour and needs to 
> > just come in to be used by 15 past the next hour.
>
> You can do a lot in sixteen minutes!
Like I said, the automated linking and calcs are critical to this
working. Much of the process I do it cookie cutter hence the premade
sheet etc.

> I trust this helps.
I'll work with it a bit today and let you know where I end up.

-- 
===[George R. Kasica]===        +1 262 677 0766
President                       +1 206 374 6482 FAX 
Netwrx Consulting Inc.          Jackson, WI USA 
http://www.netwrx1.com
georgek@netwrx1.com
ICQ #12862186

-- 
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
-- 
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted