 Re: Flast File to SQL2k -
05-03-2004
, 12:43 PM
Another post solved this wonderfully. See below:
************************************************** ********************' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim fsoi, fi, fsoo, fo, fivar, fivarl, FormType, FormNbr
'Open Input File
Set fsoi = CreateObject("Scripting.FileSystemObject")
Set fi = fsoi.OpenTextFile("D:\VBScript\inputfile.txt", 1, True, TristateFalse)
'Open Output File
Set fsoo = CreateObject("Scripting.FileSystemObject")
Set fo = fsoo.OpenTextFile("D:\VBScript\Output.txt", 2, True, TristateFalse)
Do While Not fi.AtEndOfStream
fivar = fi.Readline
fivarl = Left(fivar, 1)
Select Case fivarl
Case "!"
FormType = Trim(Mid(fivar, 8, 10))
Case ""
Case "."
Case Else
FormNbr = Trim(fivar)
fo.WriteLine FormType & "," & FormNbr
End Select
Loop
fi.Close
fo.Close
Main = DTSTaskExecResult_Success
End Function
Input File (D:\VBScript\inputfile.txt):
!FORM: 1A
306420506
306420514
306420522
306420530
306420548
306420555
306420563
!FORM: 8A
86420548
86420555
86420563
!FORM: 99
9420548
96420555
96420563
Output file (D:\VBScript\Output.txt):
1A
306420506
1A
306420514
1A
306420522
1A
306420530
1A
306420548
1A
306420555
1A
306420563
8A
86420548
8A
86420555
8A
86420563
99
9420548
99
96420555
99
96420563
"Joe Horton" <horj235 (AT) lni (DOT) wa.gov> wrote
I have a mainframe extract that we aren't allowed to change that sends us a flat file in the format at bottom. Any suggestions how to parse the flat file so we can create a SQL table as: At very bottom is the current SP we have that is working - but seems way to complex to accomplish this:
SQL Table Destination:
FormType: FormNbr:
1A 306420506
1A 306420514
23A 886421298
23A 886421306
993B 773
993B 774
993B 775
Text File Source:
!FORM: 1A
306420506
306420514
306420522
306420530
306420548
306420555
306420563
...
!FORM: 23A
886421298
886421306
...
!FORM: 993B
773
774
775
...
--This SP is ran after above flat file is transformed straight into a work table called dbo.Oshan_Alpha
CREATE PROCEDURE dbo.p_oshan_alpha
AS
--************************************************** **
-- $Modtime: 4/28/04 4:29p $
--
-- Description: This procedure takes the OSHAN.txt data from IMIS
-- and assigns the WIN.dbo.OSHAN.oshan_key_code for each form Number
-- passed from IMIS. This is accomplished by extracting the last
-- three characters from the first row containing "!FORM" in bytes
-- 1 thru 5. These three characters are then used to find the WIN
-- Form Number stored in WIN.dbo.CNTL.cntl_code by selecting the CNTL
-- row with cntl_alt_osha_code = the three charactrers extracted, and
-- cntl_list_name = 'keycode'. The Form Number along with the actual
-- OSHA Numbers following the '!FORM' row, are then inserted into the
-- OSHAN table, along with a 0 in oshan_used_flg.
--
-- It will update the oshan_used_flg from 0 to 1 on the oshan table
-- for any oshan_num that has not been marked as used but was found
-- to be in use.
--
--************************************************** **
DECLARE @Start_Time CHAR(8),
@End_Time CHAR(8),
@Form_Num VARCHAR(3),
@Oshan_Alpha_ID INT,
@Next_Oshan_Alpha_ID INT,
@Form_Recs INT,
@Rows_Inserted INT,
@Oshan_Count INT,
@Cntl_Form VARCHAR(3)
SELECT @Rows_Inserted = 0
SELECT @Oshan_Count = 0
SELECT @Start_Time = CONVERT (CHAR (8), GETDATE(), 108)
-- First thing is to delete all rows where we have no usefull data
DELETE dbo.Oshan_Alpha
WHERE Oshan_Alpha_Num IS NULL
-- Now we are getting the first row with the value "!FORM' in the first five
-- bytes of the field, and saving the sequential ID in a save area.
SELECT @Oshan_Alpha_ID = MIN(Oshan_Alpha_ID)
FROM dbo.Oshan_Alpha
WHERE Oshan_Alpha_Num LIKE '!FORM%'
-- Now we are getting the second row from our OSHAN text file with
-- the value "!FORM' in the first five bytes of the field, and saving
-- the sequential ID in a save area. This gives us a from and to ID
-- so we can process all rows between the first and second "!FORM" rows
-- passed from OSHA IMIS.
SELECT @Next_Oshan_Alpha_ID = MIN(Oshan_Alpha_ID)
FROM dbo.Oshan_Alpha
WHERE Oshan_Alpha_Num LIKE '!FORM%'
AND Oshan_Alpha_ID > @Oshan_Alpha_ID
-- If we did not find a second "!FORM" row, we will set our through value
-- with the highest ID we have on our table + 1. This will cause us to process
-- all the remaning data on the table.
If @Next_Oshan_Alpha_ID IS NULL
SELECT @Next_Oshan_Alpha_ID = MAX(Oshan_Alpha_ID) + 1
FROM dbo.Oshan_Alpha
-- Now we will loop through our text table, inserting new OSHA Numbers into the
-- OSHAN table in groups of 0 to N (where N is unknown but greater than
-- or equal to 0). We then do some totals, set up our from and to ID's for our next
WHILE @Oshan_Alpha_ID IS NOT NULL
BEGIN
-- We need to determine the oshan_key_code value to store on the OSHAN
-- This requires two selects
SELECT @Form_Num = SUBSTRING(Oshan_Alpha_Num, 7,3)
FROM dbo.Oshan_Alpha
WHERE Oshan_Alpha_ID = @Oshan_Alpha_ID
SELECT @Cntl_Form = cntl_code
FROM dbo.Cntl
WHERE cntl_alt_osha_code = LTRIM(RTRIM(@Form_Num))
AND cntl_list_name = 'keycode'
-- If @Cntl_Form is NULL we have a bad form ID, so we will not insert these
-- into the OSHAN table.
IF @Cntl_Form IS NOT NULL
BEGIN
-- If @Cntl_Form is NULL we have a bad form ID, so we will not insert these
-- into the OSHAN table.
INSERT dbo.OSHAN
(oshan_key_code,
oshan_num,
oshan_used_flg)
SELECT @Cntl_Form,
CAST(Oshan_Alpha_Num AS NUMERIC (10,0)),
0
FROM dbo.OSHAN_Alpha O
WHERE Oshan_Alpha_ID > @OSHAN_ALPHA_ID
AND OSHAN_ALPHA_ID < @Next_OSHAN_ALPHA_ID
-- Don't add the OSHA number if it is already
-- in the OSHAN table, regardless of key_code.
AND NOT EXISTS (SELECT *
FROM dbo.Oshan
WHERE oshan_num = CAST(oshan_Alpha_Num AS NUMERIC (10,0)))
Select @Rows_Inserted = @Rows_Inserted + @@ROWCOUNT
END
-- Delete the from "!FORM" rows from our temp table
DELETE dbo.Oshan_Alpha
WHERE Oshan_Alpha_ID = @Oshan_Alpha_ID
SELECT @Form_Recs = @Form_Recs + 1
-- Delete the rows from our temp table that actually had OSHA Numbers from IMIS
DELETE dbo.Oshan_Alpha
WHERE Oshan_Alpha_ID < @Next_Oshan_Alpha_ID
Select @Oshan_Count = @Oshan_Count + @@ROWCOUNT
-- Set the from ID from our temp table. It is the through ID from our prior loop
-- unless we are already at the end of the data.
SELECT @Oshan_Alpha_ID = MIN(Oshan_Alpha_ID)
FROM dbo.Oshan_Alpha
WHERE Oshan_Alpha_Num LIKE '!FORM%'
-- Now we are getting the second row from our OSHAN text file with
-- the value "!FORM' in the first five bytes of the field, and saving
-- the sequential ID in a save area. This gives us a from and to ID
-- so we can process all rows between the first and second "!FORM" rows
-- passed from OSHA IMIS.
SELECT @Next_Oshan_Alpha_ID = MIN(Oshan_Alpha_ID)
FROM dbo.Oshan_Alpha
WHERE Oshan_Alpha_Num LIKE '!FORM%'
AND Oshan_Alpha_ID > @Oshan_Alpha_ID
-- If we did not find a second "!FORM" row, we will set our through value
-- with the highest ID we have on our table + 1. This will cause us to process
-- all the remaning data on the table.
If @Next_Oshan_Alpha_ID IS NULL
SELECT @Next_Oshan_Alpha_ID = MAX(Oshan_Alpha_ID) + 1
FROM dbo.Oshan_Alpha
END
SELECT @Rows_Inserted AS "Rows_Inserted", @Oshan_Count AS "Oshan_From_IMIS"
-- Execute procedure to reconile the new osha numbers with the existing
-- reqst, visit and sampl tables to see if any have already been used.
EXECUTE dbo.p_oshan_reconcile
SELECT CONVERT (CHAR (8), GETDATE(), 108) AS "End_Time"
GO
 |
Linear Mode
