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.
On 13 Mar 2022 at 11:03, Michael H wrote: From: Michael H <[email protected]> Date sent: Sun, 13 Mar 2022 11:03:32 -0500 Subject: Re: [libreoffice-users] Spreadsheet question To: "Rogier F. van Vlissingen" <[email protected]> Copies to: "Michael D. Setzer II" <[email protected]>, LibreOffice Användare <[email protected]> > > 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 > <[email protected]> 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 > <[email protected]> > 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" > <[email protected]> > > Date sent: Sun, 13 Mar 2022 09:30:49 -0400 > > Subject: Re: [libreoffice-users] Spreadsheet question > > To: "Michael D. Setzer II" > <[email protected]> > > Copies to: [email protected] > > > 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 < > > [email protected]> > > > 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 < > > [email protected]> > > > > 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" > > > >> <[email protected]> > > > >> Date sent: Sun, 13 Mar 2022 09:05:56 -0400 > > > >> Subject: Re: [libreoffice-users] > Spreadsheet > > > >> question > > > >> To: David Lynch > <[email protected]> > > > >> Copies to: [email protected] > > > >> > > > >> > 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 > < > > [email protected]> > > > >> > > 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: > [email protected] > > > >> > 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:[email protected] > > > >> mailto:[email protected] > > > >> 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: > [email protected] > > > > 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: > [email protected] > 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:[email protected] mailto:[email protected] Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +------------------------------------------------------------+ -- To unsubscribe e-mail to: [email protected] 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
