Printable View
Hi All,
I have an excel sheet like this:
I want to convert this into a table. Please can any one help me?Code:Ratio 1 2 3 4 5
Profitability
NIM 20% UL 10 7.49 5.49 3.49 2.49
LL 7.5 5.5 3.5 2.5 1
ROAA 30% UL 50 39 29 19 9
LL 40 30 20 10 0
ROAE 30% UL 50 39 29 19 9
LL 40 30 20 10 0
Asset Quality
Gross NPL 50% UL 50 39 29 19 9
LL 40 30 20 10 0
Net NPL 50% UL 9 19 29 39 50
LL 0 10 20 30 40
What kind of table?Quote:
Originally Posted by sillylady
MSSQL table. How can i convert this excel structure in meaningful table. I want a table structure based on this given excel sheet. There are 2 groups "Profitability" and "Asset Quality" each group has different ratios (like NIM), each ratio has a weightage (like 20%) then each ratio has UL (upper limit) LL (lower limit) and based on the upper & lower limit there is a point between 1 to 5.Quote:
Originally Posted by Hack
In Access you could do a DoCmd.TransferSpreadSheet call. Don't know if SQL server has something similar. If your fields are calculated, that could be a problem.
How about this?
You'd need 5 records per sheet, each record is one column.
If you prefer one record per sheet then you just add each field five times and add a number to it.
Filling the table would be done with a procedure that sends the right cells to the right fields.Are the weightings variable?Code:tbl_Table
Sheet_Id
Column_Id
Profitability_NIM_20_UL
Profitability_NIM_20_LL
Profitability_ROAA_30_UL
Profitability_ROAA_30_LL
Profitability_ROAE_30_UL
Profitability_ROAE_30_LL
AssetQuality_GrossNPL_50_UL
AssetQuality_GrossNPL_50_LL
AssetQuality_NetNPL_50_UL
AssetQuality_NetNPL_50_LL
Thanks for ur suggestion. I think for each complete record like (Ratio Name, UL, UL, Wieghtage and Point) and that's what u have suggested. Is there any other way to simplify it, even if more than 1 table is needed.Quote:
Originally Posted by jeroen79
Depends.
Do you always have the same fields? No more, no less?
If the sheets are always the same then I see little use in creating more tables.
Sure, each individual table might have few fields but all tables combined would have the same amount plus some extra fields to keep things linked.
Now if the amount of data on these sheets is variable then a multi-table setup might be useful.
Who will be using this data and how do they want to use it?
This is just an excel sheet which is used as a reference. The Fields will remain same. I have been told to convert this into a table and there will be one more table (GRADE) that will hold the GRADE_Name and Point for each grade. Based on the point of excelsheet and after some calculation there will be another point which will be compared with the Point in the grade table and a GRADE will be awarded. Hope I am clear.Quote:
Originally Posted by jeroen79
What do these grades and points do?
How do they relate to these sheets?
I don't these 2 tables has any direct relation. Based on a calculation POINT will be picked up and it will be calculated again and the result will be compared with in GRADE table POINT field and then a grade will be awarded.Quote:
Originally Posted by jeroen79
For example:
Code:Table RANGE has fields named Ratios Group, Ratio Name, UL, LL, Weightage,Point)
Table GRADE has fields named Grade and Point
P.S. Field point is not the same in both the table.
It will be a rare day when you can import or convert an excel spreadsheet so that the table looks and behaves the way your spreadsheet does.
Here is a table which I believe accomplishes what you're after, no garantees but you can allows play around with it to get it to do what you want.
Here are the field names with sample data for the first 3 items of the first line in the sample you provided.
VB Code:
Each to the three columns 1-3 represents one complete record in your database table.