Are the posted questions getting worse?
-
Just for fun on a Friday, can anyone tell me what 0x383338313736 or in other words 838176 means?
😎
- Eirikur Eiriksson wrote:
Just for fun on a Friday, can anyone tell me what 0x383338313736 or in other words 838176 means?
😎
Hmmm. I've read it upside down, but they aren't words I know. It must be something else, but it's interesting.
- Eirikur Eiriksson wrote:
Just for fun on a Friday, can anyone tell me what 0x383338313736 or in other words 838176 means?
😎
Grey
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Gents, you can do better than that!
😎
Now think inside your (tool)box!
-
This was removed by the editor as SPAM
-
Looks like a zoom password
412-977-3526 call/text
-
If you make the sheet text only it doesn't
I think what Excel is poor on is documenting the way they process the data.
412-977-3526 call/text
- Robert Sterbal wrote:
If you can send me a file I'll give it a whirl on how I would process it. robert@sterbal.com
The file is posted above:
12345678901234567890,MARCH1
I named it "FaultyImport.csv", but feel free to use any other name, as long as it's ",csv"
_____________
Code for TallyGenerator - Lynn Pettis wrote:
I have a little bit of good news, sort of. I have managed to find work at Parsons that has extended my stay to the end of September. Better than the original date of 8/20. What is interesting is hearing that there are groups and projects out there using SQL Server, both internal and client facing, that don't have actual DBA resources working with them. What was that Red Adair quote again?
--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!
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
Viewing 15 posts - 65,056 through 65,070 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply