Importing Poorly Formatted Text File to SQL

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  January 30, 2016 at 5:24 pm

  #1855541

  mxy (1/30/2016)

  ---

  I have received file from other team,i will check with them, how it was generated.

  About duplicate columns names, even though column name is repeated value is unique and it can be n number of times per user.

  Yes, we assumed that on the duplicate column names. The question is, what do YOU want to do with them in the output?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• mxy

  SSCarpal Tunnel

  Points: 4036

  More actions

  January 30, 2016 at 5:50 pm

  #1855542

  O/p should be in comma separated

  job-title

  Admin, approve_perm

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  January 30, 2016 at 8:08 pm

  #1855543

  I'm glad you guys jumped on this, complete with quirky update and everything. I wish I'd had time to address is properly earlier today. Like Jeff said, this kind of stuff is fun.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  January 31, 2016 at 7:24 am

  #1855557

  Jeff Moden (1/30/2016)

  ---

  Thank you for the post on how to load the SSIS package. I appreciate the time you took. I'll have to pass, though. I was looking forward to seeing an SSIS solution that had no script component.

  I understand. At this point I would as well so I could learn some new SSIS-fu 😛 From where I sit, this requirement has just enough twists and turns to compel me to bring C# to the party no matter how I slice it.

  I've come up with a couple other ways I could do this in SSIS. One has C# in an extremely limited role but alas still needed. If I come up with a pure SSIS solution or the OP decides the Quirky Update isn't really their cup of tea I'm happy to assist with an SSIS solution.

  Note, this is not to say I couldn't just load the file into a table with an identity-column plus the mame-value data and then pickup the solution in T-SQL using the Quirky Update method you showed but simply replacing BULK INSERT with an SSIS raw-file-loader isn't all that interesting.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  January 31, 2016 at 3:32 pm

  #1855583

  OK, attached is an SSIS Package that uses only native SSIS Tasks and Components and T-SQL, i.e. no C# Script is utilized in the SSIS. Files included in the attached zip file:

  1. ActiveDirectoryLog.txt - sample input file (from OP)

  2. ActiveDirectoryLog2_TABLE.sql - DDL to create destination table in tempdb

  3. ImportActiveDirectoryLog2.dtsx - SSIS Package that loads the sample file into the table in tempdb

  4. ActiveDirectoryLog2_SELECT.sql - query to deliver results in required shape

  This turned out to be remarkably simple once I realized the row-delimiter I should have been using all along was \r\n\r\n, not \r\n 😎

  This is the table definition the file is loaded into (also in the attached zip):

  CREATE TABLE dbo.active_directory_profile(

  profile_id int IDENTITY(1,1) NOT NULL,

  profile varchar(8000) NULL

  );

  And this is the query that brings back the desired output (also in the attached zip):

  WITH cte AS (SELECT adp.profile_id,

  LEFT(x.item,CHARINDEX(': ',x.item)-1) AS [name],

  -- requires dbo.GROUP_CONCAT from http://groupconcat.codeplex.com

  dba.dbo.GROUP_CONCAT(SUBSTRING(x.item,CHARINDEX(': ',x.item)+2,8000)) AS [value]

  FROM dbo.active_directory_profile adp

  -- requires dbo.StringSplit_Multi from http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

  CROSS APPLY dba.dbo.SplitString_Multi(adp.[profile], CHAR(13)+CHAR(10)) x

  GROUP BY adp.profile_id,

  LEFT(x.item,CHARINDEX(': ',x.item)-1))

  SELECT profile_id,

  MAX(CASE WHEN name = 'dn' THEN [value] END) AS dn,

  MAX(CASE WHEN name = 'objectClass' THEN [value] END) AS objectClass,

  MAX(CASE WHEN name = 'userid' THEN [value] END) AS userid,

  MAX(CASE WHEN name = 'companyVal' THEN [value] END) AS companyVal,

  MAX(CASE WHEN name = 'company' THEN [value] END) AS company,

  MAX(CASE WHEN name = 'job-title' THEN [value] END) AS [job-title],

  MAX(CASE WHEN name = 'mail' THEN [value] END) AS mail,

  MAX(CASE WHEN name = 'createdby' THEN [value] END) AS createdby,

  MAX(CASE WHEN name = 'modifiedby' THEN [value] END) AS modifiedby,

  MAX(CASE WHEN name = 'createdTime' THEN [value] END) AS createdTime,

  MAX(CASE WHEN name = 'modifiedTime' THEN [value] END) AS modifiedTime

  FROM cte

  GROUP BY profile_id;

  Results:

  

  Note that there are two functions you will need to install for my query above to work, a grouped-string-concatenation-function and a string-splitter-function. One is implemented as a SQLCLR Aggregate and the other as a SQLCLR Table-Valued function. The comments show where you can get them. If SQLCLR makes you twist in your seat you can replace the grouped-string-concatenation-function with the technique that leverages FOR XML and the string-splitter with Mr. Moden's T-SQL splitter.

  Edit: fix tabs in code

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato