Re: help with a csv import script to searchable database
[email protected] (Mike) Sun, 8 Feb 2026 08:23:55 -0600
| Newsgroups | perl.beginners |
|---|---|
| Message-ID | <[email protected]> |
--------------GogPHU41Bw8WH8nS3agRorJE
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: base64
DQoxKSBBIGZsYXQgZmlsZSBEQiAoYXJyYXkpIGlzIHByb2JhYmx5IHRoZSB3YXkgdG8gZ28N
CmluIHlvdXIgY2FzZS4NCg0KMikgVG8gaWdub3JlIHRoZSBoZWFkZXIgeW91IHNpbXBseSB3
cml0ZSB0aGF0IGluIHlvdXINClBlcmwgY29kZS7CoCBJZiB0aGUgaGVhZGVyIGlzIHN0YXRp
YywganVzdCB3cml0ZSBpdCBpbg0KYW5kIGlnbm9yZSBhbGwgaW1wb3J0IGhlYWRlcnMuDQoN
CjMpIFVzZSBhIGhhc2ggdG8gcmVtb3ZlIEVYQUNUIGRhdGEgbWF0Y2hlcy7CoCBUaGF0IGlz
IGVhc3kuDQpJdCBnZXRzIG11Y2ggaGFyZGVyIGlmIHlvdSB3YW50IHRvIHJlbW92ZSBTSU1J
TEFSLCBidXQgbm90DQpleGFjdCBkYXRhLg0KDQpUbyBrZWVwIHRoZSBQZXJsIGNvZGUgc2lt
cGxlLCB5b3UgbWlnaHQgd2FudCB0byB1c2UgZmlsdGVyaW5nDQppbiBMaWJyZU9mZmljZSBD
YWxjIGZvciB5b3VyIHF1ZXJpZXMuDQoNCg0KTWlrZQ0KDQoNCk9uIDEvMjcvMjYgMTU6NTIs
IEdvbWVzLCBSaWNoIHZpYSBiZWdpbm5lcnMgd3JvdGU6DQo+IEkgYW0gd29ya2luZyBvbiBh
IG9uZ29pbmcgcHJvamVjdCB3aXRoIGVtYWlsIHdoZXJlIEkgd2lsbCBuZWVkIHRvIA0KPiBp
bXBvcnQgZGFpbHkgY3N2IGZpbGVzIGludG8gcGVybCBhbmQgY3JlYXRlIGEgc2VhcmNoYWJs
ZSBkYXRhYmFzZSBvZiANCj4gYWxsIHRoZSBmaWxlcy4NCj4gSGVyZSBpcyBzb21lIGV4YW1w
bGUgZGF0YToNCj4gSGVhZGVyOg0KPiAoU29tZSBvZiB0aGVzZSBmaWVsZHNcY29sdW1ucyBt
YXkgb3IgbWF5IG5vdCBiZSByZW1vdmVkIGluIGZ1dHVyZSANCj4gY3N24oCZcywgYnV0IHRo
aXMgaXMgd2hhdCB3ZSBoYXZlIGZvciBub3cpDQo+IFRpbWVzdGFtcCxTZW5kZXJGcm9tRG9t
YWluLFNlbmRlckZyb21BZGRyZXNzLERNQVJDLFJlY2lwaWVudEVtYWlsQWRkcmVzcyxTdWJq
ZWN0LFNlbmRlcklQdjQsQ29ubmVjdG9ycyxEZWxpdmVyeUFjdGlvbixFbWFpbEFjdGlvblBv
bGljeSxPcmdMZXZlbEFjdGlvbixPcmdMZXZlbFBvbGljeSxVc2VyTGV2ZWxBY3Rpb24sVXNl
ckxldmVsUG9saWN5LEF1dGhlbnRpY2F0aW9uRGV0YWlscyxDb250ZXh0LFJlcG9ydElkLFNl
bmRlck9iamVjdElkDQo+IEV4YW1wbGUgcm93czoNCj4gIkphbiAyNywgMjAyNiAzOjMwOjU2
IA0KPiBQTSIsZG9tYWluLmNvbSxub3JlcGx5QGRvbWFpbi5jb20scGFzcyxiaWxseWJvYkBk
b21haW4uY29tLFRoYW5rIHlvdSANCj4gZm9yIHlvdXIgYXBwbGljYXRpb24sMjAuMS4xMzAu
MTMsLERlbGl2ZXJlZCwsQWxsb3csQ29ubmVjdGlvbiANCj4gcG9saWN5LCwsInsiIlNQRiIi
OiIicGFzcyIiLCIiREtJTSIiOiIicGFzcyIiLCIiRE1BUkMiIjoiInBhc3MiIiwiIkNvbXBB
dXRoIiI6IiJwYXNzIiJ9IiwsNDY0N2Q2M2QtMWY5ZC00OTgyLTZjMzktMDhkZTVkZTJmNzc4
LTE4MTkzMjk3Mjg3NjAyMjcxMTkyLTEsMWQzNDc4ZWUtMzUxZi00ZWU5LWI2ZWMtN2IwM2Vl
NjhlMzM0DQo+ICJKYW4gMjcsIDIwMjYgMzozMzowNCBQTSIsIGRvbWFpbi5hcixub3RpZmlj
YUAgDQo+IGRvbWFpbi5hcixwYXNzLHJvY2lvQGRvbWFpbi5jb20uYXIsRW52w61vIGRlIE9y
ZGVuIGRlIENvbXByYSBBcmFtYXJrIA0KPiBOcm8uIA0KPiAxMTU2MTUsMTQ5LjcyLjE1MC4x
MywsRGVsaXZlcmVkLCwsLCwsInsiIlNQRiIiOiIicGFzcyIiLCIiREtJTSIiOiIicGFzcyIi
LCIiRE1BUkMiIjoiInBhc3MiIiwiIkNvbXBBdXRoIiI6IiJwYXNzIiJ9IiwsOTc2NzE3ZTAt
MjNhYy00NTM4LWEwNTgtMDhkZTVkZTMzYTg4LTY0NTE5MDgzNTc1NDcxNTE4NDktMSwNCj4g
IkphbiAyNywgMjAyNiAzOjMxOjI5IFBNIiwgZG9tYWluLmNvbSxwYXJhZG94QCANCj4gZG9t
YWluLmNvbSxwYXNzLG1lbHZpbkBkb21haW4uY29tLFBsZWFzZSBjb25maXJtIHlvdXIgaW50
ZXJ2aWV3IHdpdGggDQo+IEhSIA0KPiBSZXBzLDE1OS4xODMuMi4xMDgsLERlbGl2ZXJlZCws
LCwsLCJ7IiJTUEYiIjoiInBhc3MiIiwiIkRLSU0iIjoiInBhc3MiIiwiIkRNQVJDIiI6IiJw
YXNzIiIsIiJDb21wQXV0aCIiOiIicGFzcyIifSIsLGY4ZDdmNDFlLWZiMDgtNDkxYy00M2Y0
LTA4ZGU1ZGUzMGMxNi0xMTA2MTQxMDIyMTI1Mjc4Njc4My0xLDU3NjdkODE0LTQ1ZDYtNGEw
My1iYjNiLTQzNDY5MmI4ZWRjMw0KPiBNeSBpbml0aWFsIHF1ZXN0aW9uIGlzOg0KPiBTaW5j
ZSB0aGUgZGF0YSB3aWxsIHN0YXkgZm9yIHNvbWUgdGltZSAoYXQgbGVhc3QgYSB5ZWFyKSwg
aXMgYSANCj4gZGF0YWJhc2UgdGhlIGJlc3QgdG8gaW1wb3J0IHRoZSBkYXRhIOKAnGludG/i
gJ0/IE9yIHdvdWxkIGFuIGFycmF5IGJlIGEgDQo+IGJldHRlciBhcHByb2FjaD8NCj4gU29t
ZSBvZiB0aGUgcXVlcmllcyBJIGV4cGVjdCB0byBwZXJmb20gYXJlOg0KPiDigJxTaG93IG1l
IHRoZSBsYXN0IHRpbWUgdGhhdCBhIHNwZWNpZmljIHZhbHVlIGluIFNlbmRlckZyb21BZGRy
ZXNzIGhhZCANCj4gYSBDb25uZWN0b3IgdmFsdWUgb2Yg4oCcZW1wdHnigJ3igJ0NCj4g4oCc
U2hvdyBtZSB0aGUgbGFzdCB0aW1lIHRoYXQgU2VuZGVyRnJvbUFkZHJlc3MgaGFkIGEgT3Jn
TGV2ZWxQb2xpY3kgDQo+IHZhbHVlIG9mIOKAnHh5euKAneKAnQ0KPiBUaGluZ3MgbGlrZSB0
aGF0LiBCYXNpY2FsbHkgcXVlcnkgYW55IGNvbWJpbmF0aW9ucyBvZiBmaWVsZHMNCj4gQWxz
bywgc2luY2UgYWxsIHRoZSBmaWxlcyBhcmUgaW4gdGhlIHNhbWUgZm9ybWF0LCBob3cgZG8g
eW91IOKAnGlnbm9yZeKAnSANCj4gdGhlIGhlYWRlciBhZnRlciB0aGUg4oCcZmlyc3QgaW1w
b3J04oCdPw0KPiBBbHNvLCB0aGVyZSBpcyBhIHBvdGVudGlhbCBmb3Igc29tZSBvdmVybGFw
IGluIGRhdGEsIGFsYmVpdCBzbWFsbCAoSSANCj4gYW0gcHVsbGluZyB0aGlzIGRhdGEgZnJv
bSBhIEtRTCBxdWVyeSBpbiBPMzY1KSwgaXMgdGhlcmUgYSDigJxyb3V0aW5l4oCdIEkgDQo+
IGNhbiBydW4gYWdhaW5zdCB0aGUgZGF0YSB0byBkZXRlY3QgYW5kIHJlbW92ZSBhbnkgZHVw
bGljYXRlIGRhdGEuDQo+IEkgd291bGQgbGlrZSB0byBsZWFybiBob3cgdG8gZG8gdGhpcyBi
b3RoIGR1cmluZyB0aGUgaW1wb3J0IGFuZCBhbHNvIA0KPiBydW4gaXQgYWdhaW5zdCBleGlz
dGluZyBkYXRhLiBUaGF0IG1heSBzZWVtIOKAnGV4dHJh4oCdIGJ1dCB0aGlzIGlzIGFsbCAN
Cj4gYWJvdXQgbWUgbGVhcm5pbmcgaG93IHRvIGRvIGVhY2ggb2YgdGhlc2UgdGhpbmdzDQo+
IElzIHRoaXMgYSBnb29kIHN0YXJ0aW5nIHBsYWNlIGZvciB3aGF0IEkgYW0gbG9va2luZyB0
byBkbzo/DQo+IF9Ib3cgdG8gcmVhZCBhIENTViBmaWxlIHVzaW5nIFBlcmw/XyANCj4gPGh0
dHBzOi8vcGVybG1hdmVuLmNvbS9ob3ctdG8tcmVhZC1hLWNzdi1maWxlLXVzaW5nLXBlcmw+
DQo+IFRoYW5rIHlvdSwNCj4gUmljaA0KDQo=
--------------GogPHU41Bw8WH8nS3agRorJE
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
</head>
<body>
<br>
1) A flat file DB (array) is probably the way to go<br>
in your case.<br>
<br>
2) To ignore the header you simply write that in your=C2=A0<br>
Perl code.=C2=A0 If the header is static, just write it in=C2=A0<br>
and ignore all import headers.<br>
<br>
3) Use a hash to remove EXACT data matches.=C2=A0 That is easy.<br>
It gets much harder if you want to remove SIMILAR, but not=C2=A0<br>
exact data.<br>
<br>
To keep the Perl code simple, you might want to use filtering<br>
in LibreOffice Calc for your queries.<br>
<br>
<br>
Mike<br>
<br>
<br>
<div class=3D"moz-cite-prefix">On 1/27/26 15:52, Gomes, Rich via
beginners wrote:<br>
</div>
<blockquote type=3D"cite"
cite=3D"mid:[email protected]=
=2Eexchangelabs.com">
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DU=
TF-8">
<meta name=3D"Generator" content=3D"Microsoft Exchange Server">
<!-- converted from rtf -->
<style>.EmailQuote { margin-left: 1pt; padding-left: 4pt; border-le=
ft: #800000 2px solid; }</style>
<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 import daily csv files into perl and create a
searchable database of all the files.</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)</d=
iv>
<div>Timestamp,SenderFromDomain,SenderFromAddress,DMARC,Recipie=
ntEmailAddress,Subject,SenderIPv4,Connectors,DeliveryAction,EmailActionPo=
licy,OrgLevelAction,OrgLevelPolicy,UserLevelAction,UserLevelPolicy,Authen=
ticationDetails,Context,ReportId,SenderObjectId</div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div>Example rows:</div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div>"Jan 27, 2026 3:30:56
PM<a class=3D"moz-txt-link-rfc2396E" href=3D"mailto:,domain.c=
om,[email protected],pass,[email protected],Thankyouforyourapplication=
,20.1.130.13,,Delivered,,Allow,Connectionpolicy,,,">",domain.com,noreply@=
domain.com,pass,[email protected],Thank
you for your
application,20.1.130.13,,Delivered,,Allow,Connection
policy,,,"</a>{""SPF"":""pass"",""DKIM"":""pass"",""DMARC"":""pass"",""Co=
mpAuth"":""pass""}",,4647d63d-1f9d-4982-6c39-08de5de2f778-181932972876022=
71192-1,1d3478ee-351f-4ee9-b6ec-7b03ee68e334</div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div>"Jan 27, 2026 3:33:04 PM", domain.ar,notifica@
domain.ar,pass,<a class=3D"moz-txt-link-abbreviated" href=3D"=
mailto:[email protected],Env">[email protected],Env</a>=C3=ADo de Ord=
en de Compra
Aramark Nro.
115615,149.72.150.13,,Delivered,,,,,,"{""SPF"":""pass"",""DKIM"":""pass""=
,""DMARC"":""pass"",""CompAuth"":""pass""}",,976717e0-23ac-4538-a058-08de=
5de33a88-6451908357547151849-1,</div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div>"Jan 27, 2026 3:31:29 PM", domain.com,paradox@
domain.com,pass,<a class=3D"moz-txt-link-abbreviated" href=3D=
"mailto:[email protected],Please">[email protected],Please</a> confirm yo=
ur
interview with HR
Reps,159.183.2.108,,Delivered,,,,,,"{""SPF"":""pass"",""DKIM"":""pass"","=
"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;">=C2=A0</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></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 database 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</s=
pan></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
SenderFromAddress 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</s=
pan></font></div>
<div>=E2=80=9CShow me the last time that SenderFromAddress had =
a
OrgLevelPolicy 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</s=
pan></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</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div>Also, since all the files are in the same format, how do
you =E2=80=9Cignore=E2=80=9D the header after the =E2=80=9Cfi=
rst 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 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. </div>
<div>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</div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></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</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;"><a
href=3D"https://perlmaven.com/how-to-read-a-csv-file-using-perl"
moz-do-not-send=3D"true"><font size=3D"2" color=3D"#467=
886"><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;">=C2=A0</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></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</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
<div><font size=3D"3"><span style=3D"font-size:12pt;">=C2=A0</s=
pan></font></div>
</span></font>
</blockquote>
<br>
</body>
</html>
--------------GogPHU41Bw8WH8nS3agRorJE--