473,329 Members | 1,212 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,329 software developers and data experts.

one column text with multiple records to multiple data coulumns

5
Can anyone help me to write a t-sql script to import a txt file to sql server 2000?
I received a text report from a vendor that i need to extract the data for calculations. Each patient account has multilple lines that are not link to each other. I was succesfully used the substring command to extract the first line, but i could not link data in first line with the 2 line.

In other words, i want to link all below records in one line record.

This is a sample of one account records:


E-TIME PATIENT LOS (HH:MM) AGE BIRTHDATE DEPART DIAGNOSIS (DX) ADDITIONAL DX3

09/01/07-0009 ACCT# AB999999999 1:59 40 07/06/1967 2 degree burn to fingers
ESI LVL: 5 MR#: MB9999999 ADM PHYS: DISPOSITION: HOME, SELF-CARE 01
NAME: EEEE,PRRR ATT PHYS:
0000 GALLOPING WAY ER PHYS: POTEET,CHARLES L JR. M.D. ADM DATE-TIME: -
Aridfjd ,AR 75503 PRACTITIONER: ROOM-BED: -
(510-555-5555
TRIAGE DATE-TIME: 01/01/07-0011 COUNTY: BOWIE (TX) NURSE: Josh T Pinkham
FIRST PHYS RECORDED EVENT: 09/01/07-0028 STATED COMPLAINT: BURN FINGERS ON RT HAND
DEPART DATE-TIME: 01/01/07-0208 CHIEF COMPLAINT: nothing


Thansk,

ME.
Sep 10 '07 #1
4 1846
ck9663
2,878 Expert 2GB
Can anyone help me to write a t-sql script to import a txt file to sql server 2000?
I received a text report from a vendor that i need to extract the data for calculations. Each patient account has multilple lines that are not link to each other. I was succesfully used the substring command to extract the first line, but i could not link data in first line with the 2 line.

In other words, i want to link all below records in one line record.

This is a sample of one account records:


E-TIME PATIENT LOS (HH:MM) AGE BIRTHDATE DEPART DIAGNOSIS (DX) ADDITIONAL DX3

09/01/07-0009 ACCT# AB999999999 1:59 40 07/06/1967 2 degree burn to fingers
ESI LVL: 5 MR#: MB9999999 ADM PHYS: DISPOSITION: HOME, SELF-CARE 01
NAME: EEEE,PRRR ATT PHYS:
0000 GALLOPING WAY ER PHYS: POTEET,CHARLES L JR. M.D. ADM DATE-TIME: -
Aridfjd ,AR 75503 PRACTITIONER: ROOM-BED: -
(510-555-5555
TRIAGE DATE-TIME: 01/01/07-0011 COUNTY: BOWIE (TX) NURSE: Josh T Pinkham
FIRST PHYS RECORDED EVENT: 09/01/07-0028 STATED COMPLAINT: BURN FINGERS ON RT HAND
DEPART DATE-TIME: 01/01/07-0208 CHIEF COMPLAINT: nothing


Thansk,

ME.

sorry, where did the first record end and second record start?
Sep 11 '07 #2
ME2007
5
sorry, where did the first record end and second record start?
This is one record. All these fields belong to one patient account.
Sep 11 '07 #3
ME2007
5
This is one record. All these fields belong to one patient account.
These script allows me to strip one line of the posted records of one patient account:

SELECT substring(col001,28,22) as ACCT#,substring(col001,28,12)AS dob,substring(col001,122,255)AS dx
into testii
FROM [dbo].[YY090107]
WHERE substring(col001,32,1) = '#'

Thanks,

ME
Sep 11 '07 #4
mygr8r
2
I can't help with a T-SQL script, but if you can use a 3rd party tool, you should try the Data Extractor for Unstructured Text from Pervasive Software (http://pervasivedatatools.com). I've used it to extract data from some pretty ugly text files and it works most of the time. The extracted data is assembled into fields and records and exported to a CSV file.
Oct 15 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: me | last post by:
I would like to add an Identity to an existing column in a table using a stored procedure then add records to the table and then remove the identity after the records have been added or something...
1
by: Charlie | last post by:
Hello, I have data in an Access table that I would like to export to multiple HTML tables. I would like to split the data in the Access table (about 92,000 records) into multiple HTML...
20
by: Steve Jorgensen | last post by:
Hi all, I've just finished almost all of what has turned out to be a real bear of a project. It has to import data from a monthly spreadsheet export from another program, and convert that into...
2
by: Dimitri | last post by:
PLEASE HELP,I HAVE A DATABSE WITH MULTIPLE RECORDS AS OUTLINED BELOW EMP NO LEVEL NEXTINCREASE WAGETYPE UNIT 1000 1 0 1000 1000 1 0 1002 ...
9
by: Susan Bricker | last post by:
Greetings. I am having trouble populating text data that represents data in my table. Here's the setup: There is a People Table (name, address, phone, ...) peopleID = autonumber key There...
6
by: Alpha | last post by:
I retrieve a table with only 2 columns. One is a auto-generated primary key column and the 2nd is a string. When I add a new row to the dataset to be updated back to the database. What should I...
4
by: bryan | last post by:
Hi all, I am working on a .net 2.0 web app. On page load I display a gridview with a drop down list (Employees), when the user selects from the drop down list, the grid view is filled with...
1
by: KrazyKasper | last post by:
Access 2003 – Multi-Column List Box – Select Multiple Items I have a multi-column (3 columns) list box that works well to select one set of records or all sets of records (based on the first field...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.