January 31, 2011 at 1:24 pm
I have a results text file of a part ran on a machine, certain columns of the data needs to go into 1 table and other data depending on a certain field needs to go into 1 or another table. Example, file (there are more fields and values but to simplify for sample):
Part Load Speed Backlash Side DeltaH Label Value Label Value
12345 18 130 0.175 Front 0 Pos1 15.78 Pos2 5.09
12345 18 130 0.175 Rear 0 Pos1 13.42 Pos2 8.09
12345 18 130 0.175 Front 1 Pos1 38.25 Pos2 9.75
12345 18 130 0.175 Rear 1 Pos1 42.92 Pos2 10.46
The first table is "Result_Header" which will be the data that's the same for all rows
Part Load Speed Backlash
12345 18 130 0.175
Now for the row if the Side is "Front" then the results need to go to table "Result_Front" and also depending on the name in Label then goes to columnX:
DeltaH Front1 Front2
0 15.78 5.09
1 38.25 9.75
Same if the Side is "Rear" then results go to "Result_Rear" and also depending on the name in Label then goes to columnX:
DeltaH Rear1 Rear2
0 13.42 8.09
1 42.92 10.46
So my question(s) is do I initally put the text file into a temp table and then with code parse out into the 3 tables needed?
Any direction on how to handle this would greatly be appreciated.
Thanks!
January 31, 2011 at 1:37 pm
I personally would actually approach this from SSIS, though there are varying discussions on that.
For SSIS, you'd multi-cast and do a number of twists on the datastream including conditional splits and merge joins to get it where it needs to go.
In T-SQL, you'll build a staging table that holds all of the columns/rows. Next you'll create an Insert script to the header table that deals with only distinct items of the first four columns. Now that you have that, you can link back to that table as necessary for identities if need be to fill in the 'front' table.
I realize you're simply pivoting the front/rear data (with probably leftside/rightside/whatnot) into the front table itself, but my concern here is that you have unique tables for each side. These are basically the same data constructs, and you're making future querying very painful for yourself.
Would you be willing to post the full DDL of the tables you're working on, along with an idea of your intended goals, so perhaps we can give you some advice on that? I think you're setting yourself up for a world of pain.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 7, 2011 at 7:30 am
Craig Farrell (1/31/2011)
In T-SQL, you'll build a staging table that holds all of the columns/rows.
This can be done in SSIS as well, and might be easier than the SSIS example Craig used. He is, however, correct about the pain level. If we had a better idea of your needs, we might be able to suggest a better solution to your problem. It might just be a matter of partitioning.
February 7, 2011 at 8:34 am
I have attached a spreadsheet that comes out of the machine as a CSV file.
I need to upload this data and then display on the screen.
I have attached an Excel file highlighting the fields in yellow that would be considered "header" data because its the same for every line.
I have also attached a mock output for the screen of how it needs to be
displayed and where the fields come from.
Also the screen display will be refreshed every x seconds to display the most recent entry.
I would like to accomplish all of this through a package since I recently discovered table triggers don't work well when the data is entered through a package.
Thanks for any insight or help!!!
February 7, 2011 at 8:41 am
smac (2/7/2011)
I have also attached a mock output for the screen of how it needs to bedisplayed and where the fields come from.
Also the screen display will be refreshed every x seconds to display the most recent entry.
Displays are cosmetic stuff. Do not design your tables or your data load to take displays into consideration because they are two completely different things.
The first thing you want to do is upload the data in a format that fits your database and your data needs. Worry about the Display after you've got everything loaded and sorted. If you can separate your thinking on this matter, your life will be a lot easier.
Craig and I will take a look at the attachment as soon as we get some time. In the meanwhile, do yourself a favor and stop stressing over the idea that "Load / Processing = Display". Because they don't and you'll go batty trying to make two pegs fit in the same hole at the same time.
EDIT: Oh, hey, the Excel sheet is missing.
February 7, 2011 at 8:56 am
Hm, it showed it was attached.
Here again, thanks.
February 7, 2011 at 9:00 am
In the meanwhile, do yourself a favor and stop stressing over the idea that "Load / Processing = Display". Because they don't and you'll go batty trying to make two pegs fit in the same hole at the same time.
😀 Thanks, I am totally at that point right!
Think I will take a step back and start at Step 1, getting data in and go from there
February 8, 2011 at 5:19 am
Okay, I appear to be missing context for your data. I get that it appears to be machine parts, but I don't understand where "Side" comes into play here.
Forgetting about display, what is this data used for? What is the real world application? Why do people even need this information and how will they apply it to their jobs?
February 14, 2011 at 5:33 am
Brandie Tarvin (2/8/2011)
Okay, I appear to be missing context for your data. I get that it appears to be machine parts, but I don't understand where "Side" comes into play here.Forgetting about display, what is this data used for? What is the real world application? Why do people even need this information and how will they apply it to their jobs?
I haven't seen an answer to my question. Did you get your problem resolved?
February 15, 2011 at 1:24 pm
Brandie Tarvin (2/14/2011)
Brandie Tarvin (2/8/2011)
Okay, I appear to be missing context for your data. I get that it appears to be machine parts, but I don't understand where "Side" comes into play here.Forgetting about display, what is this data used for? What is the real world application? Why do people even need this information and how will they apply it to their jobs?
I haven't seen an answer to my question. Did you get your problem resolved?
No sorry, my head began to hurt from banging it. :hehe:
As far as the side the gear and the pinion have teeth on them and the flank (drive or coast) is the side, kinda like left and right.
That's why there are typically 2 lines of information, one for coast and one for drive.
We are collecting the data for a traceability project, these gears and pinions go through several processes and this is just one of them.
I don't need all the columns but I do need all the columns that have "Label" and "Inspection_Value", in addition to some others.
I am trying to use SSIS to import this but since there are several "Label" and "Inspection_Value" columns in the TXT file it won't import them.
So I thought of renaming the headers to "Label1", "Label2", etc then importing into a table but another issue is that sometimes there can be 5 label fields or 13 and I really don't want to have Label1, Label2 fields in table.
I tried to read the header and data into arrays but I don't know how to grab only the data I want - it's a mess I know.
Basically when said and done I need to bring up part X (CycleDesc) and the tests that were performed & values (Label & Inspection_Value columns).
I hope this helps?!
February 16, 2011 at 10:46 am
So you have a file with multiple, repeatable column headers. How is this file generated? Is it automated or does a person fill in the sheet?
EDIT: If automated, what sort of system is the file coming from?
February 16, 2011 at 12:06 pm
It's automated off one of our machines, I don't know what they use internally, a custom application that spits out the data.
I have been able to write some script that puts the header and each line of data into an array (yea) I am now trying to figure out how I want to handle the repeating headers.
February 16, 2011 at 12:13 pm
The best way to deal with this issue is to work with the people who own the automated files.
Here's what I would ask for:
1) Keep the # of columns consistent. If there is no value for that column for that particular part, then leave it blank or NULL, but don't delete the column.
2) Change the headers so that each column label is unique. Even if that's just appending _1, _2, etc.
If you can get them to do that, it will make your life easier.
I'm not sure if SSIS is built to account for disappearing columns. Lots of people have trouble with this issue, but I'm unaware of any resolutions to it. As far as the column headers go, you could use a script task to go through the first row, renaming headers. Either do it as part of the SSIS package, or create a brand new application that crunches the file pre-SSIS import. But it's better if you can get the person who sends you the file to do that work for you. Less headaches all around.
February 17, 2011 at 12:08 am
Craig Farrell (1/31/2011)
In T-SQL, you'll build a staging table that holds all of the columns/rows
Out of the 20 odd SSIS packages I'm in control off, 2 of them uses this approach. One of them is responses from clients on SMS send out. There is NO way of controlling what a person send back from an SMS and the only way there is, is to use a staging table for SSIS to use. A stored proc sorts out the data into proper responses which SSIS then further processes. There found be no way in SSIS to sort out this mess.
Use all of the tools you have, dont try and force anything into just one tool if there is other easier options open to you. 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply