Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Excel data (Read 2623 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Excel data
15.12.08 at 20:02:58
 
Excel data
 
You can divide data in Excel spreadsheets into 3 groups:
 
1. values
Excel cells can contain values. A distinction should be made between the value itself and the display of the value, the format. For text this is usually not different but for instance with numbers a representation needs to be chosen. A value can be 1 but the display can be set to 1.000. That's the same you say, and yes in this case it is the same. Suppose I have a value of 1.33333 and I set the display to no decimals. The value would display as a 1 but the actual value is a third higher.  
With dates this is yet more extreme. When a date shows as 1/DEC/2008 its value is actually 39783. Strange? Well, no. It is the number of days since 1/1/1900. 39784 is therefore 2/Dec/2008 and so on. Time is also added in this way as 39783.5 is 1/Dec/2008 12:00. I mention dates as it is very clear that the display value is completely different from the cell value.  
 
2. Formulas
Formulas can be used in Excel cells to perform computations. Usually other cell values serve as input for the formulas. There is an extra aspect now in the discussion of cell values and cell formatting from the previous paragraph. The value is now not hard coded in to the cell but computed by the formula. Still if a cell is used in another formula the computed cell value is used.
 
3. References
References can be seen as a very simpel formula where simply a value is copied from another location of the spreadsheet. Actually it is broader than that in the sense that also values from other spreadsheets can be used.  
Example:
When =A1 is typed the value of A1 is displayed in the current cell. When =Sheet2!D12 is used the value in D12 in sheet2 is displayed. When =[MyBook.xls]Sheet3!E3 i sused the value in E3 in the sheet3 of the mybook.xls file is used. Note that it is not needed that this file is open in Excel!
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Excel data and SQL
Reply #1 - 08.01.09 at 10:54:50
 
Excel data and SQL
 
It is important to note for users of the SQL*XL software which values are actually sent to the database when they use SQL from Excel to communicate to their databases.
 
Why not give SQL*XL a try today.
 
SQL*XL: SQL Excel software

Download SQL*XL now!


 
SQL*XL uses the value and not the formatted values in the sheet. If the value in a cell is e.g. displayed as 2.00 then the actual value is 2 and the formatting is set such that it displays with two decimals. SQL*XL will see this value as 2 and not as 2.00.
 
With dates this is a little more complex or perhaps it is just easier. A date in Excel has a numeric value. The number of seconds since 1/1/1900 I believe. What you see displayed in Excel is a formatted version as 13/Dec/2008 or Dec, 13th 2008 or whatever you have specified as default or specific formatting. SQL*Xl will take the numeric value always.
Back to top
 
« Last Edit: 08.01.09 at 10:57:14 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1