help with a csv import script to searchable database
[email protected] ("Gomes, Rich" via beginners) Tue, 27 Jan 2026 21:52:06 +0000
| Newsgroups | perl.beginners |
|---|---|
| Message-ID | <SA1PR01MB994265A4C774D2DA83A05BE0F39C90A@SA1PR01MB994265.prod.exchangelabs.com> |
--_000_SA1PR01MB994265A4C774D2DA83A05BE0F39C90ASA1PR01MB994265_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am working on a ongoing project with email where I will need to import da= ily csv files into perl and create a searchable database of all the files. Here is some example data: Header: (Some of these fields\columns may or may not be removed in future csv's, bu= t this is what we have for now) Timestamp,SenderFromDomain,SenderFromAddress,DMARC,RecipientEmailAddress,Su= bject,SenderIPv4,Connectors,DeliveryAction,EmailActionPolicy,OrgLevelAction= ,OrgLevelPolicy,UserLevelAction,UserLevelPolicy,AuthenticationDetails,Conte= xt,ReportId,SenderObjectId Example rows: "Jan 27, 2026 3:30:56 PM",domain.com,[email protected],pass,billybob@domai= n.com,Thank you for your application,20.1.130.13,,Delivered,,Allow,Connecti= on policy,,,"{""SPF"":""pass"",""DKIM"":""pass"",""DMARC"":""pass"",""CompA= uth"":""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,rocio@domain.= com.ar,Env=EDo de Orden de Compra Aramark Nro. 115615,149.72.150.13,,Delive= red,,,,,,"{""SPF"":""pass"",""DKIM"":""pass"",""DMARC"":""pass"",""CompAuth= "":""pass""}",,976717e0-23ac-4538-a058-08de5de33a88-6451908357547151849-1, "Jan 27, 2026 3:31:29 PM", domain.com,paradox@ domain.com,pass,melvin@domai= n.com,Please confirm your interview with HR Reps,159.183.2.108,,Delivered,,= ,,,,"{""SPF"":""pass"",""DKIM"":""pass"",""DMARC"":""pass"",""CompAuth"":""= pass""}",,f8d7f41e-fb08-491c-43f4-08de5de30c16-11061410221252786783-1,5767d= 814-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 "into"? Or would an array be a better approach? Some of the queries I expect to perfom are: "Show me the last time that a specific value in SenderFromAddress had a Con= nector value of "empty"" "Show me the last time that SenderFromAddress had a OrgLevelPolicy value of= "xyz"" Things like that. Basically query any combinations of fields Also, since all the files are in the same format, how do you "ignore" the h= eader after the "first import"? Also, there is a potential for some overlap in data, albeit small (I am pul= ling this data from a KQL query in O365), is there a "routine" I can run ag= ainst 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 "extra" but this is all about me lear= ning 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 --_000_SA1PR01MB994265A4C774D2DA83A05BE0F39C90ASA1PR01MB994265_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <html> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-= 1"> <meta name=3D"Generator" content=3D"Microsoft Exchange Server"> <!-- converted from rtf --> <style><!-- .EmailQuote { margin-left: 1pt; padding-left: 4pt; border-left:= #800000 2px solid; } --></style> </head> <body> <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> </div> <div>Here is some example data:</div> <div> </div> <div>Header:</div> <div>(Some of these fields\columns may or may not be removed in future csv&= #8217;s, 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;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div>Example rows:</div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div>"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"":""p= ass"",""DKIM"":""pass"",&= quot;"DMARC"":""pass"",""CompA= uth"":""pass""}",,4647d63d-1f9d-4982-6c3= 9-08de5de2f778-18193297287602271192-1,1d3478ee-351f-4ee9-b6ec-7b03ee68e334<= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div>"Jan 27, 2026 3:33:04 PM", domain.ar,notifica@ domain.ar,pas= s,[email protected],Env=EDo de Orden de Compra Aramark Nro. 115615,149.72= .150.13,,Delivered,,,,,,"{""SPF"":""pass= "",""DKIM"":""pass"",&quo= t;"DMARC"":""pass"",""CompAuth= "":""pass""}",,976717e0-23ac-4538-a058-0= 8de5de33a88-6451908357547151849-1,</div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div>"Jan 27, 2026 3:31:29 PM", domain.com,paradox@ domain.com,pa= ss,[email protected],Please confirm your interview with HR Reps,159.183.2.1= 08,,Delivered,,,,,,"{""SPF"":""pass"= ;",""DKIM"":""pass"","&qu= ot;DMARC"":""pass"",""CompAuth"= ;":""pass""}",,f8d7f41e-fb08-491c-43f4-08de5d= e30c16-11061410221252786783-1,5767d814-45d6-4a03-bb3b-434692b8edc3</div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div>My initial question is:</div> <div> </div> <div>Since the data will stay for some time (at least a year), is a databas= e the best to import the data “into”? Or would an array be a be= tter approach?</div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div>Some of the queries I expect to perfom are:</div> <div> </div> <div>“Show me the last time that a specific value in SenderFromAddres= s had a Connector value of “empty””</div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div>“Show me the last time that SenderFromAddress had a OrgLevelPoli= cy value of “xyz””</div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </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;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div>Also, since all the files are in the same format, how do you “ig= nore” the header after the “first import”?</div> <div> </div> <div> </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 “routine= 221; I can run against the data to detect and remove any duplicate data. </= 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 “extra” 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;"> </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;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"><a href=3D"https://pe= rlmaven.com/how-to-read-a-csv-file-using-perl"><font size=3D"2" color=3D"#4= 67886"><span style=3D"font-size:11pt;"><u>How to read a CSV file using Perl= ?</u></span></font></a></span></font></div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div>Thank you,</div> <div> </div> <div>Rich</div> <div> </div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> <div><font size=3D"3"><span style=3D"font-size:12pt;"> </span></font><= /div> </span></font> </body> </html> --_000_SA1PR01MB994265A4C774D2DA83A05BE0F39C90ASA1PR01MB994265_--