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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Parsing and Importing a Report

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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

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

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."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 22:32:12
yep...agreed

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

Go to Top of Page

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."
Go to Top of Page
   

- Advertisement -