how can I parse this file that comes back from snmp queries?

[JasonMiller]

I've tried multiple components, and I'm just not sure how to parse this file. Any suggestions appreciated. I'm posting to the CSV board because it seems like the closest fit, but please correct me if I'm wrong.

Input file:

WHISP-APS-MIB::linkSiteName.2 = Chicago

WHISP-APS-MIB::linkSiteName.3 = New York

WHISP-APS-MIB::linkManagementIP.2 = 10.208.194.9

WHISP-APS-MIB::linkManagementIP.3 = 10.208.194.12

WHISP-APS-MIB::avgPowerLevelInt.2 = -61

WHISP-APS-MIB::avgPowerLevelInt.3 = -65

WHISP-APS-MIB::signalToNoiseRatioVertical.2 = 34

WHISP-APS-MIB::signalToNoiseRatioVertical.3 = 29

WHISP-APS-MIB::signalToNoiseRatioHorizontal.2 = 32

WHISP-APS-MIB::signalToNoiseRatioHorizontal.3 = 27

Desired output (I'm doing a csv format below, but I'd like it in a real recordset):

ID,linkSiteName,linkManagementIP,avgPowerLevel,signalTonoiseRatioVertical,signalToNoiseRatioHorizontal

2, Chicago, 10.208.194.9, -61, 34, 32

3, New York, 10.208.194.12, -65, 29, 27

Note that I've reduced the file considerably. There will need to be close to 40 columns, and the part of the fieldname like .2 and .3 that I'm using for ID is a variable length per file, usually around 30.

tFileInputDelimited works and will split on the " = ", but how can I then transpose the data set sideways?

I thought tFileInputProperties would work, but it chokes on the "::". I considered preprocessing it to remove that since the "WHISP-APS-MIB::" will be consistent through the entire file and so it can (and probably should) be removed. I guess mainly I'm stuck on how to collate the data, if that's the correct term.

Thank you

--Jason Miller

[JasonMiller · Accepted solution]

Nevermind, I figured out a way. I used a series of tExtractDelimitedFields to split up each line and then a tmap to create a Position|Variable|Value table and then a tPivotToColumnsDelimited object to write out a transposed CSV. Then read that CSV back in with tInputFileDelimited with the appropriate schema.