Importing a text file

Question

Importing a text file

grtn316

Ten Centuries

Points: 1239
More actions
December 26, 2007 at 4:15 pm

#177806

I'm trying to figure out how to import a text file into SQL Server. I've found examples but my text file is a bit different...I've attached the file for anyone to attempt to figure out.
You will notice that some column names are followed by a ":" and others are just a numeric values that belong in its own column. The info under "Entity Description...." should have a separate row but with the general information above it in each row.
Anyone have any ideas, I'm really at a loss...

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Step 1, I would think, is to hunt down the vendor that provides you with the information... and then yank their tonsils out through their nose. Wow! They really didn't do you any favors!

This is going to boil down to pretty much the equivelent of "screen scraping"... in fact, it looks like the output from a screen. You'll need to import the data into a single wide column and then use combinations of PATINDEX and SUBSTRING to glean the data. Not much else you can do with stuff like this.

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

Rick Mills Old Hand Points: 391 More actions · Answer 2

I really like Jeff's method of Tonsil removal...painful!!

But like he said, this isn't a very fair txt file. In the past, I've used products like Monarch to extract data like this. You can extract it to a txt file or an excel spreadsheet...that is if the data is consistent throughout the file. No quick and dirty fix here...sorry!

Rick

Rick Mills, MT(ASCP)
Analyst / Programmer
Indiana Health Information Exchange
rmills@ihie.com

grtn316 Ten Centuries Points: 1239 More actions · Answer 3

haha! I was talking to the department manager about doing that yesterday. I really don't know what some people are thinking supplying their clients with such ill formatted documents.

Robert O'Byrne SSCommitted Points: 1967 More actions · Answer 4

Looks like you're being handed a report or screen copy. You can try to run 7 different imports to stagin tables, 1 for each report header line, then one for the main table data. Then use a stored procedure to combine the header tables and data table and insert into your final table. SSIS lets you skip a specific number of header records and may allow you to limit your reads to 1 row per each header import.

However, it only takes one small change to destroy all your import preparation. If the report producer adds a page count, time stamp, or even if a line of data rolls over to a second line.

I would advise getting the sender to clean up as much as they can before you tackle this because your solution will fall apart using this report data.

grtn316 Ten Centuries Points: 1239 More actions · Answer 5

Due to the volume of reports I will be facing like this (pdf format included), I think I'm going to look into the Monarch software as it seems a lot easier to extract the data needed. If there is another software that does the same thing that may be better, please let me know.

Thanks!

Joe B-478020 SSC Eights! Points: 966 More actions · Answer 6

I don't know where your reports are coming from but I would suggest contacting the person who is sending the report and ask that they export the data instead.

They will undoubtly say, that's what our system does or that's how we do it or I can't do that. The bottom line is, you don't want a report, you want data to be imported into a database (i.e another system.)

Politely ask for an IT contact.

I've been involved in projects like this and often if you can cut through the political red tape and talk to a techie, they can extract, export or dump the data in a more usable format with little or no additional effort.

Sometimes vendors charge for custom programming and that may be cheaper than your time, effort and third party software.

Just a thought,

Joe

grtn316 Ten Centuries Points: 1239 More actions · Answer 7

Joe Barbian (12/28/2007)
I don't know where your reports are coming from but I would suggest contacting the person who is sending the report and ask that they export the data instead.
They will undoubtly say, that's what our system does or that's how we do it or I can't do that. The bottom line is, you don't want a report, you want data to be imported into a database (i.e another system.)
Politely ask for an IT contact.
I've been involved in projects like this and often if you can cut through the political red tape and talk to a techie, they can extract, export or dump the data in a more usable format with little or no additional effort.
Sometimes vendors charge for custom programming and that may be cheaper than your time, effort and third party software.
Just a thought,
Joe

Obtaining these reports/sometimes exports is not part of my job and isn't structured to ever possibly be so. The information is obtained from a 3rd party using a 3rd party software. So, I would have to get in contact with the vendor of our 3rd party report supplier (Each report is from a different supplier) and figure things out. Then I would have to contact the people we get the report from and hope they have an IT guy with database experience or walk someone through extracting the data. All of a sudden other variables come into play and it just turns into 1 big mess. More times than not, when request a format that is manageable we receive exactly what I have posted.

Its something we have to deal with until the other people get their act together. 🙁