topic Splitting up a multi-delimiter subfield on data load in App Development

Splitting up a multi-delimiter subfield on data load

Wed, 13 Apr 2016 03:18:52 GMT

I'm having issues getting my data loaded correctly, and could really use some help here. Here's what it looks like so far:

Company Id	Software
1	AnalyticsGoogle AnalyticsLanguagesAJAX,HTML,PHPCollaborationGoogle Apps
2	Data ManagementLabWare LIMS,BMC,Microsoft SQL Server
3	AnalyticsOmnitureCollaborationGoogle AppsLanguagesPHP,.NET

There are line breaks where you see lack of spaces. I have this in a CSV, and am getting confused with the subfields within the subfields in the Software.field. The software category follows the line breaks. Then the category's subfield is comma delimited.

Here's what the above data should look like when nice and tidy:

Company 1:

Analytics

Google Analytics

Languages

AJAX

HTML

PHP

Collaboration

Google Apps

Company 2:

Data Management

Labware LIMS

BMC

Microsoft SQL Server

(etc)

And here's what I'm trying get to with load script:

Company Id	Category	Software
1	Analytics	Google Analytics
1	Languages	AJAX
1	Languages	PHP
1	Collaboration	Google Apps
2	Data Management	Labware LIMS
2	Data Management	BMC
2	Data Management	Microsoft SQL Server
3	Analytics	Omniture
3	Collaboration	Google Apps
3	Languages	PHP
3	Languages	.NET

I'd really appreciate any pointers. Thanks!

Re: Splitting up a multi-delimiter subfield on data load

swuehl — Wed, 13 Apr 2016 08:47:22 GMT

Could you post some lines of your csv file?

Re: Splitting up a multi-delimiter subfield on data load

Wed, 13 Apr 2016 19:22:22 GMT

I have an idea of what I want to do, but still can't figure it out. I'm thinking I create an inline table with a "category" field, and the values are the set of 24 categories possible in the CSV. Then I want to look up each category, and if it finds it, load the subfields in new lines corresponsing to the category and company id. Is that possible?

Here's what the CSV looks like:

"Company Id","Company Technologies"

"31777","Advertising

Google AdWords

Business Intelligence/Big Data

Google Analytics

Collaboration

Google Apps

Data Storage

WordPress

Databases

MySQL

ERP / HR / HCM / FI

ADP

Languages

AJAX,HTML,PHP"

"38974",

"39524",

"31971","Databases

Microsoft Team Foundation Server (TFS)

ERP / HR / HCM / FI

CATS Applicant Tracking System

Languages

Microsoft Visual C#,Java,PHP,CSS,HTML,HTML5,Microsoft Visual C++

Programming Tools

JSON,AngularJS,Agile,Microsoft .NET Framework,BackboneJS,Microsoft Foundation Class (MFC)"