flatten out a normalized child table?

I need to extract Customer Order data, and join it to normalized ship-to
table so I can get their address on a single line/row of data. The Column
names are not importat in that final flat file, just what was in
row1,2,3,...6
GARY C Test Row1
LISA C Test Row2
816 RIVERVIEW PLACE Row3
WASHINGTON, MO 63090 Row4
Row5
Row6
THOMAS H Other-Test Row1
2102 N SHAMROCK RD Row2
BEL AIR, MD 21014 Row3
Row4 ,etc.
I have to account for double names names, and possibably titles, Suite #,
etc.
TIAPlease post DDL with your sample data. What is the key of the table you
posted? What relates the address lines together to make a single
address? Apparently nothing links an address together in the sketch you
posted except for the order in which you typed them out. We know that
tables have no fixed order so it isn't possible to combine the rows
reliably to make addresses out of each one.
If you had an additional column such as contact_name or contact_no for
each address line then you could do something like:
SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
MAX(CASE WHEN row_num = 2 THEN addr END),
MAX(CASE WHEN row_num = 3 THEN addr END),
... etc
FROM your_table
GROUP BY contact_name ;
Hope this helps.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129058354.597302.39690@.g44g2000cwa.googlegroups.com...
> Please post DDL with your sample data. What is the key of the table you
> posted? What relates the address lines together to make a single
> address? Apparently nothing links an address together in the sketch you
> posted except for the order in which you typed them out. We know that
> tables have no fixed order so it isn't possible to combine the rows
> reliably to make addresses out of each one.
> If you had an additional column such as contact_name or contact_no for
> each address line then you could do something like:
> SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
> MAX(CASE WHEN row_num = 2 THEN addr END),
> MAX(CASE WHEN row_num = 3 THEN addr END),
> ... etc
> FROM your_table
> GROUP BY contact_name ;
Thanks

Flat File With Fixed Length Header and No Delimeter

Hi,

I'm trying to extract data from a Flat File which is as fixed length as they come. The file has a header, which simply contains the number of records in the file, followed by the records, with no header delimeter (No CR/LF, nothing).

For example a file would look like the following:

00000003Name1Address1Name2Address2Name3Address3

So this has 3 records (indicated by the first 8 characters), each consisting of a Name and Address.

I can't see a way to extract the data using a flat file connection, unless we add a delimeter for the header (not possible at this stage). Am I wrong?

Any suggestions on possible solution would be much appreciated - I'm thinking Ill have to write a script to parse the file manually.

Thanks in advance,

Scott

Do you need the data in the first row?

You can just ignore it by setting the "Header rows to skip" setting to 1.

K

|||

Yes. Essentially the file is just one row..Which would include the header details (number fo records) then all of the fixed length records follow (on the same line)

Scott

|||

Scott,

Given the unstructured nature of this file I think you will have to parse it out yourself in a script task. This isn't as daunting as it sounds. First clue I can give you is that it will have to be an asynchronous script task.

You can still import it into the pipeline using a Flat File Connection Manager though. It'll be a 1-column, 1-row file that's all.

-Jamie

|||

Using a script component of type source should be easiest. A little example follows.

Here is my sample file, representing an 8 byte header, followed by three rows of two columns, 10 and 20 bytes respectively.

00000003A234567890B234567890C234567890D234567890E234567890F234567890G234567890H234567890I234567890

The results table will look a bit like this-

Name Address
A234567890 B234567890C234567890
D234567890 E234567890F234567890
G234567890 H234567890I234567890

You will need to create the two columns in the script component , Name and Address as DT_WSTR 10 and 20 in length.

Now the code-

Public Class ScriptMain

Inherits UserComponent

Private stream As StreamReader

Public Overrides Sub CreateNewOutputRows()

Dim headerRecordCount As Integer

Dim recordCount As Integer = 0

'// Get filename from connection, using full acquire method

Dim filename As String = CType(Me.Connections.Connection.AcquireConnection(Nothing), String)

'// Open source file

stream = New StreamReader(filename)

'// Reader header block, 8 characters

Dim headerBuffer(7) As Char

If stream.ReadBlock(headerBuffer, 0, 8) = 8 Then

'// Store record count for later use in validation

headerRecordCount = CType(New String(headerBuffer), Integer)

Else

Throw New Exception("Invalid file format, header not valid.")

End If

With Output0Buffer

While stream.Peek > 0

'// Add data rows

.AddRow()

.Name = ReadColumn(10)

.Address = ReadColumn(20)

recordCount = recordCount + 1

End While

'// Close down buffer

.SetEndOfRowset()

'// Check record count

If recordCount = headerRecordCount Then

Me.Log(String.Format("Header row count ({0}) matched toital rows found.", headerRecordCount), 1, Nothing)

Else

Throw New Exception(String.Format("Invalid file format, header row count ({0}) not equal to rows found ({1}).", headerRecordCount, recordCount))

End If

End With

End Sub

Private Function ReadColumn(ByVal length As Integer) As String

Dim buffer(length - 1) As Char

If stream.Read(buffer, 0, length) = length Then

Return New String(buffer)

Else

Throw New Exception("Invalid file format, full column length not found.")

End If

End Function

End Class

|||Thanks for the answers guys..

Have gone with using a script component of type source, as per code above. With one change...Just ensured that the stream is closed after processing to ensure the resources are released...

I also added an extra Output for the script which holds the header details - my real data file has extra (useful) details in the header.

Thanks again..

Scott

Flat File source to Oracle OLE DB Target (ETL)

What could be simpler: map a flat file record structure, extract the data, and populate essentially the same flat file record struc in an Oracle table. Let the fun begin.

Specifically: the flat file record struc is fixed length 196 bytes. A particular field consists of 4 bytes of Integer data; IS deals very nicely with the definition, does not appear to be any issue with that. The issue is trying to get the 4 bytes of integer to map and load into the Oracle table. The data type in the flat file def is DT_UI4. The data type in the Oracle target is DT_NUMERIC. One would think that perhaps a simple transform and Viola?! I've defined the transform but does not seem to matter - whatever I try yeilds the same results.

I 've tried many different src/trg data type defs., but all yeild the same results.

Execution Results from debug:

Everything validates and then...

[kcd [8671]] Error: Data conversion failed. The data conversion for column "load_time_min" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[kcd [8671]] Error: The "output column "load_time_min" (11050)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "load_time_min" (11050)" specifies failure on error. An error occurred on the specified object of the specified component.

Any ideas appreciated!

Thanks.

What precision and scale are you specifying for the DT_Numeric? Since the Integer column is defined as an unsigned integer, you need to allow enough room in the numeric to support it. Or you could use a derived column transform to explicity cast the integer to the numeric, if you know there will not be an overflow.

(DT_NUMERIC, 10, 0) [Your_Column]

first 7 rows

I have a table sal with the following structure
emp_no int,
dept_no int,
basic_salary money
Is it possible to extract 7 highly paid employed with one SQL statement?
any help will be highly appreciated.select TOP 7
emp_no,
dept_no,
basic_salary
from YourTable
order by basic_salary desc|||how will it calculate highest salary? I mean how system knows to display record based on salary field?|||"order by basic_salary desc"|||the top clause retrieves the rows that would exist at the "Top" of your result set so if your result set was
joe
steve
bill
rob
mary

and you selected the top 3
you would get
joe
steve
bill

the control factor with the top clause is the order by clause
the order by clause sorts the result set either in ascending (ASC) or descending (DESC) order. so if you sorted a salary column asc, the lowest salary would be at the top correct?. and if you selected the top 7 salaries in that example, you would have the 7 lowest salaries.
by sorting the salary column in desc order, you would get the top 7 salaries

please open and read this help file
Books Online{Limiting Result Sets Using TOP and PERCENT}|||how will it calculate highest salary? I mean how system knows to display record based on salary field?

What is the context of the salary table? Does it hold weekly salary data?

Everyone here thinks it's annual.|||Sheesh!

create table sal
(
emp_no int
,dept_no int
,basic_salary money
)

insert into sal values (1 ,10,1000.00)
insert into sal values (2 ,10,2000.00)
insert into sal values (3 ,10,1500.00)
insert into sal values (4 ,10,1200.00)
insert into sal values (5 ,10,1000.00)
insert into sal values (6 ,10,3000.00)
insert into sal values (7 ,10,2200.00)
insert into sal values (8 ,10,1250.00)
insert into sal values (9 ,10,1350.00)
insert into sal values (10 ,10,1000.00)

select * from sal

select top 7 emp_no, dept_no
,Basic_Salary as 'WeeklySalary' --by week
,BiWeeklySalary=(Basic_Salary*2) --BI week
,AnnualSalary=((basic_salary*2)*26) --Annual
from sal
order by AnnualSalary desc|||OK...so what if there's more than 1 salary row per employee?|||...an aggregate query to sum up the salary values.|||...an aggregate query to sum up the salary values.

Thanks you...my point...

Without the DDL of the table we'd be just shooting in the dark

Bang

Yo blind dude..did that hit you?|||Nyah nyah, ya missed me!

You forget that, as the Blindman, I shoot in the dark just fine! :cool:|||okay
since the poster hasnt said anything about it then this post is officially dead.

dont be a malingerer.|||"since the poster hasnt said anything about it then this post is officially dead"?

Like THAT has ever stopped us before...

and "malingerer"?

In the words of Inigo Montoya -
"You keep using that word. I do not think it means what you think it means."

http://dictionary.reference.com/search?q=malingerer|||you killed my father -- prepare to die!|||i know exactly what it means.
you are spending your time posting to a thread that is dead
so you are in effect acting crazy(sick) to get out of the real work here
and that is driving rdjabarov and pat phelan crazy.|||i know exactly what it means.
you are spending your time posting to a thread that is dead
so you are in effect acting crazy(sick) to get out of the real work here
and that is driving rdjabarov and pat phelan crazy.Huh? I seem to keep missing meetings around here!

-PatP|||you killed my father -- prepare to die!

Do you have 6 fingers on your left hand?

That is a great movie

What's with Scott? Are there time issues involved here?

What's officially Dead?|||You are!

Because you killed my father! Prepare to die!

...and Ruprect: Glad you looked up the definition! It pays to enrich your wordpower!|||okay
since the poster hasnt said anything about it then this post is officially dead.

dont be a malingerer.

Hey...he's not even suppose to be here...he's an...an...Oracle developer...

Yo Blind dude..are you feeling OK? (http://www.google.com/search?hl=en&lr=&oi=defmore&q=define:malingerer)|||I have a cough that has been malingering on for weeks now...|||I have a cough that has been malingering on for weeks now...I resemble that remark!

-PatP|||Hey...he's not even suppose to be here...he's an...an...Oracle developer...Its Ok... He can have his kids tell folks that he plays the piano in... Nevermind!

-PatP|||And this thread is only mostly dead...there a difference

To blathe

Have fun stormin the castle boys

I would like to stay and malinger...but I have to go

EDIT: Ya know, this is corral stuff|||Where the heck is a Yak when a guy needs one? I'm off (but you knew that)!

-PatP|||Mostly dead. Now THAT was funny. I wish I'd though of that one, Buttercup.

Can this thread possibly go on? Inconceivable!|||The discussion of this mostle dead thread has moved to the corral

http://www.dbforums.com/showthread.php?p=3978181#post3978181