Spreadsheet question

users
#1

This is more of a generic question.

I am getting information uploaded from a microSD which is a text string of
about 10 variables.

I am at a loss how to convert this into spreadsheet data, with rows and
columns. I there a function to do this???

TXT file, and each string is:
Var1: value1 Var2: value2 Var3: value 3 ... Var10: value10.

#2

wow... I had never used that function before...

#3

Loaded the csv file with libreoffice and click on space and
other boxes and entered :.
That brings in columns A thru AB.

<vliscony@gmail.com>
Date sent: Sun, 13 Mar 2022 09:05:56 -0400
question
Copies to: users@global.libreoffice.org

#4

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 :wink:

#5

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

#6

The spreadsheet I sent gave columns A thru AB, but some
may not have separated exactly correct. Not knowing
exactly what changes would be needed.
Started by inserting a row at top.

To convert date to an actual date field. In ad1 put B.
In ad2 put this formula to make a date.
=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))
In ae1 put D
In ae2 put
=TIME(INT(D2/100),MOD(D2,100),0)

<vliscony@gmail.com>
Date sent:Sun, 13 Mar 2022 09:05:56 -0400
question
Copies to:users@global.libreoffice.org

#7

It gets worse for I get parsing problems with some of the data as well...
so this will require more thought... but we're on the right track.

#8

Yes, that the direction I have to go. Thanks so much. It gets really hairy
with some of the notations.

Like a variable for DustNum >0.3 (micron), that variable name became two
columns, ">0"0 and "3DustNum"

I will have to become a spreadsheet jockey (again) about 40 yrs later...

#9

=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

ADate

B20220302
03/02/22
\u201c=DATE(LEFT(AH2,4),MID(AH2,5,2),RIGHT(AH2,2))
CTime

D1631</div>04:31:00 PM</font>
\u201c=TIME(INT(AH4/100),MOD(AH4,100),0)
EPM1.0</div>

F0

GPM2.5</div>

H0

IPM10

J0

KCO2

L10

MHCHO

N0.001

OTVOC

P0

0.3DustNum

R0

0.5DustNum

T0

1.0DustNum

V0

2.5DustNum

X0

5.0DustNum

Z0

10DustNum

AB0

<vliscony@gmail.com>
Date sent:60;Sun, 13 Mar 2022 09:30:49 -0400</span>
question
<msetzerii@gmail.com>
Copies to:60;users@global.libreoffice.org

#10

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.

#11

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.

#12

Seem to be able to get it to load the data from the csv file.
Just select option to use two delimeters. Use the : for
headings and space for the data fields.
Then just have it use the odd columns for column
headers, and even columns for the data for those heading.
Also used a formula ot convert date and time to date and
time values.

Date sent: Sun, 13 Mar 2022 11:03:32 -0500
Copies to: "Michael D. Setzer II" <msetzerii@gmail.com>,
  LibreOffice Användare <users@global.libreoffice.org>