Registered ”database”

classic Classic list List threaded Threaded
2 messages Options
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Registered ”database”

0Hi. Can anyone tell me how to actually do this? I feel very stupid at the
moment…

I registered a CSV as a database. the CSV is constantly growing and I need
it to be displayed in Calc and the spreadsheet also contains two more
columns with calculations based on the data in the CSV.

Here's a typical line of the CSV:
2017-11-24 20:11\tSomeName\t9:07,122

First of all, when opening the ODS, hitting Ctrl+Shift+F4, selecting the
CSV, I can see the data from the CSV in the right part of the window that
was pulled down. However the data is displayed in some crazy odd format.
The above example looks like crap, that is:
17-11-24 20:11 SomeName 0:09:07
So the date is 2000 years too old (another way to say that I don't like
when years are represented by only two digits) and the milliseconds are
missing in the right column.
So I right click the headers and format those columns manually, then copy
and paste into the spreadsheet.
Still 000 years are missing and the milliseconds are gone. If I manually
format the columns in the spreadsheet, the above example will now look like:
2017-11-24 20:11 SomeName 9:07,000

Yesterday I actually could do this without losing milliseconds or anything,
but today I tried everything I could think of and still failed, I don't
have a clue what I did right yesterday.

So how is this supposed to be done?

In my world it would all update automatically every time I open the ODS,
but that's obviously not the case here. Is there a way to make that happen?

Also there doesn't seem to be a way to update the spreadsheet without
losing the formatting. I have some conditional formatting involved, but I
have to redo it every time, which is 100 % anoying.

Last time I did something like this, I simply just read the damned CSV with
a macro, line by line, converted the data properly with the very same macro
and assigned all the values to the range, cell by cell. A lot slower, of
course, but that's the only thing that actually worked for me so far. It
was slow, but since I only expect a couple of thousand lines anyway, that
shouldn't be a problem.

But this database registering approach seems to be the way to go, so it
would be nice to learn how to do it properly, so it's always updated and
properly formatted whenever I open the ODS.


Kind regards

Johnny Rosenberg

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Mike Adams Mike Adams
Reply | Threaded
Open this post in threaded view
|

Re: [libreoffice-users] Registered ”database”

Why have you registered it as a database? Why not just import it into Calc
and save it as a spreadsheet?

I am guessing the CSV is being generated externally to LibreOffice?

You could create a View of the data in Base. That is when and why
registering it as a database becomes useful. That is when it allows you to
manipulate and extract useful data subsets. There are beginner Base
tutorials online.

On 25 November 2017 at 08:30, Johnny Rosenberg <[hidden email]>
wrote:

> 0Hi. Can anyone tell me how to actually do this? I feel very stupid at the
> moment…
>
> I registered a CSV as a database. the CSV is constantly growing and I need
> it to be displayed in Calc and the spreadsheet also contains two more
> columns with calculations based on the data in the CSV.
>
> Here's a typical line of the CSV:
> 2017-11-24 20:11\tSomeName\t9:07,122
>
> First of all, when opening the ODS, hitting Ctrl+Shift+F4, selecting the
> CSV, I can see the data from the CSV in the right part of the window that
> was pulled down. However the data is displayed in some crazy odd format.
> The above example looks like crap, that is:
> 17-11-24 20:11 SomeName 0:09:07
> So the date is 2000 years too old (another way to say that I don't like
> when years are represented by only two digits) and the milliseconds are
> missing in the right column.
> So I right click the headers and format those columns manually, then copy
> and paste into the spreadsheet.
> Still 000 years are missing and the milliseconds are gone. If I manually
> format the columns in the spreadsheet, the above example will now look
> like:
> 2017-11-24 20:11 SomeName 9:07,000
>
> Yesterday I actually could do this without losing milliseconds or anything,
> but today I tried everything I could think of and still failed, I don't
> have a clue what I did right yesterday.
>
> So how is this supposed to be done?
>
> In my world it would all update automatically every time I open the ODS,
> but that's obviously not the case here. Is there a way to make that happen?
>
> Also there doesn't seem to be a way to update the spreadsheet without
> losing the formatting. I have some conditional formatting involved, but I
> have to redo it every time, which is 100 % anoying.
>
> Last time I did something like this, I simply just read the damned CSV with
> a macro, line by line, converted the data properly with the very same macro
> and assigned all the values to the range, cell by cell. A lot slower, of
> course, but that's the only thing that actually worked for me so far. It
> was slow, but since I only expect a couple of thousand lines anyway, that
> shouldn't be a problem.
>
> But this database registering approach seems to be the way to go, so it
> would be nice to learn how to do it properly, so it's always updated and
> properly formatted whenever I open the ODS.
>
>
> Kind regards
>
> Johnny Rosenberg
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-
> unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted