How to parse strings with different variables in each string?

Question

I have a file of strings like:

"Row 1: Location=1922&JobType=11&JobName=3719&SkillLevel:isnull=true&JobStatus=0"

"Row 2: OffenderId=1447983&Location=0&OrderBy= "

"Row 3: User=tnu11607&Agency=767&ReportYear=2015&ReportMonth=2"

There are multiple patterns depending on which of 200+ reports were run, but the parameters can be different for each report.

I'd like to parse this out into something like:

 ParamLocation  ParamOffender  ParamOrderBy  ParamUser  ...

1922 --- --- --- ...
--- 1447983 NULL --- ...
--- --- --- tnu11607 ...

(Hard to get the spacing to show adequately)

Etcetera.

I'm not even sure quite how to begin. Any Suggestions would be greatly appreciated. I'll be looking at this over the weekend.

Community · Accepted Answer · 2017-05-23 12:12:54Z

0

Try digging into Regular Expressions, and in SSIS, you could use a Script Task. Drawing on answers from this SO post, you could use something like the following, and you can see it in action using something LINQPad or Visual Studio.

string queryString = "Row 1: Location=1922&JobType=11&JobName=3719&SkillLevel:isnull=true&JobStatus=0";
Dictionary<string, string> matches = Regex.Matches(queryString, "@"(\w+:?\w*)(=(\w+))&?"").Cast<Match>().ToDictionary(x => x.Groups[1].Value, x => x.Groups[3].Value);
Console.WriteLine(matches["Location"]);

To automate that by rows in SSIS, just make the queryString variable your row text.

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Mar 20, 2015 at 18:52

sorrell

1,8712 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lynne Davidson Over a year ago

Great! I've been looking for an excuse to dig into regular expressions. I'll let you know when I've had some time to explore. Thanks.

sorrell Over a year ago

My regex-fu is fairly weak, so I updated the answer. You can check out online tools like this and this to visually see what pattern the regex is indicating.

Collectives™ on Stack Overflow

How to parse strings with different variables in each string?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related