Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Import Flat File in SSIS

Posted on 2011-02-15
11
Medium Priority
?
1,038 Views
Last Modified: 2012-05-11
My flat file is ragged right with no column headings but has multiple headers, detail, footers.
header
detail
footer

header
detail
footer

I need to import the detail only and not the header/footers.  I tried to import all and later delete the header and footers but not all the records are getting into my sql table.  Does anyone know how this can be accomplished.  I am new to SSIS and do not know how to set up a conditional split since I've read that this may be a solution.  Appreciate any help ... ps this is urgent.
0
Comment
Question by:bar0822
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 5

Expert Comment

by:jijeesh
ID: 34901410
Are your headr, details and footer info  repeating?  
Can you send a sample input file and what are you expect to extract.

0
 
LVL 40

Expert Comment

by:lcohan
ID: 34901423
If you can do it directly in SQL query the example below should help just keep in mind the path and file is relative on the SQL server box not the client where you run the query:

--Assumes:Usage : exec sp_readTextFile 'c:\autoexec.bat'
--**************************************

Create proc sp_readTextFile @filename sysname
as


    begin
    set nocount on
    Create table #tempfile (line varchar(8000))
    exec ('bulk insert #tempfile from "' + @filename + '"')
    select * from #tempfile
    drop table #tempfile
End
go

0
 

Author Comment

by:bar0822
ID: 34909821
here is a sample
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 5

Accepted Solution

by:
jijeesh earned 2000 total points
ID: 34912877
Attached a sample dtsx package. (you need to rename the extension from .txt to .dtsx). Hope this will be useful.
Copy-of-Package.txt
0
 

Author Comment

by:bar0822
ID: 34928722
I will try it and let you know. thanks
0
 

Author Comment

by:bar0822
ID: 34928809
Can you send me the solution file so I can take a look at it?
0
 

Author Comment

by:bar0822
ID: 34928842
I am running this from SSIS - how do I use your proc sp_readTextFile @filename sysname.

How does that omit the header and footer lines from the text file?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 34929640
SQLCMD has many switches and I only gave an exmaple for you. you should look for more details about SQLCMD at http://msdn.microsoft.com/en-us/library/ms162773.aspx and use the switches you need for your purpose like:

-hheaders
Specifies the number of rows to print between the column headings. The default is to print headings one time for each set of query results. This option sets the sqlcmd scripting variable SQLCMDHEADERS. Use -1 to specify that headers must not be printed. Any value that is not valid causes sqlcmd to generate an error message and then exit.

-scol_separator
Specifies the column-separator character. The default is a blank space. This option sets the sqlcmd scripting variable SQLCMDCOLSEP. To use characters that have special meaning to the operating system such as the ampersand (&), or semicolon (;), enclose the character in quotation marks ("). The column separator can be any 8-bit character.

0
 

Author Comment

by:bar0822
ID: 34932963
thank you so much for all your time and help - I did not know this existed but there will def be a time when I will need to use.  sorry, I did not explain myself correctly - The text file does not have column headers - it is a flat ragged right file - I was looking to extract information about the data in the flat file e.g.
Header000 20110101
Detail information here
Trailer000 4,222 records
Header0000 20110101
Detail information here
Trailer000 3,111 records
I need to extract only the Detail information and send to SQL table through a SQL Execute Task, need to add a count to those records.  I was thinking of a conditional split - extract header/trailer data and send to a file, then count the rows in detail.  I am new to ssis and not sure how to write this.
0
 

Author Comment

by:bar0822
ID: 35097615
here is new file posted revised
Revised-Text-Document.txt
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35097705
--Ok so let's leave the SP for now and just try in SQL to get what you need

Create table #tempfile (line varchar(8000))
exec ('bulk insert #tempfile from "' + 'C:\FolderName\FileName.txt' + '"')
select * from #tempfile where line NOT LIKE 'Header%' or line NOT LIKE 'Trailer%'
drop table #tempfile


if this is what you want then you can put the code in a SSIS T-SQL step and add a INSERT statement into your DB table from the temp table. You obviously can adjust the datatype,number of columns ETC now is all in SQL and easy to work with.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question