Append/Convert undefined column's data into rows in DataTable using Linq

Hi,
Datatable contains undefined number of coulmns, For instance:

InspMain/inspectionId
InspMain/InspReportID
InspMain/InspectionPostDate
InspMain/InspStartDate
InspMain/InspStartTime
InspMain/InspEndTime
InspMain/InspectionLevelId
InspMain/InspectionLevelDesc
InspMain/PostAccidentIndicator
InspMain/InspLocation/InspLocationCode
InspMain/InspLocation/InspLocationText
InspMain/InspHazmatTypeCode
InspMain/Carrier/USDOTNum
InspMain/Carrier/CarrierMexicanID
InspMain/Carrier/CarrierName
InspMain/Carrier/CarrierAddress/AddressStreetText
InspMain/Carrier/CarrierAddress/AddressColoniaText
InspMain/Carrier/CarrierAddress/AddressCityName
InspMain/Carrier/CarrierAddress/AddressStateCode
InspMain/Carrier/CarrierAddress/AddressZipCode
InspMain/InspTotalCounts/InspTotalOOSVioNum
Drivers/Driver/0/DriverLastName
Drivers/Driver/0/DriverBirthDate
Drivers/Driver/0/DriverLicenseID
Drivers/Driver/0/DriverLicenseStateCode
Vehicles/Vehicle/0/VehicleUnitNum
Vehicles/Vehicle/0/VehicleUnitTypeCode
Vehicles/Vehicle/0/VehicleMakeCode
Vehicles/Vehicle/0/VehicleCompanyID
Vehicles/Vehicle/0/VehicleLicenseID
Vehicles/Vehicle/0/VehicleLicenseStateCode
Vehicles/Vehicle/0/IEPDotNumber
Vehicles/Vehicle/1/VehicleUnitNum
Vehicles/Vehicle/1/VehicleUnitTypeCode
Vehicles/Vehicle/1/VehicleMakeCode
Vehicles/Vehicle/1/VehicleCompanyID
Vehicles/Vehicle/1/VehicleLicenseID
Vehicles/Vehicle/1/VehicleLicenseStateCode
Vehicles/Vehicle/1/IEPDotNumber
StatusCode
Violations/Violation/0/VioSeqID
Violations/Violation/0/VioRegSectionCode
Violations/Violation/0/VioDescText
Violations/Violation/0/VioOOSFlag
Violations/Violation/0/VehicleUnitCode
Violations/Violation/0/ViolPartSection
Violations/Violation/0/FedVioCode
Violations/Violation/0/DisplayValue
Violations/Violation/0/StateCitationNumber
Violations/Violation/0/ViolationCategory
Violations/Violation/0/SectionDesc
Violations/Violation/0/IEPAttrFlag
Violations/Violation/0/StateCitationResult
Violations/Violation/1/VioSeqID
Violations/Violation/1/VioRegSectionCode
Violations/Violation/1/VioDescText
Violations/Violation/1/VioOOSFlag
Violations/Violation/1/VehicleUnitCode
Violations/Violation/1/ViolPartSection
Violations/Violation/1/FedVioCode
Violations/Violation/1/DisplayValue
Violations/Violation/1/StateCitationNumber
Violations/Violation/1/ViolationCategory
Violations/Violation/1/SectionDesc
Violations/Violation/1/IEPAttrFlag
Violations/Violation/1/StateCitationResult
Violations/Violation/2/VioSeqID
Violations/Violation/2/VioRegSectionCode
Violations/Violation/2/VioDescText
Violations/Violation/2/VioOOSFlag
Violations/Violation/2/VehicleUnitCode
Violations/Violation/2/ViolPartSection
Violations/Violation/2/FedVioCode
Violations/Violation/2/DisplayValue
Violations/Violation/2/StateCitationNumber
Violations/Violation/2/ViolationCategory
Violations/Violation/2/SectionDesc
Violations/Violation/2/IEPAttrFlag
Violations/Violation/2/StateCitationResult
HazMats/HazMat/0/HMSeqNum
HazMats/HazMat/0/HMTypeCode
HazMats/HazMat/0/HMReportableQuantityFlag
HazMats/HazMat/0/HMWasteFlag
HazMats/HazMat/0/HMDescription
HazMats/HazMat/1/HMSeqNum
HazMats/HazMat/2/HMTypeCode
HazMats/HazMat/2/HMReportableQuantityFlag
HazMats/HazMat/2/HMWasteFlag
HazMats/HazMat/2/HMDescription

Out of these columns we need to filter these columns;

InspMain/InspReportID
InspMain/InspectionPostDate
InspMain/InspStartTime
InspMain/InspEndTime
InspMain/InspectionLevelDesc
InspMain/PostAccidentIndicator
InspMain/InspLocation/InspLocationText
Drivers/Driver/0/DriverLastName
Drivers/Driver/0/DriverBirthDate
Drivers/Driver/0/DriverLicenseID
Drivers/Driver/0/DriverLicenseStateCode
HazMats/HazMat/0/HMReportableQuantityFlag
HazMats/HazMat/0/HMWasteFlag
HazMats/HazMat/0/HMDescription
Vehicles/Vehicle/0/VehicleUnitNum
Vehicles/Vehicle/0/VehicleUnitTypeCode
Vehicles/Vehicle/0/VehicleMakeCode
Vehicles/Vehicle/0/VehicleCompanyID
Vehicles/Vehicle/0/VehicleLicenseID
Vehicles/Vehicle/0/VehicleLicenseStateCode
Vehicles/Vehicle/0/IEPDotNumber
Violations/Violation/0/VioRegSectionCode
Violations/Violation/0/VioDescText
Violations/Violation/0/VioOOSFlag
Violations/Violation/0/VehicleUnitCode
Violations/Violation/0/StateCitationNumber
Violations/Violation/0/SectionDesc
Violations/Violation/0/StateCitationResult

Condition: Rest other columns similar to the filtered columns need to be appended/converted to rows, in result rows will get repeated.

As of now I’m able to filter the columns and storing it in a datatable but unable to append the extra columns into rows. Here is my approach:

**Get the list of columns names filtered with the regex **


dt.Columns.Cast(Of Datacolumn). Select (Function (col) col.ColumnName.ToString.Trim).ToArray().Where(Function(e)
System.Text.RegularExpressions.Regex.IsMatch(e.ToString,regexpattern)=True).ToArray()

dt_new= dt1.defaultview.ToTable(False,StrArry_ColNames)

@ppr @Yoichi
Kindly help me to get a resolution for this.
Thanks in advance :slightly_smiling_face:

@Sivasees_Rajguru - A Similar /Same Requirement is stated in another post, I would suggested you to go this Solution. Could you Check and let us know if the post was helpful

1 Like

Sir,
It was me who posted that doubt. Looking forward for solution.

@Ram_Shiva_Reddy & @Sivasees_Rajguru Will help you on it sure !!

on a quick view we do see different posts and different structures within the sample data. We would recommend to freeze more the requirement and scope the topic to a case, which is worked on till to end.

This topic sample data look like it is trying to bring an extract of nontabular data into a tabular form. From where is the excel data retrieved, taken?

Maybe you elaborate more on this case and how it came to this excel input.

Hi,
Sorry to say but this doesn’t resembles my scenario. Because I have already tabular data extracted.

Thanks

Hi,

Thanks for your response.
Input data is retrieved from a XML file and output data is the required format.

Lets say, when we convert the XML to DataTable we get approx 84~89 columns. Out of which we only need distinct selected 28 columns as mentioned above.

then we would recommend using the origin XML as the source and extracting from there the needed output

Okay. If you want me to share the XML file can I please do it one to one ?
Thanks.