Are the posted questions getting worse?
- Sergiy wrote:Eirikur Eiriksson wrote:
The import process is a different type of potatoes.
😎
OOXML and MOX, Open Office XML and Microsoft Open XML file standards are the same. I've tested around 17 different OOXML compliant applications and never had any problems, makes me wonder if this is a piknic (Problem in keyboard, not in computer)
You're not listening.
Did you bother to read the article? Did you figure out where the problem is?
a scientist might fix their data but export it as a CSV file without saving the formatting. Or, another scientist might load the data without the correct formatting, changing gene symbols back into dates. The end result is that while knowledgeable Excel users can avoid this problem, it’s easy for mistakes to be introduced.
So, the problem is exactly there - in importing data from files without specified correct formatting.
This is exactly the kind of potatoes we are talking about.
And actual problem is - there is no way around it while using MS Excel.
Simple exercise for you.
Create a simple text file:
12345678901234567890,MARCH1
Rename it to "SomeName.csv" and open it in MS Excel.
File is loaded automatically and contains this:
See those zero's at the end? You may fix the format of the cell, but you cannot get those last 5 digits back.
same about the cell B1.
But if I open the same file in Libre Office, I'm greeted with this screen:
If I proceed with "Standard" I'll get pretty much the same result as from MS Excel, except "MARCH1" won't be distorted.
But I can easily set the right format here:
And the loaded file data looks like this:
Now, I'm ready to listen to another cool story about OOXML and MOX standards, hopefully it will help explain to thousands of customers around the globe how to load 19 digit account numbers into their Excel spreadsheets without actually losing those account numbers.
This is in compliance with the IEEE 754 specification floating-point numbers. Excel only stores 15 significant digits in a number, and digits after the fifteenth place are zeroes. Entering the number manually, without formatting the cell as text will result in the same, 15 digits trailed by zeros.
😎
The workaround is simple Alt+A, FT, select the file and click Import, never ever open a CSV file directly in Excel!.
-
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:)
-
As far as I know Excel uses the same logic for converting typed, pasted and imported text. If you select a sheet can switch it all to text, everything is imported as it is written.
412-977-3526 call/text
- Robert Sterbal wrote:
As far as I know Excel uses the same logic for converting typed, pasted and imported text. If you select a sheet can switch it all to text, everything is imported as it is written.
Unfortunately, that is not correct. Whilst Excel can store a large number of digits in a cell, once it is thought to be a number, the IEEE 754 handling will kick in.
😎
Importing without data detection is, as far as I know, the only way to get large numerical values into an Excel spreadsheet.
-
If you can send me a file I'll give it a whirl on how I would process it. robert@sterbal.com
412-977-3526 call/text
-
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?
- 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?
That's great news Lynn, best of luck!
😎
-
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!
-
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)
Viewing 15 posts - 65,041 through 65,055 (of 66,819 total)
You must be logged in to reply to this topic. Login to reply