Are the posted questions getting worse?
- Eirikur Eiriksson wrote:Sergiy wrote:Robert Sterbal wrote:
In nearly 30 years of using excel there are very few situations that don't have a straight forward work around.
built a report from an SQL database for a big international customer. The actual recipients of the report were their customers. So, the output was requested in the most common format - CSV.
Almost immediately they started complaining about incorrect account numbers in the report. Tirned out, 18 digit account numbers have been converted to float numbers while importing to Excel.
Can you suggest a workaround for this issue?
Apart from moving to Open Office?
Depends on the version of Excel, current versions are fully compatible with Open / Libre Office and can use the same file standard.
😎
not fully compatible.
MS Office reads csv files like they are native, with no questions asked.
Open / Libre Office read those file via "import file" wizard, which allows with 3 clicks force "text" data type over any particular column - problem's solved. For those who are not enslaved by lousy MS products.
BTW, if those scientists would be using open office, the issue with gene names would not go anywhere beyond nearest coffee station, where they'd make couple of jokes about the silly issue, which shows up if you don't specify the right type for the column, and would proceed with their work.
The only sad aspect of this - that article would be never written, and we would probably never know about a gene named "MARCH1". Well, used to be named...
_____________
Code for TallyGenerator -
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)
-
This was removed by the editor as SPAM
- Eirikur Eiriksson wrote:
Did you run into those in your youth, the year looks about right 😉
Heh... I remember it well. 😀
--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:
Did you run into those in your youth, the year looks about right 😉
Heh... I remember it well. 😀
'twas the year of Y1.9K, when all the shopkeeper's scribes quills would dry up due to a rare goosefeather disease....
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses - jonathan.crawford wrote:Jeff Moden wrote:Eirikur Eiriksson wrote:
Did you run into those in your youth, the year looks about right 😉
Heh... I remember it well. 😀
'twas the year of Y1.9K, when all the shopkeeper's scribes quills would dry up due to a rare goosefeather disease....
Dude! How'd you know about that? 😀
--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:
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.
_____________
Code for TallyGenerator - Robert Sterbal wrote:
It takes less than 10 keystrokes to convert a column to text. (Ctrl + space, alt + 1, alt + C, t, t enter)
I'm getting the impression that you think a product for hundreds of millions of users has to work the way you want it to. If it doesn't, the product is bad.
Can you suggest any set of keystrokes which would fix the issue described above? Any length of a set would do.
A sequence of digit not necessarily is a number. It's a common knowledge.
Not every string containing "March", "June", "August" is a date. You must know that OCT31 = DEC25.
If a product does not allow to handle such situations - yes, it's bad.
_____________
Code for TallyGenerator - 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!
😎
Viewing 15 posts - 65,041 through 65,055 (of 66,547 total)
You must be logged in to reply to this topic. Login to reply