SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Parsing and Importing a Report
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mauisys
Starting Member

USA
7 Posts

Posted - 01/08/2013 :  14:58:55  Show Profile  Reply with Quote
We get a report (.txt file) on a daily basis that needs to be imported into a SQL table. The report is formatted with headers and footers and then data in between. Is this possible? How would we go about doing this?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/08/2013 :  23:26:20  Show Profile  Reply with Quote
so far is metadata (columns) is fixed you can use SSIS for transferring file contents to table. FOr automating, create a sql agent job to call and execute ssis package and schedule it to be executed daily

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/09/2013 :  13:31:30  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

so far is metadata (columns) is fixed you can use SSIS for transferring file contents to table. FOr automating, create a sql agent job to call and execute ssis package and schedule it to be executed daily

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



People say that but is it really that easy in SSIS, for example, to import a file that has a format like the following (similar, I believe, to what the op is asking for)? This type of formatted file is a whole lot more common than a lot of people would expect and a whole lot of people have said to use SSIS for such a thing, but I really haven't seen anyone post any decent packages for such a thing. They always seem to venture off into the never-never land of C#, VB, or other tool instead of sticking to SSIS.

File header line 1
File header line 2
File header line 3
File header line 4
File header line 5

co1name,col2name,col3name... etc
col1data,col2data,col3data... etc
... unknown number of similar rows

File footer line 1



--Jeff Moden
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/09/2013 :  22:25:34  Show Profile  Reply with Quote
quote:
Originally posted by Jeff Moden

quote:
Originally posted by visakh16

so far is metadata (columns) is fixed you can use SSIS for transferring file contents to table. FOr automating, create a sql agent job to call and execute ssis package and schedule it to be executed daily

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



People say that but is it really that easy in SSIS, for example, to import a file that has a format like the following (similar, I believe, to what the op is asking for)? This type of formatted file is a whole lot more common than a lot of people would expect and a whole lot of people have said to use SSIS for such a thing, but I really haven't seen anyone post any decent packages for such a thing. They always seem to venture off into the never-never land of C#, VB, or other tool instead of sticking to SSIS.

File header line 1
File header line 2
File header line 3
File header line 4
File header line 5

co1name,col2name,col3name... etc
col1data,col2data,col3data... etc
... unknown number of similar rows

File footer line 1



--Jeff Moden


you can still do this by means of SSIS package so far as you dont have multiple datasets stored inside the file.
If you need to extract any data from one of those header lines, thats when things get a bit tricky as you need additional logic to first extract it out and them merge it later to final resultset from data part.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/10/2013 :  19:18:44  Show Profile  Reply with Quote
Understood and I appreciate the feedback but that's pretty much what I've been talking about. It's really not that easy a thing to do in SSIS especially if you need to distribute the header information.

--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."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/10/2013 :  22:32:12  Show Profile  Reply with Quote
yep...agreed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/11/2013 :  15:44:14  Show Profile  Reply with Quote
quote:
Originally posted by mauisys

We get a report (.txt file) on a daily basis that needs to be imported into a SQL table. The report is formatted with headers and footers and then data in between. Is this possible? How would we go about doing this?

Thanks.



To answer your first question, yes, it's possible.
To answer your second question, the answer is "brute force".

If you actually have an example of the small sample file with the component parts, I'm sure that one of us could demonstrate at least one method for doing it.

Of course, the most fun method will be to find the person that provided the file and then beat the hell out of them for making your life difficult.

--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."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000