Nick Collins
asked on
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
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
Please post one of these CSV files
The file you are getting is from a source, can you configure the source to give you want you want?
ASKER
I can't change the source
ASKER
File attached
Template1.csv
Template1.csv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.. that has worked perfectly.
On the output file - is it possible to put the YYYYMMDDhhmmss at the end of the file name
On the output file - is it possible to put the YYYYMMDDhhmmss at the end of the file name
Do you want to go with a PS solution?
Yes. It is possible to alter the name of the output file.
Yes. It is possible to alter the name of the output file.
ASKER
Yes powershell is good
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I've added Powershell to the zone list and added the datetime stamp as part of the output file name.
go ahead and close out this question
ASKER
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
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
@Nick
Please open a new question
Please open a new question