Simple Method for Importing Ragged files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Paul Ibison » Simple Method for Importing Ragged files

52 posts, Page 1 of 6

1 2 3 4 5 »»»

Simple Method for Importing Ragged files

Rate Topic

Display Mode

Topic Options

Author

Message

Paul Ibison

Posted Tuesday, March 04, 2008 9:55 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, Visits: 32

Comments posted to this topic are about the item Simple Method for Importing Ragged files

Paul Ibison
Paul.Ibison@replicationanswers.com

Post #464209

Neel Singh

Posted Tuesday, March 04, 2008 11:50 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 1:19 AM
Points: 15, Visits: 40

would it be possible for you to mail me the package please.

Post #464233

Neel Singh

Posted Tuesday, March 04, 2008 11:50 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 1:19 AM
Points: 15, Visits: 40

neel.singh@interactivesa.co.za

Post #464235

brewmanz

Posted Wednesday, March 05, 2008 2:38 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 11:16 AM
Points: 477, Visits: 389

*sigh* ANOTHER misleading title. This is NOT how to import a ragged-right file. It's how to remove unwanted header & trailer records from a perfectly formed NON-ragged-right file. I was hoping for the import of the classic ragged-right file - a variable number of fields in records that are wanted.
BTW Using the usual defaults, record sets can blow up once they reach about 200MB (of input data) with multiple fields of variable text.

Post #464281

paul.ibison

Posted Wednesday, March 05, 2008 3:07 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:26 AM
Points: 14, Visits: 144

True - the title is a little misleading. The problem is that there isn't a 'correct' term for such files and this is the nearest commonly understood one. Anyway, what you need is this link: http://www.sql-server-performance.com/article_print.aspx?id=1056&type=art
Rgds,
Paul Ibison

Post #464295

Jim Russell-390299

Posted Wednesday, March 05, 2008 5:30 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403

Re: " I didn't try using the identity method, as I'm always concerned (probably needlessly) that the order of inserts might not necessarily be guaranteed to be identical to that of the text file..."

Not needlessly at all, I've been bit by that, and after the same import routine had preserved the order some 3-4 runs prior. When you are warned that relational tables have no order, you are completely correct to take the warning seriously!

Post #464349

paul.ibison

Posted Wednesday, March 05, 2008 5:44 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:26 AM
Points: 14, Visits: 144

Hi Jim,

that's reassuring in a sense that my cautious approach was OK:)

BTW we're talking about reading from a text file rather than a table.

Even so I'm still a little mystified by this - if I did this in .NET, the sequence of reading would be consistant. My guess is that in SSIS it is read into a sequence of separate buffers and somehow it's the order of the buffers which is not sequential, but am not sure why this logically should be so.

Rgds,

Paul Ibison

Post #464356

Jim Russell-390299

Posted Wednesday, March 05, 2008 5:49 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403

I don't think it is SSIS, it is the SQL engine. Even though you read the file sequentially, where the rows wind up in the destination table is not guaranteed.
(I got caught by it doing imports into MS Access from .txt files.)

Post #464358

paul.ibison

Posted Wednesday, March 05, 2008 6:54 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:26 AM
Points: 14, Visits: 144

OK - I was hooked into thinking that the buffers were changing the order, but as you rightly point out in the absence of a clustered index on the table, the insertion order is not consistant/guaranteed.
Cheers,
Paul Ibison

Post #464393

pnewhart

Posted Wednesday, March 05, 2008 7:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:01 AM
Points: 267, Visits: 271

I think a combination of omitting the first row and doing a conditional split would do just fine. This the method I use since I receive a number of flat files from legacy systems having header and trailer rows. The conditional split needs to evaluate only one column to see if it's a valid row. Maybe I'm missing something, but I don't understand the reference to manually defining the column names and use of substrings. You don't have to rename the columns and using a substring seems a heck of a lot easier than all of the manipulations as described.

Post #464401

« Prev Topic | Next Topic »

52 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions