Can this messed up text file be loaded into a table w/o using a Parser.

Question

Can this messed up text file be loaded into a table w/o using a Parser.

//Ravi

SSCrazy

Points: 2612
More actions
January 29, 2008 at 10:02 am

#121913

Hi,
At my work, other people write file parser in C# to parse this file. I am wondering if this file can be loaded into a table using SSIS without any parser.
I am attaching a screenshot of the file and actual file.
I am new to SSIS, so I might not know as much as you know.
Thanks.
Rav.
------------
🙂

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, you could insert it into a table, but I'm not sure that would help much.

the report has 48 lines of header information, that may or may not be a constant number of lines, and then the report itself repeats 5 rows of column information every 52 lines.

you could do it in SQL, but it'd be better to manipulate it clean it up in a real programming language first.

every row of data has a subtotal row that starts with asterisks I think.

You'd have to write a lot of TSQL just to remove header and extraneous data.

Why can't you get the data directly from teh data source, instead of translating a report? it's coming from a third party I assume?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

//Ravi SSCrazy Points: 2612 More actions · Answer 2

Lowell (1/29/2008)
Why can't you get the data directly from teh data source, instead of translating a report? it's coming from a third party I assume?

I wish I wish that was possible, it would make life a lot easier. But we have about 100 different client and they just FTP their files to a site. They all use totally different system. Plus my company want to "accomodate" them. Anyways, it's a long story. Thanks for clearing that up. I guess i'll keep using programming language.

Rav.

------------
🙂

Dave23 Hall of Fame Points: 3903 More actions · Answer 3

As a professional caveat, I'd advise against attaching files of live patient data to posts. You should redact the names or other personally identifiable information of any kind before posting something like this, or construct a representative sample file of identical structure. If possible, you should probably remove this attachment from your post. I'm not trying to be disparaging, but disclosing this kind of data can open you up to a number of very unpleasant penalties depending on your locale.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

I'd second D smith's concern as the the HIPAA violation you have going on. I'd yank that image off of there before you get yourself into trouble.

This might be an unusual solution to your issue, but here goes anyway.

Invest in a product called MONARCH, from DataWatch software. It's designed specifically to handle mainframe reports like this one from SMS you just posted, and will allow you to screen scrape stuff out of it and import it into CSV, ODBC data sources, etc.... No hard C# coding, no ugly coding to scrub the stuff, really. Once you get the gist of the product, you can chew through importing that in a few minutes.

You can define the page header and footer areas, group headers (if need be), multi-line records, etc...Also allows you to do pre and post-database lookup calls, filters the text on the report, allows for on the fly aggregation, etc... In short - worth every penny. It will pay for itself in the first report you have to model.

And - it's pretty performant - I've extracted data out of 10,000 page reports, and dumped it directly into a data table in SQL server in 45 secs to 2 minutes.

Trust me - it beats the C# code you'd have to write.

And no - I'm not associated with DataWatch Software in any way other than having used their product as a customer.

Edit: Changed the name of the software company - it's DataWatch, not Executive Software like originally posted. Told you I didn't work for them!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

//Ravi SSCrazy Points: 2612 More actions · Answer 5

Matt and Dsmith,

Thanks for the suggestion. My stupid head didn't think about customer data. I just removed both of the files.

Matt, we do have Monarch. But I don't know why my supervisor freaks out over using monarch for text files. It's kinda funny, he always says, "Monarch is for PDF files Only." Anyways, I gotta talk to him again.

Thanks for your replies.

Rav.

------------
🙂

Crispin Proctor SSCoach Points: 18161 More actions · Answer 6

Where's the screen shot?

ravirobin (1/29/2008)
Hi,
At my work, other people write file parser in C# to parse this file. I am wondering if this file can be loaded into a table using SSIS without any parser.
I am attaching a screenshot of the file and actual file.
I am new to SSIS, so I might not know as much as you know.
Thanks.
Rav.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

//Ravi SSCrazy Points: 2612 More actions · Answer 7

Crispin Proctor (1/30/2008)

Where's the screen shot?

I removed the screen shot and the file. It had some information that I shouldn't be posting. See the earlier posts...

Rav.

------------
🙂

Crispin Proctor SSCoach Points: 18161 More actions · Answer 8

ahh that's ok. The government looses CD's with our data on anyway. The details you posted are probably already floating around anyway... 😀

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!