dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
1972
share rss forum feed


joako
Premium
join:2000-09-07
/dev/null
kudos:6

[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



Hall
Premium,MVM
join:2000-04-28
Germantown, 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.


joako
Premium
join:2000-09-07
/dev/null
kudos:6

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



thegeek
Premium
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.



rchandra
Stargate Universe fan
Premium
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!



cowboyro
Premium
join:2000-10-11
Shelton, CT
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).



f0rtys3ven
47

join:2011-09-01
Lansing, MI
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.



cowboyro
Premium
join:2000-10-11
Shelton, CT
Reviews:
·AT&T U-Verse

said by f0rtys3ven:

.csv is a crap exenstion.

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.


rchandra
Stargate Universe fan
Premium
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!



f0rtys3ven
47

join:2011-09-01
Lansing, MI
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.


TheMG
Premium
join:2007-09-04
Canada
kudos:3
Reviews:
·NorthWest Tel
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.


sivran
Opera ex-pat
Premium
join:2003-09-15
Irving, 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.



thegeek
Premium
join:2008-02-21
right here
kudos:2
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.


f0rtys3ven
47

join:2011-09-01
Lansing, MI

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.



thegeek
Premium
join:2008-02-21
right here
kudos:2

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.



f0rtys3ven
47

join:2011-09-01
Lansing, MI

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.



cowboyro
Premium
join:2000-10-11
Shelton, CT
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.


cowboyro
Premium
join:2000-10-11
Shelton, CT
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...


f0rtys3ven
47

join:2011-09-01
Lansing, MI
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?



f0rtys3ven
47

join:2011-09-01
Lansing, MI
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.



thegeek
Premium
join:2008-02-21
right here
kudos:2
reply to f0rtys3ven

Access does to pivot charts. Much better than Excel in my opinion.



f0rtys3ven
47

join:2011-09-01
Lansing, MI

spoke with my boss and I guess the company may have some access liscences and if it already does Pivot charts we will look into that. Our data mining effort started with my arrival a little over a year ago and we have set up roughly 20 different tables of stats data to pull from. They have varied ages of data. The first 4 I created are just now at a year old and I have had to implement a little pruning and summarizing on those. Some of the tables will be VERY big by the time they get a year so Access may be the way to go.

Thanks ALOT geek!
--
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.



drew
Automatic
Premium
join:2002-07-10
Port Orchard, WA
kudos:6
reply to joako

As a dude who gets paid to deal with Access databases and their baggage... Please stay away. Don't do it.

Eventually, the process you've made gets to be business-crucial. Then you get a promotion to the head office three states over. Or get hit by a milk truck. Or retire.

Then when Access craps itself, and if you don't think it does, you're deluding yourself, you're up a creek.
--
flickr | 'Cause I've been waiting, all my life just waiting
For you to shine, shine your light on me



PhoenixDown
FIOS is Awesome
Premium
join:2003-06-08
Fresh Meadows, NY
kudos:1
reply to joako

You can use excel to connect to an access db, excel will generate the pivot tables.



fruhead

join:2002-01-29
Mosquito,NJ
reply to joako

csv

(cool story, vro)



thegeek
Premium
join:2008-02-21
right here
kudos:2
reply to drew

said by drew:

As a dude who gets paid to deal with Access databases and their baggage... Please stay away. Don't do it.

Eventually, the process you've made gets to be business-crucial. Then you get a promotion to the head office three states over. Or get hit by a milk truck. Or retire.

Then when Access craps itself, and if you don't think it does, you're deluding yourself, you're up a creek.

Access is great as a front end. Never use it as a back end though.


fruhead

join:2002-01-29
Mosquito,NJ

said by thegeek:

Access is great as a front end. Never use it as a back end though.

I never let anyone access my back end...

NTTAWWT, of course...


cowboyro
Premium
join:2000-10-11
Shelton, CT
Reviews:
·AT&T U-Verse
reply to thegeek

said by thegeek:

Access is great as a front end. Never use it as a back end though.

What's so good about it? Yes you can do some quick and dirty data entry easily, but for anything more writing a proper front-end from the scratch in VB.NET or C# will yield a much better product for about the same effort (if not less).


drew
Automatic
Premium
join:2002-07-10
Port Orchard, WA
kudos:6

I develop in both... and there's no way that even VB.NET is as easy or more easy than Access.

Access' strong point is the fact that there are 101 wizards for everything and hooking it up to data is point-and-click that even my non-programmer, non-interested wife can figure out.

Access is the bastard stepchild of the Office suite. It IS a good idea, but companies don't set strong policies about its use and support. No LOB apps should ever be access-driven. PoC it in Access as the business analyst, get management buy in that this needs to happen and hand it off to the IT/IS department for proper development.
--
flickr | 'Cause I've been waiting, all my life just waiting
For you to shine, shine your light on me



thegeek
Premium
join:2008-02-21
right here
kudos:2

Access is great for the simple fact that IT doesn't support it. Meaning if I need a simple front end for gathering and manipulating some data and don't want to wait for forever and a day for IT to create something halfassed in VB.NET then I can just do it myself in Access and be set.