Extracting Data from a CSV - Looping for different data sets

Good Morning Experts,

I am looking for your help with a problem I currently have...

Everyday I have an CSV file that gets created and it contains several sets of data.

Each record has 3 headings - Item Type, Label & Response

The first records always starts under Item Type with Section and Label with Title

Example

ItemType      Label         Response
Section          Title

the record ends when you see the above again and then a another record begins etc... - see attachment with some example data

For each record I would like to extract the data under responses - only certain rows -

List - Client / Site / Job Number - Response (Data Required) - In CSV attachment Example - Data = "User Site 1 - Test Site - Job  Number: 1756"
textsingle - Order Number: - Response (Data Required) - In CSV attachment Example - Data = "HV_000003_992809"
datetime - Completed On: - Response (Data Required) - In CSV attachment Example - Data = "19/04/2018 13:51"
textsingle - Completed By: - Response (Data Required) - In CSV attachment Example - Data = "Nick Test"
signature - Certificate received on behalf of : (Full Name and Signature) - Response (Data Required) - In CSV attachment Example - Data = "Mr Jerry Tester"
textsingle - E-Mail Address: - - Response (Data Required) - In CSV attachment Example - Data ="Test@gmail.com"

For each record set I would like to extract the text as shown above

The final output put into a new text file - so each row is a set of data

Based on the CSV attachment the text file would be like as follows

"User Site 1 - Test Site - Job  Number: 1756", "HV_000003_992809", "19/04/2018 13:51", "Nick Test", "Mr Jerry Tester", "Test@gmail.com"

So a new line for a new set of data

I hope you understand what I am trying to achieve...

Thanks
Nick CollinsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Please post one of these CSV files
0
dtyerechCommented:
The file you are getting is from a source, can you configure the source to give you want you want?
0
Nick CollinsAuthor Commented:
I can't change the source
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Nick CollinsAuthor Commented:
File attached
Template1.csv
0
aikimarkCommented:
To help make sure I got the logic right, please run this Powershell script and see if the output is what you need.
You will need to point to your own path, not C:\Users\Mark\downloads\
You didn't show a CSV header line in your example output.  Does this mean that you don't want headers?
$TargetLabels = @("Client / Site / Job Number", "Order Number:", 
"Completed On:", "Completed By:", 
"Certificate received on behalf of : (Full Name and Signature)", "E-Mail Address:")

$a = Import-Csv  C:\Users\Mark\downloads\template1.csv

$a | % -begin{$rec=@(); $o=@{}} `
       -process{ `
        if ($_.label -eq "Title Page") {
            if ($o.count -ne 0) {
                $rec += new-object -TypeName psobject -Property $o
                $o=@{}
            }
        }
    if ($TargetLabels -contains $_.label){
        $o += @{$_.label=$_.response}
        }
}
#add the one we were constructing
$rec += new-object -TypeName psobject -Property $o
$rec | Export-Csv -Path C:\Users\Mark\downloads\Q_29095438.csv -NoTypeInformation

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick CollinsAuthor Commented:
Thank you.. that has worked perfectly.

On the output file - is it possible to put the YYYYMMDDhhmmss at the end of the file name
0
aikimarkCommented:
Do you want to go with a PS solution?

Yes.  It is possible to alter the name of the output file.
0
Nick CollinsAuthor Commented:
Yes powershell is good
0
aikimarkCommented:
$TargetLabels = @("Client / Site / Job Number", "Order Number:", 
"Completed On:", "Completed By:", 
"Certificate received on behalf of : (Full Name and Signature)", "E-Mail Address:")

$a = Import-Csv  C:\Users\Mark\downloads\template1.csv

$a | % -begin{$rec=@(); $o=@{}} `
       -process{ `
        if ($_.label -eq "Title Page") {
            if ($o.count -ne 0) {
                $rec += new-object -TypeName psobject -Property $o
                $o=@{}
            }
        }
    if ($TargetLabels -contains $_.label){
        $o += @{$_.label=$_.response}
        }
}
#add the one we were constructing
$rec += new-object -TypeName psobject -Property $o
$rec | Export-Csv -Path "C:\Users\Mark\downloads\Q_29095438_$(Get-Date -Format 'yyyyMMddhhmmss').csv" -NoTypeInformation

Open in new window

0
aikimarkCommented:
I've added Powershell to the zone list and added the datetime stamp as part of the output file name.
0
aikimarkCommented:
go ahead and close out this question
0
Nick CollinsAuthor Commented:
Following on with the powershell..

With the same as above fields, on the output file I would like to append the "Client / Site / Job Number" & "Order Number" together then separated by comma delimiter.

Client / Site / Job Number_Order Number

Without the double-quotes on the output file
0
aikimarkCommented:
@Nick

Please open a new question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.