 joakoPremium
join:2000-09-07
/dev/null
kudos:5
 ·Comcast
| [Rant] SPREADSHEETs I hate shit that assumes it is smarter than you. Case in point spreadsheets. They think they know better than you. They think that for whatever reason your perfectly crafted .CSV file is invalid and needs to be "fixed."
I thought I had come up with the perfect solution: OpenOffice.org software and opening the CSV file with every column as type "text" so it does not mangle the contents.
Well I get my 2 CSV files that contains dates, addresses and URLs. I open both as TEXT and then I copy and paste the contents of one file to create one merged file with all the data. Then I sort it and make some addresss corrections. 1 hour later when I am done the dates are mangled. The data I pasted from the other file has dates such as "334382" instead of "6/20/2012"
So now 1 more hour to re-do this. And there's no spreadsheet software that doesn't mangle your data. They all thing they are smarter than you.
--
PRescott7-2097 |
|
 HallPremium,MVM
join:2000-04-28
Dayton, OH
kudos:2 | said by joako:1 hour later when I am done the dates are mangled. The data I pasted from the other file has dates such as "334382" instead of "6/20/2012" Change those cells to "date" format. |
|
|
|
joakoPremium
join:2000-09-07
/dev/null
kudos:5 Reviews:
·Comcast
| Nope, once mangled no way back.
E.g. a cell with the aparment number "5-20" becomes 5/20/2012 and that converts back to "number" as "41049.00"
Actually that seems to be days since Jan 1 1900.... Still WTF.
--
PRescott7-2097 |
|
 thegeekPremium
join:2008-02-21
right here
kudos:2 | reply to joako
Do it the right way. Link to the files in Access and write a union query.
You can also change the settings is Excel to not attempt to auto-format your data. |
|
 rchandraStargate Universe fanPremium
join:2000-11-09
14225-2105 | reply to joako
I dislike autoconversion as much as the next guy. If I told it the column or row is text, I want it treated just as text.
However...I have to wonder if you're doing as so many people do, and using a spreadsheet as a database. I don't like people who do that. Unless there are calculations to be done on the contents, doesn't seem like it belongs in a spreadsheet...belongs in a database. It might be kind of handy to take a quick look at data or facilitate ad hoc transfers between entities (say, two companies which have no direct network ties, just Internet access).
Sorry...maybe this belongs as a rant on its own about using spreadsheets as databases...
--
English is a difficult enough language to interpret correctly when its rules are followed, let alone when a writer chooses not to follow those rules.
Jeopardy! replies and randomcaps REALLY suck! |
|
Reviews:
 ·AT&T U-Verse
| reply to joako
It boils down to people not knowing how to save in a proper format. If you go CSV make sure all text fields are qualified by quotes. CSV saves no information about the data type in a column - so it has to be guessed each time unless it's enclosed by quotes when it will be treated as plain text. If you want to preserve data types use a proper spreadsheet format such as XLS(X). |
|
| reply to joako
.csv is a crap exenstion.
I always rename them as .txt and explicitly tell Excel how to deal with the delimiting chars.
As far as once mangled theres no going back. Theres clearly a back button. And it will go a long ways on Excel. Also, save regularly and if you are unsure of your actions save different copies and delete old ones when you are satisfied.
I use spreadsheets everyday. We are using Excel to dynamically pull statistics data about our DB2 system and chart it so we can see trends both hourly and daily and in a few weeks monthly as well. Pivot charts are SOOO COOL!
Keep with it. You will find more uses for it than you can think. You just have to become smarter than the smartness.
--
Call it evil that men do, lord forgive me for what my pen do
This is for your sins, I cleanse you. You can repent but I warn you, if you continue. To hell I’ll send you. |
|
Reviews:
·AT&T U-Verse
| It's not. It's just an extension like .exe, .crap, .xls, .pif, ...
It's the data inside the file that matters... of course, along with the software guessing that a file with a certain extension contains data in a certain format. |
|
rchandraStargate Universe fanPremium
join:2000-11-09
14225-2105 | I wish more OSes were like Unix in that it's almost always magic numbers which matter, not the last (usually 4) characters of the pathname. There's only a handful of programs which care. upstart is one of them (must end in ".conf" or file is ignored), the GNOME Panel (".desktop") is another.
--
English is a difficult enough language to interpret correctly when its rules are followed, let alone when a writer chooses not to follow those rules.
Jeopardy! replies and randomcaps REALLY suck! |
|
| reply to cowboyro
Excel really only likes .csv that itself saved. Anything else and you will prolly get something weird. Changing it to.txt allows you tell excel how the data is formated. I understand file extensions, i was talking in the contex of the thead which was about Excel.
--
Call it evil that men do, lord forgive me for what my pen do
This is for your sins, I cleanse you. You can repent but I warn you, if you continue. To hell I’ll send you. |
|
TheMGPremium
join:2007-09-04
Canada
kudos:1 | reply to joako
said by joako:I hate shit that assumes it is smarter than you. Case in point spreadsheets. They think they know better than you.
Unfortunately it's not just spreadsheets. One of my biggest gripes is how Windows Vista/7 thinks it knows what view you would like to have for a folder. I've had to turn that crap off in the registry on all my computers. Annoying as hell.
I hate it when computers try to guess what I want. Absolutely hate it.
Unfortunately, "computer guessing" is becoming an increasingly more common practice.  |
|
 sivranOpera convertPremium
join:2003-09-15
Arlington, TX
kudos:1 | Yes, Vista/7 is much more annoying about that than XP. In XP, it only does that on a few folders and generally remembers what it's told if you change it. Meanwhile, Vista/7 seem to just decide to do it based on folder contents.. ugh
--
Think Outside the Fox. |
|
thegeekPremium
join:2008-02-21
right here
kudos:2 Reviews:
·Suddenlink
| reply to f0rtys3ven
said by f0rtys3ven:I use spreadsheets everyday. We are using Excel to dynamically pull statistics data about our DB2 system and chart it so we can see trends both hourly and daily and in a few weeks monthly as well. Pivot charts are SOOO COOL!
You're doing it wrong. Access is the way to go for this. |
|
| I am not sure we have access. All the data sits in DB2 tables. Excel simply goes and gets it and then puts it directly into a Pivot Chart so we can than easily change the graph to look at different statistics.
It works really really well and has brought our system moniotoring and overall knowledge of our daily activity to a new high.
--
Call it evil that men do, lord forgive me for what my pen do
This is for your sins, I cleanse you. You can repent but I warn you, if you continue. To hell I’ll send you. |
|
thegeekPremium
join:2008-02-21
right here
kudos:2 Reviews:
·Suddenlink
| All you need is one copy of Access for development. Then install the freely available Access Run-Time on each machine that needs to run the program.
If you never need to combine information from more than one table, or filter information from a table then it might be overkill and Excel would be fine to use. However, if you have a query running to create the table you export from DB2 then you are unnecessarily using up disk space if the resulting data is only exported to Excel. You'd be better off just writing a pass-through query in Access.
I think Excel is evil and should go away. Microsoft is slowly incorporating more and more of Excel's pivot table and charting abilities into Access and I think that Excel's days are numbered. I think it will be a combined product eventually. |
|
| I am in fact doing all that stat consolidating in DB2. The tables hold just the data we want to look at. Also, we run on a mainframe so resources aren't exactly sparse. We have had zero performance issues with this process so far and we have some massive data tables we can push into Excel with out any issues.
Does Access have pivot charts now?
--
Call it evil that men do, lord forgive me for what my pen do
This is for your sins, I cleanse you. You can repent but I warn you, if you continue. To hell I’ll send you. |
|
Reviews:
·AT&T U-Verse
| reply to f0rtys3ven
said by f0rtys3ven:Excel really only likes .csv that itself saved. Anything else and you will prolly get something weird. Changing it to.txt allows you tell excel how the data is formated. I understand file extensions, i was talking in the contex of the thead which was about Excel.
It makes no sense whatsoever to save excel data as csv or txt (unless you have to continuously share it with dumb programs). You save it in a proper format (xlsx, xlsm, xls) and preserve all computations and formatting. No need to guess each time you open the file. |
|
Reviews:
·AT&T U-Verse
| reply to f0rtys3ven
said by f0rtys3ven:I am in fact doing all that stat consolidating in DB2. The tables hold just the data we want to look at. You can use native SQL data query in Excel. No need to export as text... |
|
| reply to cowboyro
Ya think.
We are talking data that isn't in Excel, but is in .csv form from something else. Did you read the thread? |
|
| reply to cowboyro
You are not contributing anything.
If I am pulling from DB2 don't you think I know what Excel is capeable of in that area?
NO ONE is even speaking of exporting data as .txt.
--
Call it evil that men do, lord forgive me for what my pen do
This is for your sins, I cleanse you. You can repent but I warn you, if you continue. To hell I’ll send you. |
|
