Dual Format Data Extraction Script
Budget: $30 – $250 USD
I'm seeking a highly skilled freelancer to develop a comprehensive Powershell script capable of parsing both CSV and Excel file formats. This script is a crucial component of our data processing tasks, aimed at effectively managing and extracting specific data points from our datasets.
**Requirements:**
- The script must accommodate files containing two header rows plus a unique "header" column that precedes the data columns.
- It should efficiently extract data, ensuring to handle the dual nature of the headers and the "header" column seamlessly across both CSV and Excel formats.
- The goal is to parse these files to extract specific columns of data, entire rows based on certain conditions, and identify unique entries without duplicates—despite the complexity introduced by the dual headers and "header" column.
- The final output should be structured and easily interpretable, to facilitate further analysis or processing tasks.
**Ideal Skills and Experience:**
- Proven proficiency in Powershell scripting, particularly in data manipulation tasks.
- Demonstrated experience with handling and parsing both CSV and Excel file formats in Powershell.
- Strong problem-solving skills, especially in dealing with complex file structures and data extraction requirements.
- Attention to detail and accuracy, to ensure the integrity of extracted data.
This project is integral to improving our data analysis and reporting capabilities. Hence, I am looking for someone who is not just technically capable but also communicative and committed to delivering a solution that fits our specific needs.
Examples of a before and after
Before
,High,Medium,Low,Medium,Medium,High (This is severity)
,v-12345,v-12346,v-12347,v-12348,v-12349,v-12350 (this is Vulnerability ID)
192.168.0.1,Open,Not A Finding,Not Applicable,Open,Not Reviewed,Open
192.168.0.2,Open,Open,Open,Open,Not A Finding,Not Applicable
192.168.0.3,Open,Not A Finding,Not Applicable,Open,Not Reviewed,Open
192.168.0.4,Open,Open,Open,Open,Not A Finding,Not Applicable
192.168.0.5,Open,Not A Finding,Not Applicable,Open,Not Reviewed,Open
192.168.0.6,Open,Open,Open,Open,Not A Finding,Not Applicable
192.168.0.7,Open,Not A Finding,Not Applicable,Open,Not Reviewed,Open
192.168.0.8,Open,Open,Open,Open,Not A Finding,Not Applicable
(^ is the IP address "header column") (Everything else is status)
After (not entire data set but its really lengthy to transpose each dataset)
IP Address,Severity,VulnerabilityID,Status
192.168.0.1,High,v-12345,Open
192.168.0.1,Medium,v-12346,Not A Finding
192.168.0.1,Low,v-12347,Not Applicable
192.168.0.1,Medium,v-12348,Open
192.168.0.1,Medium,v-12349,Not Reviewed
192.168.0.1,High,v-12350,Open
192.168.0.2,High,v-12345,Open
192.168.0.2,Medium,v-12346,Open
192.168.0.2,Low,v-12347,Open
192.168.0.2,Medium,v-12348,Open
192.168.0.2,Medium,v-12349,Not A Finding
192.168.0.2,High,v-12350,Not Applicable
Everything else
It does not have to be .xlsx as I can just convert it to a csv before I parse it as the examples above are already comma delimited
**Requirements:**
- The script must accommodate files containing two header rows plus a unique "header" column that precedes the data columns.
- It should efficiently extract data, ensuring to handle the dual nature of the headers and the "header" column seamlessly across both CSV and Excel formats.
- The goal is to parse these files to extract specific columns of data, entire rows based on certain conditions, and identify unique entries without duplicates—despite the complexity introduced by the dual headers and "header" column.
- The final output should be structured and easily interpretable, to facilitate further analysis or processing tasks.
**Ideal Skills and Experience:**
- Proven proficiency in Powershell scripting, particularly in data manipulation tasks.
- Demonstrated experience with handling and parsing both CSV and Excel file formats in Powershell.
- Strong problem-solving skills, especially in dealing with complex file structures and data extraction requirements.
- Attention to detail and accuracy, to ensure the integrity of extracted data.
This project is integral to improving our data analysis and reporting capabilities. Hence, I am looking for someone who is not just technically capable but also communicative and committed to delivering a solution that fits our specific needs.
Examples of a before and after
Before
,High,Medium,Low,Medium,Medium,High (This is severity)
,v-12345,v-12346,v-12347,v-12348,v-12349,v-12350 (this is Vulnerability ID)
192.168.0.1,Open,Not A Finding,Not Applicable,Open,Not Reviewed,Open
192.168.0.2,Open,Open,Open,Open,Not A Finding,Not Applicable
192.168.0.3,Open,Not A Finding,Not Applicable,Open,Not Reviewed,Open
192.168.0.4,Open,Open,Open,Open,Not A Finding,Not Applicable
192.168.0.5,Open,Not A Finding,Not Applicable,Open,Not Reviewed,Open
192.168.0.6,Open,Open,Open,Open,Not A Finding,Not Applicable
192.168.0.7,Open,Not A Finding,Not Applicable,Open,Not Reviewed,Open
192.168.0.8,Open,Open,Open,Open,Not A Finding,Not Applicable
(^ is the IP address "header column") (Everything else is status)
After (not entire data set but its really lengthy to transpose each dataset)
IP Address,Severity,VulnerabilityID,Status
192.168.0.1,High,v-12345,Open
192.168.0.1,Medium,v-12346,Not A Finding
192.168.0.1,Low,v-12347,Not Applicable
192.168.0.1,Medium,v-12348,Open
192.168.0.1,Medium,v-12349,Not Reviewed
192.168.0.1,High,v-12350,Open
192.168.0.2,High,v-12345,Open
192.168.0.2,Medium,v-12346,Open
192.168.0.2,Low,v-12347,Open
192.168.0.2,Medium,v-12348,Open
192.168.0.2,Medium,v-12349,Not A Finding
192.168.0.2,High,v-12350,Not Applicable
Everything else
It does not have to be .xlsx as I can just convert it to a csv before I parse it as the examples above are already comma delimited