Are the posted questions getting worse?
- 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?
_____________
Code for TallyGenerator - Lynn Pettis wrote:
IIRC, there is also a difference in the dates in Excel and SQL Server. Only problem is that I don't quite remember what the problem, had I haven't had much luck with my google fu to find it.
Perhaps you are thinking of this?
1/1/1900 is 'day zero' in SQL Server, but 'day one' in Excel.
Here some T-SQL to back that up:
DECLARE @d DATETIME = '1900-01-01 00:00:00.000';
SELECT IntDate = CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(8), @d), 1, 4)); - Phil Parkin wrote:Lynn Pettis wrote:
IIRC, there is also a difference in the dates in Excel and SQL Server. Only problem is that I don't quite remember what the problem, had I haven't had much luck with my google fu to find it.
Perhaps you are thinking of this?
1/1/1900 is 'day zero' in SQL Server, but 'day one' in Excel.
Here some T-SQL to back that up:
DECLARE @d DATETIME = '1900-01-01 00:00:00.000';
SELECT IntDate = CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(8), @d), 1, 4));That is it, thank you.
- Lynn Pettis wrote:Phil Parkin wrote:Lynn Pettis wrote:
IIRC, there is also a difference in the dates in Excel and SQL Server. Only problem is that I don't quite remember what the problem, had I haven't had much luck with my google fu to find it.
Perhaps you are thinking of this?
1/1/1900 is 'day zero' in SQL Server, but 'day one' in Excel.
Here some T-SQL to back that up:
DECLARE @d DATETIME = '1900-01-01 00:00:00.000';
SELECT IntDate = CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(8), @d), 1, 4));That is it, thank you.
Heh... although I don't run into it often anymore, the other problem is that Excel thinks that 1900 was a leap year unless you use the screw ball dates that have a base date of March 1st. It only has to happen once to have to know what the "work arounds" are.
--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) - 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?
Only way I know is to put apostrophe before the number, it will appear when the file is loaded in excel but at least it will not be converted.
Far away is close at hand in the images of elsewhere.
Anon. - Jeff Moden wrote:Lynn Pettis wrote:Phil Parkin wrote:Lynn Pettis wrote:
IIRC, there is also a difference in the dates in Excel and SQL Server. Only problem is that I don't quite remember what the problem, had I haven't had much luck with my google fu to find it.
Perhaps you are thinking of this?
1/1/1900 is 'day zero' in SQL Server, but 'day one' in Excel.
Here some T-SQL to back that up:
DECLARE @d DATETIME = '1900-01-01 00:00:00.000';
SELECT IntDate = CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(8), @d), 1, 4));That is it, thank you.
Heh... although I don't run into it often anymore, the other problem is that Excel thinks that 1900 was a leap year unless you use the screw ball dates that have a base date of March 1st. It only has to happen once to have to know what the "work arounds" are.
Did you run into those in your youth, the year looks about right 😉
😎
The last BIFF file format has two different date recordings, the Date1900 and the Date1904, 0 date is 1st of Jan 1900 and 1904 respectfully. This should not be relevant to the OOXML of the current versions, IIRC, dates are stored in the cells value section unless defined as strings, then they are moved to the internal text table and no formatting is applied. The problem with the text values is obvious, no date logic can be applied without type conversion.
Mind you, Excel is a pathological liar, often what is displayed has little resemblance to the actual value stored.
- 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.
😎
- David Burrows 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?
Only way I know is to put apostrophe before the number, it will appear when the file is loaded in excel but at least it will not be converted.
that was the first thing I did - they were not quite satisfied.
Apostrophes had to be manually removed after importing, or all sorts of lookups they've been using would not work.
_____________
Code for TallyGenerator - 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)
- 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
Viewing 15 posts - 65,026 through 65,040 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply