Unstructured File with Header, Detail, Trailer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Unstructured File with Header, Detail, Trailer

Author

Topic

rka
Starting Member

6 Posts

Posted - 2012-05-31 : 10:35:17

I have a flat file (pipe delimited) which I want to import into a table. The file format is based on Header, Detail and Trailer and is structured as:

quote:
H|20120531|SAPECC6|
D|CostCentreID|CostCentreName|CostCentreDescription|
D|700|Name1|Description 1|
D|701|Name2|
D|702|Name3|Description 3|
D|703|Name4|
T|4|

The First Row represents Header (H) which contains ExtractDate and SourceSystemName
The Second row represents the Column Names for the Detail Rows
There are 4 Details (D) rows containing the data
The Trailer(T) row contains the count of data in the Detail rows

If the file is treated as an "unstructured file" e.g. the 2nd and 4th detail row does not have description, how can the data be imported into a destination table which looks like this:


CostCentreID  CostCentreName   CostCentreDescription  SourceSystem ExtractDate
700               Name1                Description1   SAPECC6       2012-05-31
701               Name2                               SAPECC6       2012-05-31
702               Name3                Description3   SAPECC6       2012-05-31
703               Name4                               SAPECC6       2012-05-31

And finally, I want to validate the number of rows loaded against the Trailer Recod which was 4 (This acts as a reconciliation)

Can anyone help me with this?

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-04 : 18:58:36

1. is there always a trailer record per file being ingested?
2. after validation against T|4| what do you want to do?
3. any chance you can get this crap of data as xml?
4. will the file structure and/or values stay the same?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-04 : 19:27:04

[code]
create table dbo.rka(CostCentreID int, CostCentreName varchar(50), CostCentreDescription varchar(50), SourceSystem varchar(50), ExtractDate datetime)

ingest to a staging table named CostCenter

insert into dbo.rka(CostCentreID , CostCentreName, CostCentreDescription , SourceSystem, ExtractDate )
SELECT cs1.[COLUMN 1], cs1.[COLUMN 2], cs1.[COLUMN 3], a.[COLUMN 2], CAST(a.[COLUMN 1] AS DATETIME)
FROM [dbo].[CostCenter] cs1
cross apply (SELECT [COLUMN 1], [COLUMN 2] FROM [dbo].[CostCenter] cs1 where [COLUMN 0] = N'H') a
where ISNUMERIC(cs1.[COLUMN 1]) = 1
and cs1.[COLUMN 0] = 'D'
[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources