Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mauisys
Starting Member
7 Posts |
Posted - 2013-01-08 : 14:58:55
|
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
52326 Posts |
Posted - 2013-01-08 : 23:26:20
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 13:31:30
|
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 MVPhttp://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 1File header line 2File header line 3File header line 4File header line 5co1name,col2name,col3name... etccol1data,col2data,col3data... etc... unknown number of similar rowsFile footer line 1 --Jeff Moden |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 22:25:34
|
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 MVPhttp://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 1File header line 2File header line 3File header line 4File header line 5co1name,col2name,col3name... etccol1data,col2data,col3data... etc... unknown number of similar rowsFile 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 MVPhttp://visakhm.blogspot.com/ |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-10 : 19:18:44
|
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." |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-10 : 22:32:12
|
yep...agreed------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-11 : 15:44:14
|
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." |
|
|
|
|
|
|
|