Are the posted questions getting worse?
- Eirikur Eiriksson wrote:
Gents, you can do better than that!
😎
Now think inside your (tool)box!
My brain has melted this previous week so I'm having a hard time with it.
In that same spirit, what does the number 3|8 mean to a DBA? Ed... you're not allowed to answer because I know you know. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Eirikur Eiriksson wrote:
Gents, you can do better than that!
😎
Now think inside your (tool)box!
Heh... lordy. You need to use the tool to spell the tool...
SELECT CONCAT(CHAR(838176/10000),CHAR(838176/100%100),CHAR(838176%100));
--===== Or, if you want to do it all auto-magically
DECLARE @Value INT = 838176;
SELECT REVERSE(STRING_AGG(CHAR(@Value/POWER(10,t.N*2)%100),''))
FROM dbo.fnTally(0,CEILING(LOG(@Value)/LOG(10))/2-1) t
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Eirikur Eiriksson wrote:
Gents, you can do better than that!
😎
Now think inside your (tool)box!
Heh... lordy. You need to use the tool to spell the tool...
SELECT CONCAT(CHAR(838176/10000),CHAR(838176/100%100),CHAR(838176%100));
--===== Or, if you want to do it all auto-magically
DECLARE @Value INT = 838176;
SELECT REVERSE(STRING_AGG(CHAR(@Value/POWER(10,t.N*2)%100),''))
FROM dbo.fnTally(0,CEILING(LOG(@Value)/LOG(10))/2-1) t
;Kind of obvious isn't it 😉
😎
- Eirikur Eiriksson wrote:
Further on the Excel CSV quirks, as all numbers are floating-point numbers in Excel, any manipulation of the cell value will result in the 15[n]+0[n]
😎
Create a CSV file
"12345678901234567890","MARCH1"
"123456789012345678901234567890","MARCH1"
"1234567890123456789012345678901234567890","MARCH1"
"12345678901234567890123456789012345678901234567890","MARCH1"In a blank Excel press Alt+A and then F + T
Select the file to import and press "Import"
make certain that the "Data type detection" is set to "Do not detect data types"
Click "Load"
and the result will be
Word of warning, this method does not like any column headers, will elaborate on this upon any request!
Not certain whether to call this a bug or a piknic, sometimes the two go together:)
Several issues with this approach.
- It solves one issue by creating another. People still want to load dates as dates, and amounts as numbers. Making everything "text" means a lot of manual adjustments to follow the file upload.
- The files definitely had column headers. Not sure what problems you're talking about because I don't use MS Office for years and don't have an ability for find it out.
- This solution needs to be propagated to thousands of corporate customers in all sides of the world, actually, to 10th of thousands of their employees and contractors. They all are supposed to accept and remember following this tricky process.
- Their computers have Excel setup as a default application for opening CSV files. Simple double-click - and here you are, annoyed and swearing on those stupid IT people who cannot get such a simple thing right.
- "Data type detection" settings are not accessible for most of corporate users. You need to go through the process, to make your system admin to change the Office settings in the whole corporation. When your corporation name starts with "Penta" and ends with "gon" you may imagine how the process would look like and what would be its outcome.
We actually did kinda enforce data import for some users by generation ".tsv" files for them. They were able to change the data type for "Account" column in the wizard screen. But it was not easy, I can tell you.
Especially if to compare with simplicity of Libre Office solution.
_____________
Code for TallyGenerator - Sergiy wrote:Eirikur Eiriksson wrote:
Further on the Excel CSV quirks, as all numbers are floating-point numbers in Excel, any manipulation of the cell value will result in the 15[n]+0[n]
😎
Create a CSV file
"12345678901234567890","MARCH1"
"123456789012345678901234567890","MARCH1"
"1234567890123456789012345678901234567890","MARCH1"
"12345678901234567890123456789012345678901234567890","MARCH1"In a blank Excel press Alt+A and then F + T
Select the file to import and press "Import"
make certain that the "Data type detection" is set to "Do not detect data types"
Click "Load"
and the result will be
Word of warning, this method does not like any column headers, will elaborate on this upon any request!
Not certain whether to call this a bug or a piknic, sometimes the two go together:)
Several issues with this approach.
- <li style="list-style-type: none;">
- It solves one issue by creating another. People still want to load dates as dates, and amounts as numbers. Making everything "text" means a lot of manual adjustments to follow the file upload.
- <li style="list-style-type: none;">
- The files definitely had column headers. Not sure what problems you're talking about because I don't use MS Office for years and don't have an ability for find it out.
- <li style="list-style-type: none;">
- This solution needs to be propagated to thousands of corporate customers in all sides of the world, actually, to 10th of thousands of their employees and contractors. They all are supposed to accept and remember following this tricky process.
- <li style="list-style-type: none;">
- Their computers have Excel setup as a default application for opening CSV files. Simple double-click - and here you are, annoyed and swearing on those stupid IT people who cannot get such a simple thing right.
- <li style="list-style-type: none;">
- "Data type detection" settings are not accessible for most of corporate users. You need to go through the process, to make your system admin to change the Office settings in the whole corporation. When your corporation name starts with "Penta" and ends with "gon" you may imagine how the process would look like and what would be its outcome.
We actually did kinda enforce data import for some users by generation ".tsv" files for them. They were able to change the data type for "Account" column in the wizard screen. But it was not easy, I can tell you.
Especially if to compare with simplicity of Libre Office solution.
There seems to be a bit of a misunderstanding here:
1. Values consisting of digits only (0-9) are numerical and not converted to text. Any non-numerical character will result in the value being tagged as text. The caveat is that large numbers will overflow most functions although arithmetic operators can be applied.
2. Files can have column headers but those should not be marked as such in the import as that will lead to the values being transformed into a floating-point number with the 15 significant digits limit.
3. The process is relatively simple, and one would assume that if the users can efficiently use a computer and an application such as Excel, they should have the capacity of learning a 4-keystroke process.
4. Unfortunately, the MS-Office/Excel setup process will register Excel as the default application for CSV files, and when those files are opened by Excel directly (i.e. double click), numerical values are converted into floating-point numbers. Changing this is easy and any AD admin worth his wages can blanketly apply such changes to the whole organisation.
5. Data type detection limitation is nonsensical, administrators do not control application-level data detection algorithms and certainly should not trust the likes of Micro$oft or anyone for that matter, other than the application users, for deciding what are the appropriate data types.
I am just pointing out the correct procedures for importing large numerical values into MS-Excel. Why someone would want to do such thing is another discussion with someone holding a note pad and the other lying flat on a bench, spreadsheet applications are not the right tools for the job. Further, if the large numerical sequences are account identifiers, they should, in fact, be treated as a text.
😎
- Sergiy wrote:
We actually did kinda enforce data import for some users by generation ".tsv" files for them. They were able to change the data type for "Account" column in the wizard screen. But it was not easy, I can tell you.
Especially if to compare with simplicity of Libre Office solution.
On a side note, I have tested and used a few spreadsheet applications, none of which are perfect.
😎
From the top of my head, here is the list of what I got installed at the moment:
1. Microsoft Office 2007 and later
2. SoftMaker Office 2010
3. LibreOffice
4. OpenOffice.org
5. Apache OpenOffice
6. The Go-oo fork of OpenOffice
7. KOffice
8. Calligra Suite
9. NeoOffice
10. TextEdit
11. iWork
12. IBM Lotus Notes
13. Abiword
14. WordPerfect
15. Kingsoft Office
16. Google Docs
17. SoftMaker FreeOffice
- Eirikur Eiriksson wrote:Jeff Moden wrote:Eirikur Eiriksson wrote:
Gents, you can do better than that!
😎
Now think inside your (tool)box!
Heh... lordy. You need to use the tool to spell the tool...
SELECT CONCAT(CHAR(838176/10000),CHAR(838176/100%100),CHAR(838176%100));
--===== Or, if you want to do it all auto-magically
DECLARE @Value INT = 838176;
SELECT REVERSE(STRING_AGG(CHAR(@Value/POWER(10,t.N*2)%100),''))
FROM dbo.fnTally(0,CEILING(LOG(@Value)/LOG(10))/2-1) t
;Kind of obvious isn't it 😉
😎
Once the last vestiges of the day's supply of caffeine tried one final and almost unsuccessful time to energize my brain, yes. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Eirikur Eiriksson wrote:Jeff Moden wrote:Eirikur Eiriksson wrote:
Gents, you can do better than that!
😎
Now think inside your (tool)box!
Heh... lordy. You need to use the tool to spell the tool...
SELECT CONCAT(CHAR(838176/10000),CHAR(838176/100%100),CHAR(838176%100));
--===== Or, if you want to do it all auto-magically
DECLARE @Value INT = 838176;
SELECT REVERSE(STRING_AGG(CHAR(@Value/POWER(10,t.N*2)%100),''))
FROM dbo.fnTally(0,CEILING(LOG(@Value)/LOG(10))/2-1) t
;Kind of obvious isn't it 😉
😎
Once the last vestiges of the day's supply of caffeine tried one final and almost unsuccessful time to energize my brain, yes. 😀
And here I was thinking that
0x383338313736
8 3 8 1 7 6was far too obvious of a hint
😎
- Eirikur Eiriksson wrote:
And here I was thinking that
0x383338313736
8 3 8 1 7 6was far too obvious of a hint
😎
It absolutely was obvious... it just couldn't find a target in my brain because there was no caffeine to hit. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
On the same note, you're not yet told me what you think the 3|8 number means in the life of a DBA. My hint would be "It Depends" on how you look at it. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
On the same note, you're not yet told me what you think the 3|8 number means in the life of a DBA. My hint would be "It Depends" on how you look at it. 😀
I should remember this as you've told me this before, the problem is that my memory is as leaky as an old coffee filter.
😎
The first thought is that 3 OR 8, the number of normal DBA duties. The second one is 3 times 8, the number of hours per day that the DBA is expected to perform those duties. The third one is 3 divided by 8 (times 1000), roughly the number of days in a year that the DBA must do his job. The last one is 3 in the 8th power, roughly what the DBA should be paid per hour 😉
Further on the first thought:
1. Software installation and Maintenance
2. Data Extraction, Transformation, and Loading
3. Specialised Data Handling
4. Database Backup and Recovery
5. Security
6. Authentication
7. Capacity Planning
8. Performance Monitoring
9. Database Tuning
10. Troubleshooting
11. Education, education, education
-
Good luck, Lynn, Hope you find something.
- Eirikur Eiriksson wrote:Jeff Moden wrote:
On the same note, you're not yet told me what you think the 3|8 number means in the life of a DBA. My hint would be "It Depends" on how you look at it. 😀
I should remember this as you've told me this before, the problem is that my memory is as leaky as an old coffee filter.
😎
The first thought is that 3 OR 8, the number of normal DBA duties. The second one is 3 times 8, the number of hours per day that the DBA is expected to perform those duties. The third one is 3 divided by 8 (times 1000), roughly the number of days in a year that the DBA must do his job. The last one is 3 in the 8th power, roughly what the DBA should be paid per hour 😉
Further on the first thought:
1. Software installation and Maintenance
2. Data Extraction, Transformation, and Loading
3. Specialised Data Handling
4. Database Backup and Recovery
5. Security
6. Authentication
7. Capacity Planning
8. Performance Monitoring
9. Database Tuning
10. Troubleshooting
11. Education, education, education
All great thoughts but that's not it. Remember the hint was "It Depends on how you look at it". Look at it like this and with the understanding that it's not actually a number but a glyph...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Eirikur Eiriksson wrote:Jeff Moden wrote:
On the same note, you're not yet told me what you think the 3|8 number means in the life of a DBA. My hint would be "It Depends" on how you look at it. 😀
I should remember this as you've told me this before, the problem is that my memory is as leaky as an old coffee filter.
😎
The first thought is that 3 OR 8, the number of normal DBA duties. The second one is 3 times 8, the number of hours per day that the DBA is expected to perform those duties. The third one is 3 divided by 8 (times 1000), roughly the number of days in a year that the DBA must do his job. The last one is 3 in the 8th power, roughly what the DBA should be paid per hour 😉
Further on the first thought:
1. Software installation and Maintenance
2. Data Extraction, Transformation, and Loading
3. Specialised Data Handling
4. Database Backup and Recovery
5. Security
6. Authentication
7. Capacity Planning
8. Performance Monitoring
9. Database Tuning
10. Troubleshooting
11. Education, education, education
All great thoughts but that's not it. Remember the hint was "It Depends on how you look at it". Look at it like this and with the understanding that it's not actually a number but a glyph...
Work over infinity, Jeff, you will have to elaborate further on this one 😉
😎
- Eirikur Eiriksson wrote:
I thought, baggy eyes, toothless grin and a double chin.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 65,056 through 65,070 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply