Import question

Question

Import question

jeremy.schillinger

Grasshopper

Points: 12
More actions
September 27, 2017 at 12:01 pm

#339547

I am importing data from a 3rd party system into a DB that I am going to use for reporting. I want to import the UserID from the user column, and populate the record with the Date of the column with a date (into the Date field) in the row contains an X in the column. I know simple imports, but this one ins a little complicated for me. any help is appreciated.
Here is a sample of the data:
People Export

UserId First Name Last Name Sep 4, 2016 Sep 11, 2016 Sep 18, 2016 Sep 25, 2016 Oct 2, 2016 Oct 9, 2016 Oct 16, 2016
1023 Jane Doe X X X
1032 John Doe X X
1054 James Smith X X
For example, UserID would have 3 records created:
UserId: 1023 Date: 9/4/2016
UserId: 1023 Date: 9/25/2016
UserId: 1023 Date: 10/9/2016
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004558 More actions · Answer 1

jeremy.schillinger - Wednesday, September 27, 2017 12:01 PM
I am importing data from a 3rd party system into a DB that I am going to use for reporting. I want to import the UserID from the user column, and populate the record with the Date of the column with a date (into the Date field) in the row contains an X in the column. I know simple imports, but this one ins a little complicated for me. any help is appreciated.
Here is a sample of the data:
People Export
UserId First Name Last Name Sep 4, 2016 Sep 11, 2016 Sep 18, 2016 Sep 25, 2016 Oct 2, 2016 Oct 9, 2016 Oct 16, 2016
1023 Jane Doe X X X
1032 John Doe X X
1054 James Smith X X
For example, UserID would have 3 records created:
UserId: 1023 Date: 9/4/2016
UserId: 1023 Date: 9/25/2016
UserId: 1023 Date: 10/9/2016
Thanks,

What format is the file in? CSV? TSV? Fixed Field? Or ???

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

EDIT: Disregard, I misunderstood.

If that's for reporting, I suggest that you do it on the front end.
However, here are 2 articles that can show you the path to follow:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
~~Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral~~

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

Maybe it would be easiest to import into a temporary table then unpivot to put into your real table?
Here's an article about unpivoting using CROSS APPLY which I've found to work very well:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Jason A. Long SSC-Insane Points: 23789 More actions · Answer 4

If you can get the raw file into a staging table, you can use something like the following to get into the shape you're looking for.

-- create some test data... IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL DROP TABLE #TestData;

CREATE TABLE #TestData ( UserId INT, FirstName VARCHAR(20), LastName VARCHAR(20), Sep_04_2016 CHAR(1), Sep_11_2016 CHAR(1), Sep_18_2016 CHAR(1), Sep_25_2016 CHAR(1), Oct_02_2016 CHAR(1), Oct_09_2016 CHAR(1), Oct_16_2016 CHAR(1) ); INSERT #TestData (UserId, FirstName, LastName, Sep_04_2016, Sep_11_2016, Sep_18_2016, Sep_25_2016, Oct_02_2016, Oct_09_2016, Oct_16_2016) VALUES (1023, 'Jane', 'Doe', 'X', NULL, NULL, 'X', NULL, 'X', NULL), (1032, 'John', 'Doe', 'X', NULL, NULL, 'X', NULL, NULL, NULL), (1054, 'James', 'Smith', 'X', 'X', NULL, NULL, NULL, NULL, NULL);

--====================================================================================== -- the actual solution... SELECT td.UserId, td.FirstName, td.LastName, DateValue = CAST(d.DateValue AS DATE) FROM #TestData td CROSS APPLY ( VALUES (Sep_04_2016, '20160904'), (Sep_11_2016, '20160911'), (Sep_18_2016, '20160918'), (Sep_25_2016, '20160925'), (Oct_02_2016, '20161002'), (Oct_09_2016, '20161009'), (Oct_16_2016, '2016-1016') ) d (dMark, DateValue) WHERE d.dMark = 'X';

Results...
UserId FirstName LastName DateValue ----------- -------------------- -------------------- ---------- 1023 Jane Doe 2016-09-04 1023 Jane Doe 2016-09-25 1023 Jane Doe 2016-10-09 1032 John Doe 2016-09-04 1032 John Doe 2016-09-25 1054 James Smith 2016-09-04 1054 James Smith 2016-09-11

Thom A SSC Guru Points: 99137 More actions · Answer 5

The OP doesn't give us a lot to go on here, but I have (bad) feeling that those date columns are subject to change, thus the need for some "D-SQL" instead.

One solution, therefore, using UNPIVOT inside the dynamic statement. I have used Jason's sample data (thanks Jason!), just not a temporary table:
DECLARE @Columns varchar(MAX); SELECT @Columns = STUFF((SELECT ',[' + c.name + ']' FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.[name] = 'TestData' AND TRY_CONVERT(date, c.name) IS NOT NULL FOR XML PATH ('')),1,1,''); DECLARE @sql varchar(MAX); SET @sql = 'SELECT * FROM (SELECT UserID, FirstName, LastName, CONVERT(date, ColumnName) AS DateValue FROM TestData) td UNPIVOT (ValidDate FOR ColumnName IN (' + @Columns + ')) UP'; EXEC (@SQL);

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

UserId	First Name	Last Name	Sep 4, 2016	Sep 11, 2016	Sep 18, 2016	Sep 25, 2016	Oct 2, 2016	Oct 9, 2016	Oct 16, 2016
1023	Jane	Doe	X			X		X
1032	John	Doe	X			X
1054	James	Smith	X	X

Import question

Cookies on SQLServerCentral