2 Replies Latest reply: Apr 13, 2016 4:14 PM by Jeff Kodish RSS

    Splitting up a multi-delimiter subfield on data load

    Jeff Kodish

      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 IdSoftware
      1

      AnalyticsGoogle AnalyticsLanguagesAJAX,HTML,PHPCollaborationGoogle Apps

      2

      Data ManagementLabWare LIMS,BMC,Microsoft SQL Server

      3AnalyticsOmnitureCollaborationGoogle 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 IdCategorySoftware

      1

      AnalyticsGoogle Analytics
      1LanguagesAJAX
      1LanguagesPHP
      1CollaborationGoogle Apps
      2Data ManagementLabware LIMS
      2Data ManagementBMC
      2Data ManagementMicrosoft SQL Server
      3AnalyticsOmniture
      3CollaborationGoogle Apps
      3LanguagesPHP
      3Languages.NET



         

      I'd really appreciate any pointers. Thanks!

        • Re: Splitting up a multi-delimiter subfield on data load
          Stefan Wühl

          Could you post some lines of your csv file?

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

              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)"