Re: [libreoffice-users] export data from Calc, import to Base
Hi Harvey, mark the content. Copy it to clipboard. Go to the database file and open the tables pane. Right mousclick → insert. Choose the table where to append the data to, choose the column in the wizard and it will be done. You won't need a *.csv-file and phpMyAdmin to do this the same way for MariaDB/MySQL. Regards Robert -- Homepage: https://www.familiegrosskopf.de/robert -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Tab Bug?
Does not occur for me in 7.3.4.2 What text language shows at the bottom of the window frame. (mine is English(UK) What decimal separator is shown in Tools>Options>Language settings Steve On 09/08/2022 12:18, Dave Liesse wrote: Hi, all. It's been a long time since I've run into a problem of any significance, so posting here before I look up how to report a bug (partly to see if this really is one). In a Writer document, I have three Right tab stops set, to show staggered columns of numbers. When I type a large number, though, with commas separating the thousands, the tab stop starts acting as a Decimal stop instead of Right. For example, if I type 1,234,567 it acts as though the tab stop is a Decimal stop and the comma between the 4 and the 5 is a decimal point. Might this be a bug? As a secondary note: I don't think this one is a bug, but it's certainly unexpected behavior. Because I was in a hurry to get a report out to a client I tried the same numbers in a spreadsheet, again expecting the columns would be overlapping. Numeric values, though, won't overflow the cell the way text does, even when the cell is specifically right-justified. Again, I was in a hurry so I just entered all the numbers as text, but then had to do my own calculations to show totals (no biggie, since I would have had to do that anyway in the Writer document, but annoying in a spreadsheet). The question is really about the first item -- the second one is just a minor gripe. Do y'all think this is a bug, or is there some rational reason the program should behave this way? Dave -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Tab Bug?
Hi, all. It's been a long time since I've run into a problem of any significance, so posting here before I look up how to report a bug (partly to see if this really is one). In a Writer document, I have three Right tab stops set, to show staggered columns of numbers. When I type a large number, though, with commas separating the thousands, the tab stop starts acting as a Decimal stop instead of Right. For example, if I type 1,234,567 it acts as though the tab stop is a Decimal stop and the comma between the 4 and the 5 is a decimal point. Might this be a bug? As a secondary note: I don't think this one is a bug, but it's certainly unexpected behavior. Because I was in a hurry to get a report out to a client I tried the same numbers in a spreadsheet, again expecting the columns would be overlapping. Numeric values, though, won't overflow the cell the way text does, even when the cell is specifically right-justified. Again, I was in a hurry so I just entered all the numbers as text, but then had to do my own calculations to show totals (no biggie, since I would have had to do that anyway in the Writer document, but annoying in a spreadsheet). The question is really about the first item -- the second one is just a minor gripe. Do y'all think this is a bug, or is there some rational reason the program should behave this way? Dave -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date
For the following I assumed the “simple date” column is is date format.. If not you can get to that by entering the formula =IFERROR(DATEVALUE(LEFT($RAW.A3;10));"") and pull that down. On another sheet I entered the “query date” in B2 The next formula gives you the max temp on that date: =MAXIFS($’RAW data'.$C$2:$C$16;$’RAW data'.B$2:B$16;"="&$B$1) Hope this helps, Rob > Op 8 aug. 2022, om 21:25 heeft Hylton Conacher (ZR1HPC) > het volgende geschreven: > > Hi, > > Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data table in excess > of 65k rows. A three column sample below: > Date Simple Date Outdoor Temp > 2022-01-01T00:05:00+02:00 2022-01-01 00:05:00 17.5 > 2022-01-01T00:00:00+02:00 2022-01-01 00:00:00 17.4 > > I have a secondary table, called Amended RAW Data containing just the 10 > digit date and the corresponding value of the column value I am seeking, > whether it be the Outdoor Temp, Daily Rainfall etc > > What I need to do is query the following table, searching for a specific 10 > digit date, and find the max value on a specific date. > > I have tried so many different itinerations on the VLOOKUP formula and > browsed many elementary Google VLOOKUP pages that I need help. I have > tried(all return N/A): > > =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62037,10),$'RAW > Data'.$B$2:$L$62037,1,FALSE)) > > =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11)) > > =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11)) > > =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62307,10)=$'Amended RAW > Data'.$A$11,$'RAW Data'.$B$2:$L$62037,11,0)) > > References to 'Amended RAW Data'.$A$11 refer to a date that I know has > greater than zero value, and the 11th column data I want to max, within a > date. > > Help appreciated > Hylton > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > 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/ > Privacy Policy: https://www.documentfoundation.org/privacy -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date
On 8 Aug 2022 at 21:25, Hylton Conacher (ZR1HPC) wrote: Date sent: Mon, 8 Aug 2022 21:25:44 +0200 To: LibreOffice Users From: "Hylton Conacher (ZR1HPC)" Subject:[libreoffice-users] VLOOKUP Syntax to find MAX on a particular date > Hi, > > Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data table in > excess of 65k rows. A three column sample below: > Date Simple Date Outdoor Temp > 2022-01-01T00:05:00+02:00 2022-01-01 00:05:00 17.5 > 2022-01-01T00:00:00+02:00 2022-01-01 00:00:00 17.4 > > I have a secondary table, called Amended RAW Data containing just the 10 > digit date and the corresponding value of the column value I am seeking, > whether it be the Outdoor Temp, Daily Rainfall etc > > What I need to do is query the following table, searching for a specific > 10 digit date, and find the max value on a specific date. > > I have tried so many different itinerations on the VLOOKUP formula and > browsed many elementary Google VLOOKUP pages that I need help. I have > tried(all return N/A): > > =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62037,10),$'RAW > Data'.$B$2:$L$62037,1,FALSE)) > > =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11)) > > =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11)) > > =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62307,10)=$'Amended RAW > Data'.$A$11,$'RAW Data'.$B$2:$L$62037,11,0)) > > References to 'Amended RAW Data'.$A$11 refer to a date that I know has > greater than zero value, and the 11th column data I want to max, within > a date. > > Help appreciated > Hylton Not sure I understand you completely, but here is a test I did using maxifs. In cells A5-A8 Date 2022-01-01T00:05:00+02:00 2022-01-01T00:00:00+02:00 2022-01-02T00:00:00+02:00 In cells B5-B8 Simple Date 01/01/2022 12:05 AM 01/01/2022 12:00 AM 01/02/2022 12:00 AM In cells C5-C8 Outdoor Temp 17.5 17.4 26 In cells E6-E7 01/01/22 01/02/22 In cells F6 and F7 =MAXIFS($C$6:$C$8,$B$6:$B$8,">="&$E6,$B$6:$B$8,"<"&($E6+1)) =MAXIFS($C$6:$C$8,$B$6:$B$8,">="&$E7,$B$6:$B$8,"<"&($E7+1)) Displayed values of 17.5 and 26 Using Maxifs to get the max of range with outdoor temp, but date needs to be >= date in E and less than that +1 (next day).. > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > 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/ > Privacy Policy: https://www.documentfoundation.org/privacy ++ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ ++ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] export data from Calc, import to Base
Hi Harvey, my answer is older and I have not checked, whether the methods still work, but perhaps you try it? https://listarchives.libreoffice.org/global/users/2011/msg02407.html Kind regards Regina Harvey Nimmo schrieb am 08.08.2022 um 21:16: Dear formum, I believe my objective should be rather simple, but I need to be pointed in the right direction. I have a column of text data (unique values) in Calc that I want to transfer to a Base table. The Base table has been set up with three columns, two of which I expect to remain empty after the data import. The one column of unique values will be the primary key. I thought that the best way to implement the data transfer would be to create a .csv file from the column of data, but I need help to find out how Calc supports this. Can anyone help? One idea I had, was to copy the data column (which contains more than 7000 values, incidentally) to a text file. Unfortunately, I do not understand enough about regular expressions to add two commas before each linefeed. Help there would be appreciated. (For completeness, the Base Table is a front end to a backend Mysql database, and I expect the import to be done using phpMyAdmin) Regards Harvey -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] export data from Calc, import to Base
On 9/8/22 05:16, Harvey Nimmo wrote: Dear formum, I believe my objective should be rather simple, but I need to be pointed in the right direction. I have a column of text data (unique values) in Calc that I want to transfer to a Base table. The Base table has been set up with three columns, two of which I expect to remain empty after the data import. The one column of unique values will be the primary key. I thought that the best way to implement the data transfer would be to create a .csv file from the column of data, but I need help to find out how Calc supports this. Can anyone help? One idea I had, was to copy the data column (which contains more than 7000 values, incidentally) to a text file. Unfortunately, I do not understand enough about regular expressions to add two commas before each linefeed. Help there would be appreciated. (For completeness, the Base Table is a front end to a backend Mysql database, and I expect the import to be done using phpMyAdmin) Regards Harvey Hi Harvey, In Calc the File->Save As option has a filter of filetypes for the output file, one of which is .csv. This will then save your spreadsheet as a csv file. When you import the csv into Base you may have to configure the type and format for each of the fields you are importing. regards, Steve -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] VLOOKUP Syntax to find MAX on a particular date
Hi, Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data table in excess of 65k rows. A three column sample below: DateSimple Date Outdoor Temp 2022-01-01T00:05:00+02:00 2022-01-01 00:05:00 17.5 2022-01-01T00:00:00+02:00 2022-01-01 00:00:00 17.4 I have a secondary table, called Amended RAW Data containing just the 10 digit date and the corresponding value of the column value I am seeking, whether it be the Outdoor Temp, Daily Rainfall etc What I need to do is query the following table, searching for a specific 10 digit date, and find the max value on a specific date. I have tried so many different itinerations on the VLOOKUP formula and browsed many elementary Google VLOOKUP pages that I need help. I have tried(all return N/A): =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62037,10),$'RAW Data'.$B$2:$L$62037,1,FALSE)) =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11)) =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11)) =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62307,10)=$'Amended RAW Data'.$A$11,$'RAW Data'.$B$2:$L$62037,11,0)) References to 'Amended RAW Data'.$A$11 refer to a date that I know has greater than zero value, and the 11th column data I want to max, within a date. Help appreciated Hylton -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] export data from Calc, import to Base
Dear formum, I believe my objective should be rather simple, but I need to be pointed in the right direction. I have a column of text data (unique values) in Calc that I want to transfer to a Base table. The Base table has been set up with three columns, two of which I expect to remain empty after the data import. The one column of unique values will be the primary key. I thought that the best way to implement the data transfer would be to create a .csv file from the column of data, but I need help to find out how Calc supports this. Can anyone help? One idea I had, was to copy the data column (which contains more than 7000 values, incidentally) to a text file. Unfortunately, I do not understand enough about regular expressions to add two commas before each linefeed. Help there would be appreciated. (For completeness, the Base Table is a front end to a backend Mysql database, and I expect the import to be done using phpMyAdmin) Regards Harvey -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] multiplying time
On Mon, 08 Aug 2022 15:55:16 +0100
Brian Barker wrote:
Hello Brian,
I'm not the OP, but
>This is not a perfectly defined problem, I'm afraid. I'm guessing
>that the pay rate is per hour: is that right?
{snipped for brevity}
thank you, Brian, for going into detail.
Bookmarked for future reference.
--
Regards _
/ ) "The blindingly obvious is never immediately apparent"
/ _)rad "Is it only me that has a working delete key?"
It's the age of destruction, in a world of corruption
Neuromancer - Billy Idol
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
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/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] multiplying time
If I understand what you are looking at.
With A1 having 8:45
B1 having A1/time(1,0,0)
B1 would have decimal equivalent of time in hours
C1 would be B1 * $15 (example rate)
C1 would be 131.25 (8.75 * 15)
That would work if 24 hours or less.
If values more than 24 hours would need to more
Example:
a6=days as whole number
a6=2
b6=2:00 (time(2,0,0))
c6=(a6+b6)/time(1,0,0)
d6=c6*15 (50 * 15) = 750
Another example:
a9 =date(0,0,2)+time(2,0,0) Displays 12/02/99 02:00am
b9 =(a9-date(0,0,0))/time(1,0,0) Displays 50
c9 =b9 * 15 Display 750
On 8 Aug 2022 at 9:51, bill wrote:
From:bill
Date sent:Mon, 8 Aug 2022 09:51:51 -0400
Send reply to:will...@techservsys.com
To:users@global.libreoffice.org
Subject:[libreoffice-users] multiplying time
> On a spreadsheet I want to multiply time worked in hh:mm by pay
> rate (integer) and I can't find out how.
>
> Help please.
>
> --
> Bill Drescher
> william {at} TechServSys {dot} com
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> 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/
> Privacy Policy: https://www.documentfoundation.org/privacy
++
Michael D. Setzer II - Computer Science Instructor (Retired)
mailto:mi...@guam.net
mailto:msetze...@gmail.com
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
++
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
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/
Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] multiplying time
On a spreadsheet I want to multiply time worked in hh:mm by pay
rate (integer) and I can't find out how.
Help please.
--
Bill Drescher
william {at} TechServSys {dot} com
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
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/
Privacy Policy: https://www.documentfoundation.org/privacy
