Re: help with a csv import script to searchable database
[email protected] (Andrew Solomon) Tue, 27 Jan 2026 22:11:15 +0000
| Newsgroups | perl.beginners |
|---|---|
| Message-ID | <CAN=twKvSu4oYFLaE0rH+ZT50Togo=3+ZskDojgG8ajCpwuoc6Q@mail.gmail.com> |
--0000000000009a3553064965e7b6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Rich, I can confirm that Perl Maven is a well written and up-to-date resource for learning Perl, and the article https://perlmaven.com/how-to-read-a-csv-file-using-perl is a good path to understanding the best approach to processing CSV. Definitely read it through to the end. With regard to the kinds of queries you'll be working on, here's an explanation of the best approach to turning the Timestamp column into DateTime objects https://chatgpt.com/share/69793681-6538-800c-a726-bb59b1cca6b1 and with DateTime objects you can order the rows https://metacpan.org/pod/DateTime#DateTime-%3Ecompare(-$dt1,-$dt2-),-DateTi= me-%3Ecompare_ignore_floating(-$dt1,-$dt2-) I hope you find these resources helpful. Kind regards, Andrew On Tue, Jan 27, 2026 at 9:52=E2=80=AFPM Gomes, Rich via beginners < [email protected]> wrote: > I am working on a ongoing project with email where I will need to import > daily csv files into perl and create a searchable database of all the fil= es. > > Here is some example data: > > Header: > (Some of these fields\columns may or may not be removed in future csv=E2= =80=99s, > but this is what we have for now) > > Timestamp,SenderFromDomain,SenderFromAddress,DMARC,RecipientEmailAddress,= Subject,SenderIPv4,Connectors,DeliveryAction,EmailActionPolicy,OrgLevelActi= on,OrgLevelPolicy,UserLevelAction,UserLevelPolicy,AuthenticationDetails,Con= text,ReportId,SenderObjectId > > > Example rows: > > "Jan 27, 2026 3:30:56 PM",domain.com,[email protected],pass, > [email protected],Thank you for your > application,20.1.130.13,,Delivered,,Allow,Connection > policy,,,"{""SPF"":""pass"",""DKIM"":""pass"",""DMARC"":""pass"",""CompAu= th"":""pass""}",,4647d63d-1f9d-4982-6c39-08de5de2f778-18193297287602271192-= 1,1d3478ee-351f-4ee9-b6ec-7b03ee68e334 > > "Jan 27, 2026 3:33:04 PM", domain.ar,notifica@ domain.ar,pass, > [email protected],Env=C3=ADo de Orden de Compra Aramark Nro. > 115615,149.72.150.13,,Delivered,,,,,,"{""SPF"":""pass"",""DKIM"":""pass""= ,""DMARC"":""pass"",""CompAuth"":""pass""}",,976717e0-23ac-4538-a058-08de5d= e33a88-6451908357547151849-1, > > "Jan 27, 2026 3:31:29 PM", domain.com,paradox@ domain.com,pass, > [email protected],Please confirm your interview with HR > Reps,159.183.2.108,,Delivered,,,,,,"{""SPF"":""pass"",""DKIM"":""pass"","= "DMARC"":""pass"",""CompAuth"":""pass""}",,f8d7f41e-fb08-491c-43f4-08de5de3= 0c16-11061410221252786783-1,5767d814-45d6-4a03-bb3b-434692b8edc3 > > > > > My initial question is: > > Since the data will stay for some time (at least a year), is a database > the best to import the data =E2=80=9Cinto=E2=80=9D? Or would an array be = a better approach? > > Some of the queries I expect to perfom are: > > =E2=80=9CShow me the last time that a specific value in SenderFromAddress= had a > Connector value of =E2=80=9Cempty=E2=80=9D=E2=80=9D > > =E2=80=9CShow me the last time that SenderFromAddress had a OrgLevelPolic= y value > of =E2=80=9Cxyz=E2=80=9D=E2=80=9D > > Things like that. Basically query any combinations of fields > > > > Also, since all the files are in the same format, how do you =E2=80=9Cign= ore=E2=80=9D the > header after the =E2=80=9Cfirst import=E2=80=9D? > > > Also, there is a potential for some overlap in data, albeit small (I am > pulling this data from a KQL query in O365), is there a =E2=80=9Croutine= =E2=80=9D I can run > against the data to detect and remove any duplicate data. > I would like to learn how to do this both during the import and also run > it against existing data. That may seem =E2=80=9Cextra=E2=80=9D but this = is all about me > learning how to do each of these things > > Is this a good starting place for what I am looking to do:? > > *How to read a CSV file using Perl?* > <https://perlmaven.com/how-to-read-a-csv-file-using-perl> > > > Thank you, > > Rich > > > > > --0000000000009a3553064965e7b6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div dir=3D"ltr">Hi=C2=A0Rich,<div><br></div><div>I can co= nfirm that Perl Maven is a well written and up-to-date resource for learnin= g Perl, and the article=C2=A0<a href=3D"https://perlmaven.com/how-to-read-a= -csv-file-using-perl">https://perlmaven.com/how-to-read-a-csv-file-using-pe= rl</a> is a good path to understanding the best approach to processing CSV.= Definitely=C2=A0read it through to the end.</div><div><br></div><div>With = regard to the kinds of queries you'll be working on, here's an expl= anation of the best approach to turning the Timestamp column into DateTime = objects</div><div><br></div><div><a href=3D"https://chatgpt.com/share/69793= 681-6538-800c-a726-bb59b1cca6b1">https://chatgpt.com/share/69793681-6538-80= 0c-a726-bb59b1cca6b1</a></div><div><br></div><div>and with DateTime objects= you can order the rows</div><div><br></div><div><a href=3D"https://metacpa= n.org/pod/DateTime#DateTime-%3Ecompare(-$dt1,-$dt2-),-DateTime-%3Ecompare_i= gnore_floating(-$dt1,-$dt2-)">https://metacpan.org/pod/DateTime#DateTime-%3= Ecompare(-$dt1,-$dt2-),-DateTime-%3Ecompare_ignore_floating(-$dt1,-$dt2-)</= a></div><div><br></div><div>I hope you find these resources helpful.</div><= div><br></div><div>Kind regards,</div><div><br></div><div>Andrew</div></div= ><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" clas= s=3D"gmail_attr">On Tue, Jan 27, 2026 at 9:52=E2=80=AFPM Gomes, Rich via be= ginners <<a href=3D"mailto:[email protected]">[email protected]</a>>= ; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px= 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div cl= ass=3D"msg-7891831325091789137"> <div> <font face=3D"Aptos" size=3D"2"><span style=3D"font-size:11pt"> <div>I am working on a ongoing project with email where I will need to impo= rt daily csv files into perl and create a searchable database of all the fi= les.</div> <div>=C2=A0</div> <div>Here is some example data:</div> <div>=C2=A0</div> <div>Header:</div> <div>(Some of these fields\columns may or may not be removed in future csv= =E2=80=99s, but this is what we have for now)</div> <div>Timestamp,SenderFromDomain,SenderFromAddress,DMARC,RecipientEmailAddre= ss,Subject,SenderIPv4,Connectors,DeliveryAction,EmailActionPolicy,OrgLevelA= ction,OrgLevelPolicy,UserLevelAction,UserLevelPolicy,AuthenticationDetails,= Context,ReportId,SenderObjectId</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>Example rows:</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>"Jan 27, 2026 3:30:56 PM",<a href=3D"http://domain.com" targ= et=3D"_blank">domain.com</a>,<a href=3D"mailto:[email protected]" target= =3D"_blank">[email protected]</a>,pass,<a href=3D"mailto:[email protected]= om" target=3D"_blank">[email protected]</a>,Thank you for your applicatio= n,20.1.130.13,,Delivered,,Allow,Connection policy,,,"{""SPF&= quot;":""pass"",""DKIM"":"= ;"pass"",""DMARC"":""pass"= ;",""CompAuth"":""pass""}"= ;,,4647d63d-1f9d-4982-6c39-08de5de2f778-18193297287602271192-1,1d3478ee-351= f-4ee9-b6ec-7b03ee68e334</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>"Jan 27, 2026 3:33:04 PM", <a href=3D"http://domain.ar" targ= et=3D"_blank">domain.ar</a>,notifica@ <a href=3D"http://domain.ar" target= =3D"_blank">domain.ar</a>,pass,<a href=3D"mailto:[email protected]" targe= t=3D"_blank">[email protected]</a>,Env=C3=ADo de Orden de Compra Aramark = Nro. 115615,149.72.150.13,,Delivered,,,,,,"{""SPF""= ;:""pass"",""DKIM"":""pas= s"",""DMARC"":""pass"",&q= uot;"CompAuth"":""pass""}",,976717e= 0-23ac-4538-a058-08de5de33a88-6451908357547151849-1,</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>"Jan 27, 2026 3:31:29 PM", <a href=3D"http://domain.com" tar= get=3D"_blank">domain.com</a>,paradox@ <a href=3D"http://domain.com" target= =3D"_blank">domain.com</a>,pass,<a href=3D"mailto:[email protected]" target= =3D"_blank">[email protected]</a>,Please confirm your interview with HR Rep= s,159.183.2.108,,Delivered,,,,,,"{""SPF"":"&q= uot;pass"",""DKIM"":""pass"&qu= ot;,""DMARC"":""pass"",""= CompAuth"":""pass""}",,f8d7f41e-fb08-491= c-43f4-08de5de30c16-11061410221252786783-1,5767d814-45d6-4a03-bb3b-434692b8= edc3</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>My initial question is:</div> <div>=C2=A0</div> <div>Since the data will stay for some time (at least a year), is a databas= e the best to import the data =E2=80=9Cinto=E2=80=9D? Or would an array be = a better approach?</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>Some of the queries I expect to perfom are:</div> <div>=C2=A0</div> <div>=E2=80=9CShow me the last time that a specific value in SenderFromAddr= ess had a Connector value of =E2=80=9Cempty=E2=80=9D=E2=80=9D</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>=E2=80=9CShow me the last time that SenderFromAddress had a OrgLevelPo= licy value of =E2=80=9Cxyz=E2=80=9D=E2=80=9D</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>Things like that. Basically query any combinations of fields</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>Also, since all the files are in the same format, how do you =E2=80=9C= ignore=E2=80=9D the header after the =E2=80=9Cfirst import=E2=80=9D?</div> <div>=C2=A0</div> <div>=C2=A0</div> <div>Also, there is a potential for some overlap in data, albeit small (I a= m pulling this data from a KQL query in O365), is there a =E2=80=9Croutine= =E2=80=9D I can run against the data to detect and remove any duplicate dat= a. </div> <div>I would like to learn how to do this both during the import and also r= un it against existing data. That may seem =E2=80=9Cextra=E2=80=9D but this= is all about me learning how to do each of these things</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>Is this a good starting place for what I am looking to do:<font size= =3D"3"><span style=3D"font-size:12pt">?</span></font></div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt"><a href=3D"https://per= lmaven.com/how-to-read-a-csv-file-using-perl" target=3D"_blank"><font size= =3D"2" color=3D"#467886"><span style=3D"font-size:11pt"><u>How to read a CS= V file using Perl?</u></span></font></a></span></font></div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div>Thank you,</div> <div>=C2=A0</div> <div>Rich</div> <div>=C2=A0</div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> <div><font size=3D"3"><span style=3D"font-size:12pt">=C2=A0</span></font></= div> </span></font> </div> </div></blockquote></div><div><br clear=3D"all"></div><div><br></div><div d= ir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div></div></div></di= v></div> --0000000000009a3553064965e7b6--