Note that regex is much easier in a text editor than in LO Calc. I do this kind
of thing as normal course of my activity: get the file to look like the .csv i
want with an editor first, then import it into LO Calc. Tab delimited is the
least error prone... (you can make a tab in most editors with regex with \t )
I use jEdit because it's ability to record the search/replace activity, then
allow you to edit and replay the recorded "macros" on other files helps me
work far far faster. But any text editor with regex functionality will
eventually have to moving faster than LO Calc.
But it's a steep learning curve.
On Sun, Mar 13, 2022 at 9:18 AM Rogier F. van Vlissingen
<vliscony@gmail.com> wrote:
Awesome, thanks!
A bit puzzling... I thought I exactly copied your previous
instructions,
but I got the idiot results you saw. Now I downloaded your
spreadsheet, and
indeed it came out better in your case, so there must be
some subtle
mistake some place. I will have to repeat the process and
figure that out.
Thanks so much.
On Sun, Mar 13, 2022 at 10:00 AM Michael D. Setzer II
<msetzerii@gmail.com>
wrote:
Not clear on how you got that break down with my
instructions I get A thru
AB and here I transposed them to show. PM2.5 is in
Column G not two.
Then had formula to convert date and time value. Moved
A
Date
B
20220302
03/02/22
"=DATE(LEFT(AH2,4),MID(AH2,5,2),RIGHT(AH2,2))
C
Time
D
1631
04:31:00 PM
"=TIME(INT(AH4/100),MOD(AH4,100),0)
E
PM1.0
F
0
G
PM2.5
H
0
I
PM10
J
0
K
CO2
L
10
M
HCHO
N
0.001
O
TVOC
P
0
Q
0.3DustNum
R
0
S
0.5DustNum
T
0
U
1.0DustNum
V
0
W
2.5DustNum
X
0
Y
5.0DustNum
Z
0
AA
10DustNum
AB
0
On 13 Mar 2022 at 9:30, Rogier F. van Vlissingen wrote:
From: "Rogier F. van Vlissingen"
<vliscony@gmail.com>
Date sent: Sun, 13 Mar 2022 09:30:49 -0400
Subject: Re: [libreoffice-users] Spreadsheet question
To: "Michael D. Setzer II"
<msetzerii@gmail.com>
Copies to: users@global.libreoffice.org
Funny complication.
One of the variable labels is PM2.5, but in this case that
became two
columns, H, and I respectively, like this:
H I
PM2 5
On Sun, Mar 13, 2022 at 9:27 AM Rogier F. van
Vlissingen <
vliscony@gmail.com>
wrote:
Awesome, thanks!
I replicated that, and now I can just name the
columns and the delete
the
preceding columns with the variable names and I am
in business.
One of these babies is 11775 rows ;-)
On Sun, Mar 13, 2022 at 9:18 AM Michael D. Setzer
II <
msetzerii@gmail.com>
wrote:
Loaded the csv file with libreoffice and click on
space and
other boxes and entered :.
That brings in columns A thru AB.
On 13 Mar 2022 at 9:05, Rogier F. van Vlissingen
wrote:
From: "Rogier F. van Vlissingen"
<vliscony@gmail.com>
Date sent: Sun, 13 Mar 2022 09:05:56 -0400
Subject: Re: [libreoffice-users]
Spreadsheet
question
To: David Lynch
<dlynch1319ng@gmail.com>
Copies to: users@global.libreoffice.org
wow... I had never used that function before...
I am not quite there yet, however, but at least
this is a path.
Here
is an
example of one of the strings
Date:20220302 Time:1631 PM1.0:000
PM2.5:000 PM10:000 CO2:0010
HCHO:0.001
TVOC:0.000 >0.3DustNum:00000
0.5DustNum:00000 >1.0DustNum:00000
2.5DustNum:00000 >5.0DustNum:00000
10DustNum:00000
Using the expression as you wrote it, I get
fields, like this:
Date 20220302 Time 1631 PM1.0 000 PM2.5
000 PM10 000 CO2 0010 HCHO
0.001
TVOC 0.000 >0.3DustNum 00000
0.5DustNum 00000 >1.0DustNum 00000
2.5DustNum 00000 >5.0DustNum 00000
10DustNum 00000 and then I
could
name the columns and delete the columns with
the variable names.
I will have to study that function... thanks.
On Sun, Mar 13, 2022 at 8:41 AM David Lynch
<
dlynch1319ng@gmail.com>
wrote:
On 13/03/2022 12:04, Rogier F. van
Vlissingen wrote:
Var1: value1 Var2: value2 Var3: value 3 ...
Var10: value10
Place the strings in column 1 than put
=REGEX($A1,"(?<=^| |:)[^ ]+?(?=:|
|$)",,COLUMN()-1)
in column 2 and drag right for ten columns
and down.
David Lynch
--
To unsubscribe e-mail to:
users+unsubscribe@global.libreoffice.org
Problems?
https://www.libreoffice.org/get-help/mailing-lists/how-to-uns
ubscribe/
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:mikes@guam.net
mailto:msetzerii@gmail.com
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+
The following section of this message contains a file
attachment
prepared for transmission using the Internet MIME
message format.
If you are using Pegasus Mail, or any other
MIME-compliant system,
you should be able to save it or view it from within
your mailer.
If you cannot, please ask your system administrator
for assistance.
---- File information -----------
File: BRSmart128S_0302-0313_2022_RFvV_BR.ods
Date: 13 Mar 2022, 23:16
Size: 675913 bytes.
Type: Libre-sheet
--
To unsubscribe e-mail to:
users+unsubscribe@global.libreoffice.org
Problems?
https://www.libreoffice.org/get-help/mailing-lists/how-to-uns
ubscribe/
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+unsubscribe@global.libreoffice.org
Problems?
https://www.libreoffice.org/get-help/mailing-lists/how-to-uns
ubscribe/
Posting guidelines + more:
https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.