Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Unanswered: Loading in .txt file w/ master/detail recs

    SQL Server 2000

    Help! Surely this has happened to others before me. A new customer wants to send updates in a fixed-width txt file in which master and detail rows alternate.

    How do you do this?

    Do you:
    1) Painfully muck around with 100,000 rows in the .txt file first--split it into 2 files, one for master records and one for detail records and then process? If so, what do you use? I'd probably hack at it with a VB Script module in a DTS package.

    or

    2) Is there a way to feed it into 2 tables where rows starting with x go to one table and rows starting with y go to another?

    The plan was to use a fast and dirty DTS package to shove this stuff into a table (probably 2 but we might just toss the stuff we don't need and put in in one) but I'd like some advice on how to proceed.

    Thanks for any suggestions!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It won't be easy.

    Load it into a staging table first. Add a MasterRecordID to the staging table. Then create logic to separate the two types of records. Then mark all the master records with a unique ID. Then mark all the subtable records with their master id. Then write stored procedures that parse the records and insert them into your data tables.

    It is permissible to curse and swear at the jackass that is sending you data like this while you are writing code, but it is best to keep profanity out of your program comments.

    (Actually, there may be a way to do this in DTS. I'm not proficient with DTS, but hopefully someone else on the forum can come up with an alternate solution. I'd be interested to see it...)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    AAaaarrrrrggghhhh!

    Well, at least you made me laugh! Since I'm actually an applications person I'll probably parse that file in VBScript in a DTS package script element. Didn't see a way to parse it using the text file data object.

    Thanks!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I betcha I could give it a shot...

    Post the DDL of both tables, and the record layouts required...

    Actually attach a file...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Are you really that bored?

    Seems like a terribly generous offer. Here's a bit of info if you like:

    Can't send data--it includes personal data for all the employees at a large US company.

    Can't give you the table specs, 'cause we haven't built it.

    The data description is in an .xls file so that doesn't attach--not that you want to start at that point, anyway.

    All Master rows start with 'MSTC0001' and then the SSN. All detail rows start with 'DTLC0001' and then the SSN. There is a header and a trailer row on each file. The file they are using with their current provider is fixed width. However, the rumor is that actually the file they will send when we become their provider will be pipe delimited so it would be 'MST|C|0001|522329752|...' for a master row and 'DTL|C|0001|522329752|...' for the matching detail row.

    Let me know if you have any recommendations. If you have a great idea on how we could parse this with either a stored proc or a DTS package sans VB Script based on the first 3 characters in the line it would be excellent. The easy part will be filling in the rest of the fields.

    Thanks!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett rises to the challenge!

    Fly Girl, at least give Brett a sample of data with names and values disguised to protect the innocent.

    "DTS package sans VB Script"? Why can't you have VB behind your DTS package?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If the maximum record length can be identified or at least guessed, then it's just a matter of following what blindman had sketched out earlier, - BULK INSERT into a table with 2 fields (RecordID identity, FileContents varchar(maxumum recordlength)) After that is done, - you can even omit blindman's additional key-creation-stuff, because you can already determine what child record belongs to what parent.

    Actually your situation is easier than you might think. Here I have to deal with parsing an EDI transaction (tilde-separated continuous data stream) into a table. While doing that the order in which sections are stored MATTERS, and there is no distinction between section headers and section contents... AAAAAAAAAAAARRRRRRRRRRGGGGGGGGGGGGHHHHHHH!!!

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In fact, you don't even need a RecordID, just the FileContents in that staging table.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Are you really that bored?

    Originally posted by Fly Girl
    Seems like a terribly generous offer. Here's a bit of info if you like:
    Not at all...I'm sure you'll be able to modify this....

    Code:
    USE Northwind
    GO 
    
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY, Col2 varchar(10))
    GO
    
    CREATE TABLE myTable00(Col1 int, Col2 int, Col3 varchar(5), Col4 datetime
    	, PRIMARY KEY(Col1, Col2)
    	, FOREIGN KEY (Col1) REFERENCES myTable99(Col1))
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT 'A' UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'D' UNION ALL
    SELECT 'E'
    GO
    
    INSERT INTO myTable00(Col1, Col2, Col3, Col4)
    SELECT 1,1,'ABCDE','1/1/2001' UNION ALL
    SELECT 1,2,'ABCDE','1/1/2001' UNION ALL
    SELECT 1,3,'ABCDE','1/1/2001' UNION ALL
    SELECT 2,1,'ABCDE','1/1/2001' UNION ALL
    SELECT 3,1,'ABCDE','1/1/2001' UNION ALL
    SELECT 4,1,'ABCDE','1/1/2001' UNION ALL
    SELECT 5,1,'ABCDE','1/1/2001'
    GO
    
    SELECT * FROM myTable99
    GO
    SELECT * FROM myTable00
    GO
    
    -- Assume Record Layouts
    -- Master Record
    --	Record Type	CHAR(1)		Value 'M'
    --	Col1		CHAR(5)		Left Padded w/ 0's
    --	Col2		CHAR(10)
    -- Child Record
    --	Record Type	CHAR(1)		Value 'C'
    --	Col1		CHAR(5)		Left Padded w/ 0's
    --	Col2		CHAR(5)		Left Padded w/ 0's
    --	Col3		CHAR(5)
    --	Col4		CHAR(10)	Format MM/DD/YYYY
    
    CREATE VIEW myView99
    AS
    SELECT TOP 100 PERCENT DataRow FROM (
    	SELECT	  Col1, Null As Col2, 1 AS RowOrder
    		,  'M'
    		+  CONVERT(char(5),ISNULL(RIGHT('00000' + CONVERT(varchar(5),Col1),5),''))
    		+  CONVERT(char(10),ISNULL(Col2,''))
    		+  SPACE(20)
    	    AS Datarow
    	  FROM myTable99
    	UNION ALL
    	SELECT	  Col1, Col2, 2 AS RowOrder
    		,  'D'
    		+  CONVERT(char(5),ISNULL(RIGHT('00000' + CONVERT(varchar(5),Col1),5),''))
    		+  CONVERT(char(5),ISNULL(RIGHT('00000' + CONVERT(varchar(5),Col2),5),''))
    		+  CONVERT(char(5),ISNULL(Col3,''))
    		+  CONVERT(char(10),ISNULL(CONVERT(varchar(10),Col4,101),''))
    	    AS Datarow
    	  FROM myTable00
    ) AS XXX
    ORDER BY Col1, Col2, RowOrder
    GO
    
    SELECT * FROM myView99
    GO
    
    master..xp_cmdshell 'bcp Northwind.dbo.myView99 out c:\myView.txt -c -S<servername> -Usa -P'
    GO
    
    CREATE TABLE myData99(Col1 varchar(8000))
    GO
    
    master..xp_cmdshell 'bcp Northwind.dbo.myData99 in c:\myView.txt -c -S<servername> -Usa -P'
    GO
    
    SELECT * FROM myData99
    GO
    
    DROP VIEW myView99
    DROP TABLE myTable00
    DROP TABLE myTable99
    DROP TABLE myData99
    GO
    EDIT: What an idiot...hop no one saw that....
    Last edited by Brett Kaiser; 02-19-04 at 14:30.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Wow!

    Ok I'll hack up a small .txt file with no actual data.

    No real reason to not use VB Script--that's the way I would do it. Just figured that I wouldn't waste someone else's time doing it the same way I would. One of the things I really like about these boards is that I learn new ways to do things.

    A head start on any process would be fantastic, though.
    Attached Files Attached Files

  11. #11
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    THANKS!!!!

    Didn't see all the new replies until after I posted the .txt file.

    I need a bit of time to play with the sproc.

    I also like the idea (if I understand it correctly) of setting up a staging table where column1 = ID, column2 = Master rec as large string, column3 = detail rec as large string then just reading the .txt file and slapping the rows into the appropriate columns.

    Cool stuff!

    Thanks guys!

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well not exactly....

    The tables are not staging tables, they are actually you're data tables.

    No need to move data around for no reason.

    You then build a view off of those tables...making 1 datarow column that matches your spec.

    You also then Ordering information. You need to keep the "keyed" records together, then give them an order with in the bunch.

    Heck you could even make header and trailer records appear at the very top or bottom with something like

    SELECT CHAR('00') AS Col1, CHAR('00') AS Col1, 0 AS Row_Order
    SELECT CHAR('99') AS Col1, CHAR('99') AS Col1, 9 AS Row_Order

    For fixed width stuff (it's a mainframe thing) You need to make sure you handle Nulls, and set the datatypes to CHAR(whatever)

    Good Luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    EDIT: OH And BTW did you cut and paste the code in to query analyzer to see it run?

    You'll actually get to see what's going on...and it'll clean up after itself...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, this is just to give you an idea. I didn't bother with the rest of the fields.

    Code:
    create table tblImport(FileContents varchar(510) null)
    go
    exec master.dbo.xp_cmdshell 'bcp TEST.dbo.tblImport in y:\eligibility_test.txt -T -c'
    go
    select * from tblImport
    go
    create table tblMaster (PKey char(22) not null primary key clustered, fRest varchar(490) not null)
    go
    create table tblChild (CKey char(22) not null primary key nonclustered, PKey char(22) not null, fRest varchar(490) not null)
    go
    alter table tblChild add constraint FK_PKey foreign key (PKey) references tblMaster (PKey)
    go
    insert tblMaster (PKey, fRest)
       select cast(FileContents as char(22)), substring(FileContents, 23, 488) from tblImport where cast(FileContents as char(3)) = 'MST'
    go
    insert tblChild (CKey, PKey, fRest)
       select cast(FileContents as char(22)), m.PKey, substring(FileContents, 23, 488)
          from tblMaster m inner join tblImport i on substring(m.PKey, 5, 17) = substring(FileContents, 5, 17)
          where cast(FileContents as char(3)) = 'DTL'
    go
    select * from tblMaster
    select * from tblChild
    go
    alter table tblChild nocheck constraint all
    go
    alter table tblMaster nocheck constraint all
    go
    update tblChild set PKey = replace(PKey, '|', ''), CKey = replace(CKey, '|', '')
    go
    update tblMaster set PKey = replace(PKey, '|', '')
    go
    alter table tblMaster check constraint all
    go
    alter table tblChild check constraint all
    go

  15. #15
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    I like it

    Haven't run the code in Query Analyzer yet--fixing a bug in a Word Mail Merge that is run off of the app right now. It doesn't like WinXP. I'll get to it soon, though.

    I was thinking that about the header and trailer rows also.

    Right now what we have for a test file is fixed-width. However, the first live file that they send to us is supposed to be pipe delim. Guess we'll wait to be surprised!

    Thanks. This has all been really helpful in considering how to do this. We are a small company that has landed it's first big fish. We're trying to hire/find a cheap SQL Server guru (major non-sequiter 'cheap' and 'SQL Server guru') to handle this stuff, but right now it's up to us applications programmers.

    I really appreciate the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •