Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: Flatfile into MSSQL

    I currently have a flatfile with a seperate COBOL copybook. I need to be able to import all of it correctly into a db in mssql. Are there any *free programs that will turn the flatfile into some form for insertion into the db? What about csv format into the db?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No need...just use bcp with a format file

    What's the lrecl?

    You could bcp the wole thing in as 1 column and the parse it to it's final destination...but creating a format file is the way to go....
    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.

  3. #3
    Join Date
    Aug 2004
    Posts
    9
    Code:
    00002001  FLATFILE-REC.
    000030    03  F-MASTER-PAR.
    000040        05  F-MASTER-PARCEL-NUMBER	PIC X(23).
    000050	      05  F-CARD-NUM			PIC XXX.
    000060    03  F-NUM-CARDS			PIC XXX.
    000070	  03  F-APPR-PIN-NUMBER			PIC X(22).
    000080    03  F-PARCEL-LOCATOR.
    000090        05  F-USER-CODES.
    000100            07  F-USER-FILLER-1		PIC X(8).
    000110            07  F-E-CODE                  PIC X.
    000120            07  F-USER-FILLER-2           PIC X.
    000130    03  F-APPR-DATE                       PIC 9(8).
    000140    03  F-A-N-CODES.
    000150        05  F-APPR-BY			PIC XX.
    000160        05  F-NEW-NOTICE			PIC XX.
    000170    03  F-APPR-UNUSED			PIC 9(05).
    000180    03  F-IMPROVEMENT-CODES.
    000190        05  F-IMP-1.
    000200            10  F-IMP-1-N			PIC 99.
    000210        05  F-IMP-2.
    000220            10  F-IMP-2-N          	PIC 99.
    000230    03  F-ACCOUNT-NUM          		PIC X(12).
    000240    03  F-SOURCE-INFO-CODE		PIC X.
    000250    03  F-SUMMARY-VALUES.
    000260        05  F-BLDG-DEPR-VALUE		PIC 9(11).
    000270        05  F-BLDG-RCN-VALUE		PIC 9(11).
    000280        05  F-XFOB-DEPR-VALUE		PIC 9(11).
    000290        05  F-LAND-VALUE			PIC 9(11).
    000300	      05  F-SPECIAL-LAND-VALUE          PIC 9(11).
    000310    03  F-LAND-LINE-SUPERSET.
    000320      04  F-LAND-LINE OCCURS 6 TIMES.
    000330        05  F-USE-CODE			PIC X(4).
    000340        05  F-ZONING			PIC X(6).
    000350        05  F-UNIT-PRICE			PIC 9(7)V99.
    000360        05  F-NUMBER-UNITS		PIC 9(7)V9(3).
    000370        05  F-LAND-LINE-TYPE		PIC X(2).
    000380        05  F-LAND-LINE-VALUE		PIC 9(9).
    000390        05  F-FRONT			PIC 9(5).
    000400        05  F-DEPTH-XX                    PIC 9(5).
    000410        05  F-DEPTH-FACT			PIC 9V999.
    000420        05  F-D-TAB			PIC X.
    000430        05  F-COND-FACT			PIC 9V99.
    000440        05  F-OTHER-ADJ			PIC X(17).
    000450        05  F-OTHER-ADJ-O11O REDEFINES F-OTHER-ADJ.
    000460            10  F-SIGN1			PIC X.
    000470		  10  F-ROAD-FRONTAGE		PIC 99.
    000480		  10  F-SIGN2			PIC X.
    000490		  10  F-TYP-ACCESS-OTHER	PIC 99.
    000500		  10  F-SIGN3			PIC X.
    000510		  10  F-LOCATION-FACTOR		PIC 99.
    000520		  10  F-SIGN4			PIC X.
    000530 		  10  F-TOPO-FACTOR		PIC 99.
    000540		  10  F-SIGN5			PIC X.
    000550		  10  F-SHAPE-FACTOR		PIC 99.
    000560		  10  F-TYPE-RD			PIC XX.
    000570	      05  F-LAND-NOTES			PIC X(10).
    000580	      05  F-ADJ-UNIT-PRICE		PIC 9(7)V999.
    There is part of the copybook file starting with the FLATFILE-REC at the top and a Filler at the bottom. How do I go about changing this into a format file?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you know what table(s) you need to put the data in?

    Because I'm thinking this is 3 tables.

    You would have a parent (the main master part) and 2 children.

    1 For the Occurs, and another for the redefines..

    And the more I think about it...I would set up a staging environment and use sql to so the work....

    You can then also audit the data...

    Got any low values?

    Is this a 1 time thing *, or does it need to be repeated on a scheduled basis?


    Do you know what DTS is?

    Hell, I'd just bcp it in to a a table with 1 varchar(8000) column

    How many rows are we talking about?

    * Biggest lie in the industry
    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
    Aug 2004
    Posts
    9
    Well, I was just going to place it into one table. This is definately a one time thing. I don't know what DTS is, and I am even surprised to be understanding any of this at all. I am pretty new to SQL/COBOL, but I am trying to learn. For the amount of rows, there are 38629 in total. The flatfile is around 100MB in size, and I have no chance on actually looking at the data through Excel or Access, but currently use KEDIT to view as a text file and to handle the size.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    We are talking about SQL Server correct?

    Do you have SQL Server Client side tools installed?

    Do you know what Enterprise manager or Query Analyzer is?

    You're going to need them...

    Do you have access to the sql server drives? Like can you map to a share?

    You should put the file you want to import there.

    And OK, you say 1 table...you can get away with the occurs part and have 6 repetitive columns...

    But what about the redefines.....can't do that unless you make everything char data
    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.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    REDEFINES allows you to reformat the same storage area.

    OCCURS means that fields from line 330 through 580 will be repeated in the same order 6 times.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Aug 2004
    Posts
    9
    Yes, I have all the tools via client side, and have an actual test server running on the same machine (So I don't fubar anything on the live server). 1 table was what my supervisor was looking for, but it seems like 3 would be a better solution. So what do I do from here?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    REDEFINES allows you to reformat the same storage area.

    OCCURS means that fields from line 330 through 580 will be repeated in the same order 6 times.

    holy sh-t....someone else knows COBOL?
    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
    Aug 2004
    Posts
    9
    So... What do I do now?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    holy sh-t....someone else knows COBOL?
    Yes, and Algol, and Fortran, and Ratfor, and BAL, and...

    -PatP

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Brett Kaiser
    We are talking about SQL Server correct?

    Do you have SQL Server Client side tools installed?

    Do you know what Enterprise manager or Query Analyzer is?

    You're going to need them...

    You need to answer the question.....

    BUT

    I would create a table in Query Analyzer

    CREATE TABLE myTable99(Col1 varchar(8000))
    GO

    I would ftp the file to the SQL Server server...please don't use IND$FILE 100 MG would take a awhile

    I would then bcp the data in

    EXEC master..xp_cmdshell 'bcp dbname.dbo.myTable99 IN c:\filelocation\filename -Sservername -Uuserid -Ppassword -c'

    Then I would create a SELECT Statement using SUBSTRINGs

    SELECT SUBSTRING(col1,1,32) AS F-MASTER-PARCEL-NUMBER
    ,ect for every column

    I might even do conversion to the correct data types...

    The I'd have a look

    Then I would do a

    SELECT...all your column defenitions
    INTO myNewTable99
    FROM myTable99

    Which would create your final table

    That's really how I would do it.

    BUT!

    I think most people would use DTS.....in Enterprise manager...

    It's a lot like importing data in to Access..but much more robust...
    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
    Quote Originally Posted by Pat Phelan
    Yes, and Algol, and Fortran, and Ratfor, and BAL, and...

    -PatP

    You left off Assembler and PL1...

    What was that language we used to code on a commodore 64?
    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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I did leave off PL/1, even though I'm pretty fair at it (they still use it at the University!!!). You must have missed BAL.

    I wrote Commodore Basic and 6502 Assembler on the Commodore, although I've always preferred the Apple ][.

    -PatP

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    There was that, and this

    http://www.atarimagazines.com/compute/issue55/logo.php

    And what'dya think...

    Budster should install the clientside tools and dts the sucker in?
    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.

Posting Permissions

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