How to find the details from the csv file in a new field for further processing using lookup ?

[Real_captain]

Hi 

Can someone tell me how we can use a csv file using a lookup and extract the details from a file in a field which we can use for further calculations. 

Example: A csv file (dummy.csv) with the below details are saved in Splunk and we need to extract the details present in the file after the date in a new field in SPlunk and use the new field for further calculations. 

Data in the dummy.csv file : 

"Monday,01/07/2024",T2S Live Timing,"[OTHER] BILL invoice for CSDs Billing period 10-30 June ",,,,,,
"Tuesday,02/07/2024",, ,,,,,,
"Wednesday,03/07/2024",,"[OTHER] BILL invoice for NCBs Billing period 10-30 June",,,,,,
"Thursday,04/07/2024",, ,[OTHER] DKK Service window between 19.35 - 23.59 ,,,,,
"Friday,05/07/2024",T2S Synchronised Release day,,,,,,,
"Saturday,06/07/2024",,[4CB] T2-T2S Site Recovery (internal technical test) ,[4CB] T2-T2S Site Recovery (internal technical test) ,,,,,
"Sunday,07/07/2024",,[4CB] T2-T2S Site Recovery (internal technical test) ,[4CB] T2-T2S Site Recovery (internal technical test) ,,,,,
"Monday,08/07/2024",T2S Live Timing, ,,,,,,

How we can use the lookup and eval command to find the data present in the above file after the date ?? 

Example : 
Date = 01/07/2024  Output = T2S Live Timing

Date = 02/07/2024  Output = Blank Space 

Date = 03/07/2024  Output = Blank Space 

Date = 04/07/2024  Output = Blank Space 

Date = 05/07/2024  Output = T2S Synchronised Release day

[bowesmana · Accepted solution]

Aside from the comments from @richgalloway in that you need a header, if you are just looking to ingest those rows and do something with that info, then using 

| inputlookup dummy.csv

will input the rows into your pipeline.

As you have 3+ columns in the data, first contains DayOfWeek,DD/MM/YYYY because the entire entry is quoted, you will have to extract the date if you need it, using the rex command. The second column will automatically have the field name defined by the header.

Unless you have a header, it will lose the first line. 

[richgalloway]

The CSV file needs to have a header row that labels the fields in the file ("Date", "Field2", "Field3", etc.).  Then a query can use the lookup command to find a specific value in the CSV file and return the matching fields.

... | lookup dummy.csv Date ``` Returns all fields by default ```

One limiting factor is lookups are exact matches.  The Date field would need to include the day of the week to match the CSV file. 

You can set up a lookup definition (Settings->Lookups->Lookup definitions) that references the dummy.csv file, but also allows wildcard searching of the lookup table.  Go to the Advanced options, and put "WILDCARD(Date)" in the "Match type" box.  Then, it's a matter of putting wildcards ('*') in the CSV file's Date field in place of the day name.