Solved

Pipe Delimited text into SQL Multiple Tables

Posted on 2011-09-21
6
341 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
insert into table 8 22
SQL Syntax 5 33
Loop to go backward 90 days 2 18
SQL Server Count where two id types exist in column 8 27
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

805 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