| Author |
 Topic  |
|
|
mauisys
Starting Member
USA
7 Posts |
 Posted - 01/08/2013 : 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
India
47099 Posts |
Posted - 01/08/2013 : 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 MVP
http://visakhm.blogspot.com/
|
 |
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/09/2013 : 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 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 01/09/2013 : 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 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/
|
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/10/2013 : 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
India
47099 Posts |
Posted - 01/10/2013 : 22:32:12
|
yep...agreed
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/11/2013 : 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." |
|
|
| |
Topic |
|
Parsing and Importing a Report
