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?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...|||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?|||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|||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.|||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|||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.|||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?|||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?|||So... What do I do now?|||holy sh-t...someone else knows COBOL?Yes, and Algol, and Fortran, and Ratfor, and BAL, and...
-PatP|||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...|||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?|||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|||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?|||I don't see 3 tables there, I see only 2 (where's blindman, he usually sees better than me ;))
Everything before OCCURS is your master table, and starting from OCCURS and including REDEFINES (you can redefine every field, its storage will not change) with the pointer to the master table would go to the child table.|||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?
^^
I am in DTS import wizard, and now I just need help understanding how the cobol seperates the columns. For the flatfile, here is the first line (weird formatting with spacing and whatnot) Btw, I did not include the whole copybook.
0002 00059001 001001 1 06A5 1990010101 2020B5000000000054433 00000000000000000000000000000000000000410240000000 000000120A1 0001300000000606860AC000410237216000312507004074+0 4+00-20-10+00RP 0000676000 0000000000000000000 00000000000000000000000 000 0000000000 0000000000000000000 00000000000000000000000 000 0000000000 0000000000000000000 00000000000000000000000 000 0000000000 0000000000000000000 00000000000000000000000 000 0000000000 0000000000000000000 00000000000000000000000 000 0000000000 000000000 10001000 0000000000000 00000000000000000000000000000000000000000000000000 00000000000000000000000000000000000 00 000 00000000000000000000000000000000000000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000014160258062001WDXV0003215000138706090 42001WDXV000918000013840508042001WDXV0000000000138 20238032001QCXV000000000013750273032001WDXV0051500 00WAS 760 AC MADE SPLITS IN 2001, REDUCED AC TO 606 SOLD 3 TIMES IN 2001 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 STRICKLAND THOMAS P D/B/A WAYSIDE RD 0004940000000000000000049400000000606860ACLL 32 59 62 227 LD16 606.86AC01600 00000494000000494000000000000000000000020020715RAI NTREE PROPERTIES 00 00|||Well...the data is fixed width format...
Just import the whole thing as 1 column and do what I mentioned...
Go to Data Transformation Services in Enterprise Manger, right click on the icon, choose all tasks, the choos import data.
Select the source data type as text...and follow the yellow brick road...
PS It's a wizard...
And rdjabarov, you don't think the redefines should be it's own table?|||OK, I have the whole file in a db now, but all as 1 column. What should I do now?|||You need to create a SELECT Statement that matches your layout...
First you need to determine the offset of each field so you can determine the starting position...
Actually make 3 SELECTS...make the first one up to the OCCURS columns...
What's the name of the table and what did you name the column?
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|||I was able to get the flatfile into a database using your suggestion, but now there is like wordwrapping going on. Also, Ive tried the SELECT statement, and it seems to work well on the first column, but how do I continue to do the rest of the columns?|||Word Wraping?
Where are you doing this? If it's in QA look at your settings...
And you need to count your offsets and use the starting position for each filed with the length as defined in your layout...|||OK, ya gotta work with me here...you reading all of this?
It's going to look like...
SELECT F-MASTER-PARCEL-NUMBER = SUBSTRING(Col1,1,23)
, F-CARD-NUM = SUBSTRING(Col1,24,3)
, F-NUM-CARDS = SUBSTRING(Col1,27,3)
, F-APPR-PIN-NUMBER = SUBSTRING(Col1,30,22)
, F-USER-FILLER-1 = SUBSTRING(Col1,52,8)
, F-E-CODE = SUBSTRING(Col1,60,1)
, F-USER-FILLER-2 = SUBSTRING(Col1,61,1)
, ect
FROM myTable99
Just open the txt file in Excel
The offsets are calculated like =SUM(C3+F3)-1|||OK. The query above works great! Thanks for all that. As for the wordwrapping, these are the steps I go through from DTS.
Choose Data Source -> Source = Text File
Select File Format -> Format = Fixed Field
File Type = ANSI
Skip Rows = 0
Row delimiter = {CR}{LF}
Now, right after that where it asks for the column positions, I get a preview of 3 lines. The first two are exactly what I want to see, yet the 3rd line looks like it has been wordwrapped. I have also tried setting "Row delimiter" to none and setting the Row Width either at 8000, or inbetween the black bars (which is what I am guessing represents a break, or enter in the data).|||Oh yah, can you explain the OCCURS and REPLACES again, being in different tables?|||Does each record have a unique key?
Id it does then for the occurs you would do
SELECT KeyName = SUBSTRING(Col1,n,m)
, OCCURS_FIELD_1 = SUBSTRING(Col1,n,m)
, OCCURS_FIELD_2 = SUBSTRING(Col1,n,m)
, Ect
UNION ALL
SELECT KeyName = SUBSTRING(Col1,n,m)
, OCCURS_FIELD_1 = SUBSTRING(Col1,n,m)
, OCCURS_FIELD_2 = SUBSTRING(Col1,n,m)
, Ect
Do that 6 time, 1 for each offset|||And I think I would forget about the redefines and just leave it as CHAR(17)
(mostly because it's be a pain...you'd have to check that the datatype of a column is numeric or not and the isolate the sets...still...)
for the occurs you need to have 6 union alls. I set it up so you can just add the offset
SELECT KEY-FIELDS = SUBSTRING(Col1,n,m)
--F-LAND-LINE OCCURS 6 TIMES.
, F-USE-CODE = SUBSTRING(Col1,151,4)
, F-ZONING = SUBSTRING(Col1,155,6)
, F-UNIT-PRICE = SUBSTRING(Col1,161,9)
, F-NUMBER-UNITS = SUBSTRING(Col1,170,10)
, F-LAND-LINE-TYPE = SUBSTRING(Col1,180,2)
, F-LAND-LINE-VALUE = SUBSTRING(Col1,182,9)
, F-FRONT = SUBSTRING(Col1,195,5)
, F-DEPTH-XX = SUBSTRING(Col1,196,5)
, F-DEPTH-FACT = SUBSTRING(Col1,201,4)
, F-D-TAB = SUBSTRING(Col1,205,1)
, F-COND-FACT = SUBSTRING(Col1,206,3)
, F-OTHER-ADJ = SUBSTRING(Col1,209,17)
, F-LAND-NOTES = SUBSTRING(Col1,225,10
, F-ADJ-UNIT-PRICE = SUBSTRING(Col1,235,10)
FROM myTable99
UNION ALL
SELECT KEY-FIELDS = SUBSTRING(Col1,n,m)
--F-LAND-LINE OCCURS 6 TIMES.
, F-USE-CODE = SUBSTRING(Col1,151+95,4)
, F-ZONING = SUBSTRING(Col1,155+95,6)
, F-UNIT-PRICE = SUBSTRING(Col1,161+95,9)
, F-NUMBER-UNITS = SUBSTRING(Col1,170+95,10)
, F-LAND-LINE-TYPE = SUBSTRING(Col1,180+95,2)
, F-LAND-LINE-VALUE = SUBSTRING(Col1,182+95,9)
, F-FRONT = SUBSTRING(Col1,195+95,5)
, F-DEPTH-XX = SUBSTRING(Col1,196+95,5)
, F-DEPTH-FACT = SUBSTRING(Col1,201+95,4)
, F-D-TAB = SUBSTRING(Col1,205+95,1)
, F-COND-FACT = SUBSTRING(Col1,206+95,3)
, F-OTHER-ADJ = SUBSTRING(Col1,209+95,17)
, F-LAND-NOTES = SUBSTRING(Col1,225+95,10
, F-ADJ-UNIT-PRICE = SUBSTRING(Col1,235+95,10)
FROM myTable99
UNION ALL
SELECT KEY-FIELDS = SUBSTRING(Col1,n,m)
--F-LAND-LINE OCCURS 6 TIMES.
, F-USE-CODE = SUBSTRING(Col1,151+190,4)
, F-ZONING = SUBSTRING(Col1,155+190,6)
, F-UNIT-PRICE = SUBSTRING(Col1,161+190,9)
, F-NUMBER-UNITS = SUBSTRING(Col1,170+190,10)
, F-LAND-LINE-TYPE = SUBSTRING(Col1,180+190,2)
, F-LAND-LINE-VALUE = SUBSTRING(Col1,182+190,9)
, F-FRONT = SUBSTRING(Col1,195+190,5)
, F-DEPTH-XX = SUBSTRING(Col1,196+190,5)
, F-DEPTH-FACT = SUBSTRING(Col1,201+190,4)
, F-D-TAB = SUBSTRING(Col1,205+190,1)
, F-COND-FACT = SUBSTRING(Col1,206+190,3)
, F-OTHER-ADJ = SUBSTRING(Col1,209+190,17)
, F-LAND-NOTES = SUBSTRING(Col1,225+190,10
, F-ADJ-UNIT-PRICE = SUBSTRING(Col1,235+190,10)
FROM myTable99
--ect
I gotta start charging soon...sql
没有评论:
发表评论