Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Simple Method for Importing Ragged files Expand / Collapse
Author
Message
Posted Thursday, March 6, 2008 9:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Jeff Moden (3/6/2008)

I don't believe it was meant to... wasn't advertised that way except maybe by a slightly misleading title.


Yes, catchy title. When I first clicked on it I actually expected it would be about the type of file I was asking about. The author's right though, I've never had any need to solve this very specific problem. I was kind of wondering how common this specific problem is compared to, say, ANSI X12 EDI and other formats where simply stripping headers and footers is not an option?
Post #465628
Posted Thursday, March 6, 2008 9:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:05 PM
Points: 35,832, Visits: 32,505
Mike C (3/6/2008)
Yes, catchy title. When I first clicked on it I actually expected it would be about the type of file I was asking about. The author's right though, I've never had any need to solve this very specific problem. I was kind of wondering how common this specific problem is compared to, say, ANSI X12 EDI and other formats where simply stripping headers and footers is not an option?


Heh... guess it depends on what business you're in... I've been fortunate in not ever having to mess with EDI files. I have had to do exactly what's in this article a couple of dozen times. Not necessarily using the methods in this article each time, but same problem files.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #465632
Posted Thursday, March 6, 2008 9:36 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Jeff Moden (3/6/2008)
[quote]Mike C (3/6/2008)

Heh... guess it depends on what business you're in... I've been fortunate in not ever having to mess with EDI files. I have had to do exactly what's in this article a couple of dozen times. Not necessarily using the methods in this article each time, but same problem files.


I guess I should have suspected, but I've gotten used to working with file formats where the headers and footers (if there are any) are used to group detail lines for specific reasons. I guess there's still a proliferation of headers and footers in files out there with the sole purpose of getting in the way to make the ETL process harder :)
Post #465636
Posted Wednesday, March 19, 2008 4:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 8, 2010 4:57 PM
Points: 49, Visits: 49
Is it possible to use SSIS to import this same flat file if it doesn't have a ROW delimiter?

I get these files which are basically text streams and they are only delimited by record.

I've written a utlity to handle it now (it's slow and difficult to manage on all my remote sites) but is there anything already in SSIS that would just delimit the row at a specified length?

Perhaps there's a different approach that doesn't require an external utility?



Post #471996
Posted Wednesday, March 19, 2008 5:11 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
johnr (3/19/2008)
Is it possible to use SSIS to import this same flat file if it doesn't have a ROW delimiter?

I get these files which are basically text streams and they are only delimited by record.

I've written a utlity to handle it now (it's slow and difficult to manage on all my remote sites) but is there anything already in SSIS that would just delimit the row at a specified length?

Perhaps there's a different approach that doesn't require an external utility?





You can use the Bulk Insert task with a BCP format file to import fixed-length record files. You can probably do it in the data flow task as well with some transformations, although I haven't had occasion to do that myself yet.
Post #472010
Posted Wednesday, March 19, 2008 5:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 8, 2010 4:57 PM
Points: 49, Visits: 49
I'm not sure that works because it's not a fixed width file, it's a chunk of text that may not have any delimiters in it at all and BCP still needs to know the ROW delimiter? If I'm wrong please do correct me, it would be very helpful.


I guess an easier way to ask this question is how would you take a flat file that has these three lines of text below:

TEST1 ABC
TEST2
TEST3 DEFG

and insert them into a table using SSIS with the ROW delimiter being every 3rd character so that your rows in your table would be:

TES
T1
ABC
TES
T2
TES
T3
DEF
G
Post #472017
Posted Wednesday, March 19, 2008 7:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Hi johnr,

The sample you posted appears to have row delimiters in it. If there are no row delimiters at all, and the file looks like this:

TEST1ABCTEST2DEFTEST3   TEST4GHI

Then you'll probably have some problems. If the file looks like this:

TEST1ABC
TEST2DEF
TEST3
TEST4GHI

And there actually are row delimiters, then the BCP format file trick should work. What you seem to be indicating though, is that you want to essentially rotate the columns and rows. I believe there's an SSIS transformation to do something similar, although if not you can bulk load into a temp table and perform a few INSERT...SELECT FROM... statements to move the data from the temp table into your permanent table pretty easily.
Post #472034
Posted Thursday, March 20, 2008 10:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 8, 2010 4:57 PM
Points: 49, Visits: 49
yeah i realized after i posted this that i included row delimiters in the example but there really aren't in the file i'm talking about. it's to the same end though.

i'm after an ssis method to import the file into a table based on a column delimiter of every (n) characters.

appreciate the feedback.

thanks so much,

-j
Post #472378
Posted Thursday, March 20, 2008 10:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
You could load the entire file into a single VARCHAR(MAX) column and use SUBSTRING to split it up on the server or you may be able to find some functionality in SSIS that would essentially substring the data out at fixed-width positions... I'm just not 100% on what the best SSIS functionality to do that would be.
Post #472398
Posted Monday, May 19, 2008 2:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 4, 2011 1:04 PM
Points: 1, Visits: 11
Hello,

Would it be possible to email me the pacakge created in this example? I would like to use it as a guide since I am new to developing SSIS packages.

Email: tcgeeks@live.com

Thanks,
Assudad
Post #503128
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse