Solved

Pipe Delimited text into SQL Multiple Tables

Posted on 2011-09-21
6
337 Views
Last Modified: 2012-05-12
I need help with how to bring data from a pipe delimited text file into a SQL database.

Here is a small sample of the data:

  10|0001|Smith|Joe|...
  20|0001|BCBS|YAQ123456789|...
  20|0001|MEDICARE|555224444A|...
  30|0001|09192011|CT HEAD|1|100.00|...
  10|0002|Jones|Kay|...
  20|0002|MEDICIAD|333226666|...
  30|0002|09192011|CT ABDOMEN|1|250.00|...

The "10" line is the patient's basic information: name, address, birthdate
The "20" line is the patient's insurance information:: company, ID number
The "30" line contains the charge information: date, procedure, quantity, price

The 2nd field in each row is the Patient ID#, and is how the lines are related.

I need to read the data and insert the separate lines into appropriate tables based on the "10", "20", "30".

The database and tables already exist, and this is a process which needs to run daily.

I am very limited in SQL knowledge, thanks for assistance.

0
Comment
Question by:wcotis60
  • 2
  • 2
  • 2
6 Comments
 
LVL 4

Expert Comment

by:jmnf
ID: 36575174
Here an example code of one way to do it, i have it as a function.

DECLARE @string NVARCHAR(MAX),
		@separator NVARCHAR(MAX);	-- INPUT VALUES

DECLARE @indx1 INT, @indx2 INT;		-- VARIABLES USED INTERNALLY

SET @string = 'one,two,three,four';	-- EXAMPLE STRING
SET @separator = ',';				-- EXAMPLE SEPARATOR
		
DECLARE @tempTable TABLE(someValue NVARCHAR(MAX));	-- TABLE TO HOLD SEPARATED VALUES

SET @indx1 = 0;
	
WHILE @indx1 > -1
	BEGIN
		SELECT @indx2 = CHARINDEX(@separator, @string, @indx1);
		IF @indx2 > 0
			BEGIN
				INSERT INTO @tempTable(someValue)
					SELECT SUBSTRING(@string, @indx1, @indx2 - @indx1);
				SET @indx1 = @indx2 + 1;
			END
		ELSE
			BEGIN
				INSERT INTO @tempTable(someValue)
					SELECT SUBSTRING(@string, @indx1, LEN(@string) + 1 - @indx1);
				SET @indx1 = -1;
			END
	END

SELECT someValue FROM @tempTable

Open in new window


Hope it helps you
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36575211
You can do this with SSIS and the Conditional Split Transformation (http://msdn.microsoft.com/en-us/library/ms137886.aspx), but it is not trivial and I would urge you to hire some competent consultant to show you the ropes.
0
 

Author Comment

by:wcotis60
ID: 36575877
ACPERKINS, Currently trying to find a way with MS SQL Server Express, which does not provide SSIS, failed to mention that earlier. sorry.

JMNF,  can you be more specific with my example data in your function?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:wcotis60
ID: 36576658
I do have a couple of SQL 2000 servers in place with DTS which could be used.
0
 
LVL 4

Assisted Solution

by:jmnf
jmnf earned 250 total points
ID: 36577198
This is a beter example that parses a line like the ones you give in your sample data.
You just need to make a loop somehow to automatically (if possible) parse line by line, and that depends on how you get your "sample data" (in a string, in a file, etc)... not sure how you get your data.

-- INPUT VALUES
DECLARE @string NVARCHAR(MAX), @separator NVARCHAR(MAX);

----------------------------------------------------------------
-- EXAMPLE STRING (EACH LINE)                                 --
SET @string = '30|0002|09192011|CT ABDOMEN|1|250.00|...';     --
-- EXAMPLE SEPARATOR (PIPE)                                   --
SET @separator = '|';                                         --
----------------------------------------------------------------

-- TABLES
--    YOU MENTIONED THEY ARE ALREADY CREATED,
--    SO MAKE SURE TO CHANGE TO YOUR NEEDS IN THE INSERT STATEMENTS)
DECLARE @tPatients   TABLE (
iIdPatient INT,
sLastName VARCHAR(50),
sFirstName VARCHAR (50),
sOtherData VARCHAR(50)  );

DECLARE @tInsurances TABLE (
iIdPatient INT,
sInsuranceName VARCHAR(50),
iIdInsuranceMemberNumber VARCHAR(50),
sOtherData VARCHAR(50)  );

DECLARE @tProcedures TABLE (
iIdPatient INT,
dProcedureDate SMALLDATETIME,
sProcedure VARCHAR(50),
iQuantity INT,
fProcedureUnitaryPrice FLOAT,
sOtherData VARCHAR(50)  );

-- VARIABLES FOR TABLE VALUES
DECLARE @iIdPatient INT, @sLastName VARCHAR(50), @sFirstName VARCHAR(50), @sOtherData VARCHAR(50),
		@sInsuranceName VARCHAR(50), @iIdInsurancememberNumber VARCHAR(50),
		@dProcedureDate SMALLDATETIME, @sProcedure VARCHAR(50), @iQuantity INT, @fProcedureUnitaryPrice FLOAT,
		@day VARCHAR(2), @month VARCHAR(2), @year VARCHAR(4), @mmddyyyyDate VARCHAR(8);

-- VARIABLES USED FOR STRING PARSING
DECLARE @indx1 INT, @indx2 INT, @whichTable INT, @someRow INT;

-- TABLE TO HOLD TEMPORARY STRING TO ROW INFORMATION (STRING TO COLUMNS THEN COLUMN TO ROW)
DECLARE @tempTable TABLE(someRow INT,someValue NVARCHAR(MAX));

---------------------------------------------------------------------------------------------------
-- MAKE LOOP STARTING HERE TO PARSE LINES (DIFFERENT INPUT VALUES) -----------------------
---------------------------------------------------------------------------------------------------

-- INITIALIZE ROW NUMBER
SET @someRow = 1;

-- INITIALIZES FIRST INDEX
SET @indx1 = 0;

WHILE @indx1 > -1
	BEGIN
		-- SETS SECOND INDEX TO THE NEXT SEPARATOR CHARACTER IN THE STRING
		SELECT @indx2 = CHARINDEX(@separator, @string, @indx1);
		
		-- CHECKS THAT THE STRING STILL HAS ANOTHER SEPARATOR STRING, ELSE, ADDS THE REMAINING TO THE TABLE
		IF @indx2 > 0
			BEGIN
				-- INSERT STRING BETWEEN FIRST AND SECOND INDEXES TO TABLE ROW
				INSERT INTO @tempTable(someRow, someValue)
					SELECT @someRow, SUBSTRING(@string, @indx1, @indx2 - @indx1);
					
				-- ADVANCES FIRST INDEX TO THE NEXT CHARACTER AFTER THE SECOND INDEX
				SET @indx1 = @indx2 + 1;
				
				-- INCREASES ROW NUMBER AFTER EACH INSERTION
				SET @someRow = @someRow + 1;
			END
		ELSE -- (...ELSE, ADDS THE REMAINING TO THE TABLE)
			BEGIN
				-- INSERT REMAINING STRING TO TABLE ROW
				INSERT INTO @tempTable(someRow, someValue)
					SELECT @someRow, SUBSTRING(@string, @indx1, LEN(@string) + 1 - @indx1);
		-- SETS THE FIRST INDEX TO -1 SO THE LOOP WILL END
				SET @indx1 = -1;
			END
	END

-- GET WHICH TABLE THE INFORMATION BELONGS TO FROM FIRST ROW
SET @whichTable = CAST((SELECT someValue FROM @tempTable WHERE someRow = 1) AS INT);


-- DO THIS FOR EVERY TABLE THAT HAS TO BE CONSIDERED
--   AND CHANGE ACCORDINGLY IF TABLES ARE ALREADY CREATED ON YOUR DATABASE
IF @whichTable = 10
	BEGIN
		SELECT	@iIdPatient = CAST((SELECT someValue FROM @tempTable WHERE someRow = 2) AS INT),
				@sLastName = (SELECT someValue FROM @tempTable WHERE someRow = 3),
				@sFirstName = (SELECT someValue FROM @tempTable WHERE someRow = 4),
				@sOtherData = (SELECT someValue FROM @tempTable WHERE someRow = 5);
		
		INSERT INTO @tPatients (iIdPatient, sLastName, sFirstName, sOtherData)
			VALUES (@iIdPatient, @sLastName, @sFirstName, @sOtherData)
	END

IF @whichTable = 20
	BEGIN
		SELECT	@iIdPatient = CAST((SELECT someValue FROM @tempTable WHERE someRow = 2) AS INT),
				@sInsuranceName = (SELECT someValue FROM @tempTable WHERE someRow = 3),
				@iIdInsurancememberNumber = CAST((SELECT someValue FROM @tempTable WHERE someRow = 4) AS INT),
				@sOtherData = (SELECT someValue FROM @tempTable WHERE someRow = 5);
		
		INSERT INTO @tInsurances (iIdPatient, sInsuranceName, iIdInsuranceMemberNumber, sOtherData)
			VALUES (@iIdPatient, @sInsuranceName, @iIdInsurancememberNumber, @sOtherData)
	END

IF @whichTable = 30
	BEGIN
		SELECT	@iIdPatient = CAST((SELECT someValue FROM @tempTable WHERE someRow = 2) AS INT),
				@mmddyyyyDate = (SELECT someValue FROM @tempTable WHERE someRow = 3),
				@sProcedure = (SELECT someValue FROM @tempTable WHERE someRow = 4),
				@iQuantity = CAST((SELECT someValue FROM @tempTable WHERE someRow = 5) AS INT),
				@fProcedureUnitaryPrice = CAST((SELECT someValue FROM @tempTable WHERE someRow = 6) AS FLOAT),
				@sOtherData = (SELECT someValue FROM @tempTable WHERE someRow = 7);
		
		SELECT	@month = SUBSTRING(@mmddyyyyDate,1,2),
				@day = SUBSTRING(@mmddyyyyDate,3,2),
				@year = SUBSTRING(@mmddyyyyDate,5,4);
		
		SET @dProcedureDate = CAST((@year + '-' + @month + '-' + @day) AS SMALLDATETIME);
		
		INSERT INTO @tProcedures (iIdPatient, dProcedureDate, sProcedure, iQuantity, fProcedureUnitaryPrice, sOtherData)
			VALUES (@iIdPatient, @dProcedureDate, @sProcedure, @iQuantity, @fProcedureUnitaryPrice, @sOtherData)
	END

---------------------------------------------------------------------------------------------------
-- END LOOP FOR PARSING LINES ---------------------------------------------------------------------
---------------------------------------------------------------------------------------------------

SELECT someRow, someValue FROM @tempTable
SELECT * FROM @tPatients
SELECT * FROM @tInsurances
SELECT * FROM @tProcedures

Open in new window


Commented as much as posible the code. Hope this one really helps.

Note: Usually, tables should have an ID field, like the one that the patient table seems to have just by looking at the sample data... but the other tables (looking at the sample data) should have an ID field, and data on those tables should be accessed through their corresponding ID, not by their name or description (e.g. "BCBS" / "MEDICAID" / ETC). If possible change those tables to have an ID and access data through their ID.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 36577527
>>I do have a couple of SQL 2000 servers in place with DTS which could be used. <<
And that is even more complicated as there is no native split component.  But what you have to do is read the table and write it out to t text files.  You can then import them into three staging tables, before joining them altogether in the final production table(s).
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now