George R. Kasica | 23 Jul 14:48 2011

How to import a txt file into an existing Calc sheet?

Hello:

I have a currently existing Excel 2007 sheet that I can do a (In
Excel) Refresh All for \\APOLLO\e\WeatherLink\JACW3\download.txt which
is a test file in the format of:

                  Temp     Hi    Low   Out         Wind        Wind Hi
Hi   Wind   Heat          THSW                Rain  Solar Solar Hi
Solar         UV    Hi     Heat    Cool    In     In    In In     In
In Air          Soil 1   Soil    Leaf  Wind  Wind    ISS Arc.
  Date    Time     Out   Temp   Temp   Hum    Dew Speed   Dir   Run
Speed   Dir  Chill  Index    THW  Index   Bar    Rain  Rate   Rad.
Energy    Rad.    UV   Dose   UV     D-D     D-D    Temp   Hum    Dew
Heat    EMC Density     ET   Moist.  Temp 1  Wet 1  Samp   Tx   Recept
Int.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 7/22/11   0:01   75.6   75.6   75.6    69   64.7   1.0   WNW  0.02
1.0   WNW   75.6   77.3   77.3   75.1  1009.5  0.00  0.00     0 0.00
0     0.0  0.00   0.0   0.000   0.007   79.5    46   56.9 79.3   8.46
.0722    0.000    153    69.0     15      9    1     39.1 1 
 7/22/11   0:02   75.6   75.6   75.6    69   64.7   2.0   WNW  0.03
3.0   WNW   75.6   77.3   77.3   75.1  1009.5  0.00  0.00     0 0.00
0     0.0  0.00   0.0   0.000   0.007   79.5    47   57.5 79.3   8.66
.0722    0.000    153    69.0     15     20    1     87.0 1 
 7/22/11   0:03   75.6   75.6   75.6    69   64.7   1.0   WNW  0.02
2.0   WNW   75.6   77.3   77.3   75.1  1009.5  0.00  0.00     0 0.00
0     0.0  0.00   0.0   0.000   0.007   79.5    47   57.5 79.3   8.66
.0722    0.000    153    69.0     15     22    1     95.7 1 
 7/22/11   0:04   75.6   75.6   75.6    69   64.7   1.0   WNW  0.02
3.0   WNW   75.6   77.3   77.3   75.1  1009.5  0.00  0.00     0 0.00
(Continue reading)

Brian Barker | 23 Jul 16:53 2011

Re: 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?

>... 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.

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 
(Continue reading)

George R. Kasica | 25 Jul 13:10 2011

Re: 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
(Continue reading)

Tom Davies | 25 Jul 15:32 2011
Picon

Re: 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 <at> netwrx1.com>
To: users <at> 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?
(Continue reading)

George R. Kasica | 25 Jul 16:00 2011

Re: 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.  
Interesting idea, and I'm open to change as long as it gives accurate
results I'm open to it.

>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.
It wasn't, I'm an IT guy by trade so am familiar with several DB
products.

>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.  
Look for them shortly along with the Excel sheet as well.

>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.
That's what I'm doing now, no hurry here, I'm just finally getting
time to work on moving off 2007 because I don't want to pay huge $$ to
go to 2010 for a few items I use it for.

>Good luck and regards from
(Continue reading)

Alexander Thurgood | 25 Jul 16:34 2011
Picon

Re: How to import a txt file into an existing Calc sheet?

Le 25/07/11 15:32, Tom Davies a écrit :

Hi all,

> 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.  
> 

Yes, you can bind text files directly to the HSQLDB backend :

http://hsqldb.org/doc/guide/ch06.html

in order to access them from LibreOffice, you would first need to create
an empty ODB database document, e.g. using the Database Creation Wizard.
Once the file has been created and saved, close it, reopen it, and then
execute the SQL statement provided in the HSQLDB documentation to bind
your text tables into the database. The SQL commands are executed from
the menu Tools > SQL... and not from the Query UI of the ODB container.

Alternatively, open your CSV in Calc, and then copy the whole sheet over
to the new DB as a new table. Things to be aware of if you are going the
Calc to Base route:

- type recognition between Base and Calc is not perfect, i.e. the engine
used for type recognition of data types sometimes gets it horribly wrong
for certain data types like decimal point containing data, date or
date/time data, especially if the locale is not EN-US.

(Continue reading)

Daryl Cook | 25 Jul 17:14 2011

Re: Re: How to import a txt file into an existing Calc sheet?

I think I have it worked out.
Thanks

On Mon, Jul 25, 2011 at 10:34 AM, Alexander Thurgood <
alex.thurgood <at> gmail.com> wrote:

> Le 25/07/11 15:32, Tom Davies a écrit :
>
> Hi all,
>
>
> > 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.
> >
>
> Yes, you can bind text files directly to the HSQLDB backend :
>
> http://hsqldb.org/doc/guide/ch06.html
>
> in order to access them from LibreOffice, you would first need to create
> an empty ODB database document, e.g. using the Database Creation Wizard.
> Once the file has been created and saved, close it, reopen it, and then
> execute the SQL statement provided in the HSQLDB documentation to bind
> your text tables into the database. The SQL commands are executed from
> the menu Tools > SQL... and not from the Query UI of the ODB container.
(Continue reading)

Andreas Säger | 28 Jul 00:25 2011
Picon

Re: How to import a txt file into an existing Calc sheet?

Am 25.07.2011 15:32, Tom Davies wrote:
> 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.
>

Right, a Base makes text import into office documents easy. You can 
specify the delimiters and everything once for ever and use the 
resulting database connection in all documents.
There are 2 ways. One uses Base for trivial read-only access to a whole 
directory of csv files.
The second method is less trivial and utilizes the built-in HSQLDB (or 
some other connected database server) to bind one particular text file 
to one particular database table for full database access.

Example with 5 files demonstrating several use cases with HSQLDB, Base 
and Calc: 
http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=23727

--

-- 
Unsubscribe instructions: E-mail to users+help <at> 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
Jean-Francois Nifenecker | 25 Jul 19:25 2011
Picon

Re: How to import a txt file into an existing Calc sheet?

Le 25/07/2011 13:10, George R. Kasica a écrit :
> 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)

In such a situation, I import into a file (always named identically) 
which I then use as a source for an external worksheet where the actual 
computations are done. This way, the import never breaks anything.

-- 
Jean-Francois Nifenecker, Bordeaux

--

-- 
Unsubscribe instructions: E-mail to users+help <at> 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

David B Teague sr | 27 Jul 14:04 2011
Picon

Re: How to import a txt file into an existing Calc sheet?

Using your data, I used
Edit > "paste special"
then chose "unformatted text" from the dialog.  The dialog then wanted 
to know what the separator was. I tried "space" and it inserted data 
into cells but your data has a number spaces so I got many blank cells.

I tried to replace multiple spaces with a comma, and that worked, but 
your data doesn't have a consistent number of separating spaces so that 
did not appear to be useful. I played with it, and I believe if you 
separate the data items with a single space, a tab, or something that 
Calc likes for a separator you may have some luck with "paste special".

Hope this helps.

--David Teague

On 7/23/2011 8:48 AM, George R. Kasica wrote:
> Hello:
>
> I have a currently existing Excel 2007 sheet that I can do a (In
> Excel) Refresh All for \\APOLLO\e\WeatherLink\JACW3\download.txt which
> is a test file in the format of:
>
>                    Temp     Hi    Low   Out         Wind        Wind Hi
> Hi   Wind   Heat          THSW                Rain  Solar Solar Hi
> Solar         UV    Hi     Heat    Cool    In     In    In In     In
> In Air          Soil 1   Soil    Leaf  Wind  Wind    ISS Arc.
>    Date    Time     Out   Temp   Temp   Hum    Dew Speed   Dir   Run
> Speed   Dir  Chill  Index    THW  Index   Bar    Rain  Rate   Rad.
> Energy    Rad.    UV   Dose   UV     D-D     D-D    Temp   Hum    Dew
(Continue reading)

Tom Davies | 27 Jul 14:09 2011
Picon

Re: How to import a txt file into an existing Calc sheet?

Hi :)
I had a go at using "Fixed width" but then needed to define each width in the 
little box at the bottom which was uncomfortably small on my screen at my 
resolution.  Later on i tried it again and it had remembered the widths i had 
defined last time.  Sadly i could not find the fixed width thing in Base.
Regards from
Tom :)

________________________________
From: David B Teague sr <davidbteague <at> comporium.net>
To: users <at> global.libreoffice.org
Sent: Wed, 27 July, 2011 13:04:59
Subject: Re: [libreoffice-users] How to import a txt file into an existing Calc 
sheet?

Using your data, I used
Edit > "paste special"
then chose "unformatted text" from the dialog.  The dialog then wanted to know 
what the separator was. I tried "space" and it inserted data into cells but your 
data has a number spaces so I got many blank cells.

I tried to replace multiple spaces with a comma, and that worked, but your data 
doesn't have a consistent number of separating spaces so that did not appear to 
be useful. I played with it, and I believe if you separate the data items with a 
single space, a tab, or something that Calc likes for a separator you may have 
some luck with "paste special".

Hope this helps.

--David Teague
(Continue reading)

Andreas Säger | 27 Jul 14:30 2011
Picon

Re: How to import a txt file into an existing Calc sheet?

Am 27.07.2011 14:09, Tom Davies wrote:
> Hi :)
> I had a go at using "Fixed width" but then needed to define each width in the
> little box at the bottom which was uncomfortably small on my screen at my
> resolution.  Later on i tried it again and it had remembered the widths i had
> defined last time.  Sadly i could not find the fixed width thing in Base.
> Regards from
> Tom :)
>

In Base a query with function SUBSTR can split the lines of text.

--

-- 
Unsubscribe instructions: E-mail to users+help <at> 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
JOE Conner | 27 Jul 19:01 2011
Picon

Re: How to import a txt file into an existing Calc sheet?

On 7/27/2011 5:04 AM, David B Teague sr wrote:
> Using your data, I used
> Edit > "paste special"
> then chose "unformatted text" from the dialog.  The dialog then wanted 
> to know what the separator was. I tried "space" and it inserted data 
> into cells but your data has a number spaces so I got many blank cells.
>
> I tried to replace multiple spaces with a comma, and that worked, but 
> your data doesn't have a consistent number of separating spaces so 
> that did not appear to be useful. I played with it, and I believe if 
> you separate the data items with a single space, a tab, or something 
> that Calc likes for a separator you may have some luck with "paste 
> special".
>
> Hope this helps.
>
> --David Teague
Did you not have the checkbox checked for "merge delimiters"?
Joe Conner, Poulsbo, WA USA

--

-- 
Unsubscribe instructions: E-mail to users+help <at> 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

David B Teague sr | 27 Jul 20:59 2011
Picon

Re: How to import a txt file into an existing Calc sheet?

On 7/27/2011 1:01 PM, JOE Conner wrote:
> On 7/27/2011 5:04 AM, David B Teague sr wrote:
>> Using your data, I used
>> Edit > "paste special"
>> then chose "unformatted text" from the dialog.  The dialog then 
>> wanted to know what the separator was. I tried "space" and it 
>> inserted data into cells but your data has a number spaces so I got 
>> many blank cells.
>>
>> I tried to replace multiple spaces with a comma, and that worked, but 
>> your data doesn't have a consistent number of separating spaces so 
>> that did not appear to be useful. I played with it, and I believe if 
>> you separate the data items with a single space, a tab, or something 
>> that Calc likes for a separator you may have some luck with "paste 
>> special".
>>
>> Hope this helps.
>>
>> --David Teague
> Did you not have the checkbox checked for "merge delimiters"?
> Joe Conner, Poulsbo, WA USA
>
OOOOooooh. I did NOT. I'll have to look for that. It will save me some work.
Thanks
David

-- 
nil significat nisi oscillat

--

-- 
(Continue reading)

David B Teague sr | 27 Jul 21:20 2011
Picon

Re: How to import a txt file into an existing Calc sheet?

On 7/27/2011 1:01 PM, JOE Conner wrote:
> On 7/27/2011 5:04 AM, David B Teague sr wrote:
>> Using your data, I used
>> Edit > "paste special"
>> then chose "unformatted text" from the dialog.  The dialog then 
>> wanted to know what the separator was. I tried "space" and it 
>> inserted data into cells but your data has a number spaces so I got 
>> many blank cells.
>>
>> I tried to replace multiple spaces with a comma, and that worked, but 
>> your data doesn't have a consistent number of separating spaces so 
>> that did not appear to be useful. I played with it, and I believe if 
>> you separate the data items with a single space, a tab, or something 
>> that Calc likes for a separator you may have some luck with "paste 
>> special".
>>
>> Hope this helps.
>>
>> --David Teague
> Did you not have the checkbox checked for "merge delimiters"?
> Joe Conner, Poulsbo, WA USA
>
As I said, I did not, and had not seen the message on this that suggests 
that ... but I just tried it-- it works like a charm. Thanks.
--David

-- 
nil significat nisi oscillat

--

-- 
(Continue reading)


Gmane