Parsing a CSV with a varying number of columns and in the format Column=Data

Question

I have some data in a (poorly) delimited state that I've managed to turn into a CSV for further parsing.

I've managed to alter it such that's it's in the format:

"Timestamp", "ServerName", "ProcessName", Column4=Data4, Column5=Data5,... Column25=Data25

In practice this might look like:

12:34:23, MyServerName, MyProcess, Type=10, Price=45.4, ProductType=AV

21:23:17, MyServerName2, MyProcess2, Type=10, ProductType=AV, ClassKey=2324

I have two problems that I could use some help solving. The first is Parsing the data so that "Type=10" becomes a "Type" column header and that row takes the value "10" when I upload it to a MS SQL Server database. The second issue is that my columns aren't in consistent locations in my CSV. It sounds like I'll have to make an array of key:value pairs, but I'm uncertain how to do that, or if it's the best way. Can anyone offer some insight on what tools/methods would be best to go about this?

Possibly of interest http://stackoverflow.com/questions/8168548/ssis-task-for-inconsistent-column-count-import/8173162#8173162 — billinkc, Oct 29 '14 at 04:01

score 1 · Answer 1 · answered Oct 28 '14 at 21:29

Not sure if this helps or not, but:

$data = @(
'12:34:23, MyServerName, MyProcess, Type=10, Price=45.4, ProductType=AV'
'21:23:17, MyServerName2, MyProcess2, Type=10, ProductType=AV, ClassKey=2324'
)

$data -replace '^([^,]+),([^,]+),([^,]+)','Timestamp=$1, ServerName=$2, ProcessName=$3' |
foreach {New-Object PSObject -Property (convertfrom-stringdata $_.replace(',',"`n"))}



Timestamp   : 12:34:23
ProcessName : MyProcess
ServerName  : MyServerName
Price       : 45.4
ProductType : AV
Type        : 10

Timestamp   : 21:23:17
ProcessName : MyProcess2
ClassKey    : 2324
ServerName  : MyServerName2
ProductType : AV
Type        : 10

Parsing a CSV with a varying number of columns and in the format Column=Data

1 Answers1