Dangers of using Excel as a database

[Steve Hayes]

When we go to archives to record genealogical and historical
information, I usa a databade program,. askSam, but my wife uses a
spreadsheet, because she is an accountant, and that isd what she is
familiar with.

But when we try to print the information out for filing, Excel has
"helpfully" changed it.

For example, one archival reference was MOOC 13/1/3119

Excel printed this out as 13/01/19, which would be a quite different
file in the archives filing system, and so useless as a reference.

I have notice this behaviour elsewhere, when I was using a spreadsheet
to index books, because I wound it easier than setting up a database
program to do it.

Even if one defined a column as "Text" in Excel, it still interpreted
some entries as dates, and changed them.

Eventually I used Libre Office Calc, which did not seem to have that
particular problem.

Spreadsheets can sometimes be useful for simple "flat file" database
projects, but the erratic behaviour of Excel can be a pitfall for the
unwary.



--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

[GS]

This is dependent on how you output to file. If you use Excel's
built-in methods then expect these kinds of issues. Using VBA standard
file I/O methods is a much more efficient way to write worksheet data
to a file, or file data to a worksheet.

While Libre Calc doesn't have some of Excel's irritating nuances, it
does have a few of its own. It's still, though, the best 'free'
spreadsheet program available. (WPS might qualify as a good runnerup
for 2nd place)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[Ian Goddard]

On 05/07/15 15:22, Steve Hayes wrote:
> When we go to archives to record genealogical and historical
> information, I usa a databade program,. askSam, but my wife uses a
> spreadsheet, because she is an accountant, and that isd what she is
> familiar with.

IT departments often encounter this situation, especially with accountants.

Write out 100 times "A spreadsheet is not a database"

But in this particular case were MOOC and 13/1/3119 in different
columns? If not this seems a particularly egregious error - even if
making assumptions about date-like strings is acceptable a spreadsheet
really shouldn't be trying to parse sub-strings to look for dates.

--
Hotmail is my spam bin. Real address is ianng
at austonley org uk

[GS]

Not trying to be combative...
When I enter 13/1/3119 or 1/13/3119 in a cell, format doesn't change
(the cell is formatted 'General').

A spreadsheet is nothing more than a glorified grid control. Grid
controls are what users typically use to work with data stored in a
database file. It doesn't really matter what the filetype is, but
matters more *how the data is handled* between the file and the grid
control.

Thus, the key to manipulating your data in a specific grid control lies
in *knowing how to use the grid control effectively for the task at
hand*!

[Steve Hayes]

If you look it up in the online index, MOOC and 13/1/3119 are in
separate fields.

In this example:

DEPOT KAB
SOURCE MOOC
TYPE LEER
VOLUME_NO 13/1/2121
SYSTEM 01
REFERENCE 46
PART 1
DESCRIPTION HOFFMAN, ELIZABETH MOUNT. LIQUIDATION AND
DISTRIBUTION ACCOUNT.
STARTING 19130000
ENDING 19130000

Excel turned the Volume No into a date -- 13/01/21

I'm not sure that Microsoft Access is much better, however. I once
tried to make a fairly simple database with names, addresses and dates
of birth, and the first person I entered had a birthdate of 1 Jan
1926, which MS Access changed to 1 Jan 2026, even though I typed in
the full date. I wasted a couple of days trying to get it to wrok,
then went back to using askSam, but my wife prefers to use
spreadsheets, because that is what she is familiar with.

Next time we go to the archives I'll try to persuade her to use
OneNote.

[Steve Hayes]

On Sun, 05 Jul 2015 14:37:54 -0400, GS <g...@v.invalid> wrote:

>> On 05/07/15 15:22, Steve Hayes wrote:
>>> When we go to archives to record genealogical and historical
>>> information, I usa a databade program,. askSam, but my wife uses a
>>> spreadsheet, because she is an accountant, and that isd what she is
>>> familiar with.
>>
>> IT departments often encounter this situation, especially with
>> accountants.
>>
>> Write out 100 times "A spreadsheet is not a database"
>>
>> But in this particular case were MOOC and 13/1/3119 in different
>> columns? If not this seems a particularly egregious error - even if
>> making assumptions about date-like strings is acceptable a
>> spreadsheet really shouldn't be trying to parse sub-strings to look
>> for dates.
>
>Not trying to be combative...
>When I enter 13/1/3119 or 1/13/3119 in a cell, format doesn't change
>(the cell is formatted 'General').

But did you try to print it?

When I said the records were wrong, my wife checked, and the data was
as she entered it. It was in printing that the entry was changed.

[GS]

> But did you try to print it?
>
> When I said the records were wrong, my wife checked, and the data was
> as she entered it. It was in printing that the entry was changed.

It prints exactly as displayed whether I open the file in Excel 9, 10,
11, 12, or 14 on either of my XP Pro SP3, Win7 Pro, or Win8.1 machines!

[GS]

Like I already stated...

*it matters more how the data is handled between the database file and
the grid control*

..meaning the method used to import the data to the worksheet.

[Steve Hayes]

On Sun, 05 Jul 2015 20:39:36 -0400, GS <g...@v.invalid> wrote:

>> But did you try to print it?
>>
>> When I said the records were wrong, my wife checked, and the data was
>> as she entered it. It was in printing that the entry was changed.
>
>It prints exactly as displayed whether I open the file in Excel 9, 10,
>11, 12, or 14 on either of my XP Pro SP3, Win7 Pro, or Win8.1 machines!

Well then the version on my wife's computer can't be any of those,
because hers *did* behave like that.

For what it's worth, it came with MS Office 10.

[GS]

> On Sun, 05 Jul 2015 20:39:36 -0400, GS <g...@v.invalid> wrote:
>
>>> But did you try to print it?
>>>
>>> When I said the records were wrong, my wife checked, and the data
>>> was as she entered it. It was in printing that the entry was
>>> changed.
>>
>> It prints exactly as displayed whether I open the file in Excel 9,
>> 10, 11, 12, or 14 on either of my XP Pro SP3, Win7 Pro, or Win8.1
>> machines!
>
> Well then the version on my wife's computer can't be any of those,
> because hers *did* behave like that.
>
> For what it's worth, it came with MS Office 10.

Do you mean MS Office 2010, or MS Office 2002 (v10)? Regardless, I
tested with all versions from v9 to v14 across the 3 OSs I mentioned.
Are you sure she doesn't have it set up to only display 2-digit years?

[Denis Beauregard]

On Sun, 05 Jul 2015 16:22:34 +0200, Steve Hayes
<haye...@telkomsa.net> wrote in soc.genealogy.computing:

>For example, one archival reference was MOOC 13/1/3119
>
>Excel printed this out as 13/01/19, which would be a quite different
>file in the archives filing system, and so useless as a reference.

I was using formerly Excel on my main computer and OpenOffice
on the laptop, now LibreOffice on both.

OO and LO accepted for years to enter pre-1900 dates as dates
while Excel considered them as texts. I found a method to
handle that similarly, by prepending a ' to the cell.

So enter '13/1/3119 to keep it as is. I enter all complete dates
like that, even after 1900. In a few cases, the quote is still
shown, when the date is wrong, i.. '12-31-2000 will be right
but '13-31-2000 will show the quote.


Denis

--
Denis Beauregard - généalogiste émérite (FQSG)
Les Français d'Amérique du Nord - www.francogene.com/genealogie--quebec/
French in North America before 1722 - www.francogene.com/quebec--genealogy/
Sur cédérom à 1785 - On CD-ROM to 1785

[GS]

For clarity...

Office9 is MSO2000
Office10 is MSO2002
Office11 is MSO2003
Office12 is MSO2007
Office14 is MSO2010
...

[Steve Hayes]

On Mon, 06 Jul 2015 10:20:41 -0400, Denis Beauregard
<denis.b-at-f...@fr.invalid> wrote:

>OO and LO accepted for years to enter pre-1900 dates as dates
>while Excel considered them as texts. I found a method to
>handle that similarly, by prepending a ' to the cell.
>
>So enter '13/1/3119 to keep it as is. I enter all complete dates
>like that, even after 1900. In a few cases, the quote is still
>shown, when the date is wrong, i.. '12-31-2000 will be right
>but '13-31-2000 will show the quote.

Thanks very much, I'll suggest to my wife that she do that with all
fields where numerals are separated with slashes.

We're not going to waste the paper and ink to reprint the ones that
were wrong -- I'll just correct them by pen.

[Steve Hayes]

On Tue, 07 Jul 2015 09:06:12 -0400, Dennis Lee Bieber
<bieber.g...@earthlink.net> wrote:

>On Tue, 07 Jul 2015 04:58:36 +0200, Steve Hayes <haye...@telkomsa.net>
>declaimed the following:

>
>
>>
>>Thanks very much, I'll suggest to my wife that she do that with all
>>fields where numerals are separated with slashes.
>>

> May or may not help, but...
>http://exceluser.com/formulas/earlydates.htm

No, the problem is not early dates, but rather Excel treating
non-dates as if they were dates.

It seems that if it finds one field in a column that looks as though
it could possibly be a date, then it will treat other fields in that
column as dates too, and store them as numerals.

So even if it is formatted to *display* as text, as entered, if one
pronts it, or saves it in a a CSV style, it will print or store the
wrong value.

It seems that the only way out of it is to precede every entry in the
field (column) with a '

[GS]

> It seems that if it finds one field in a column that looks as though
> it could possibly be a date, then it will treat other fields in that
> column as dates too, and store them as numerals.
>
> So even if it is formatted to *display* as text, as entered, if one
> pronts it, or saves it in a a CSV style, it will print or store the
> wrong value.

So for 1 *last time*...

If you *do not* use Excel's methods to import/output the data, but use
VBA standard file I/O methods OR ADODB, the data transfers *'as is'*
without being changed by Excel.

Using Excel's import methods gives Excel license to *interpret* data
type! As you're experiencing here this is not what you want to happen*!

[Steve Hayes]

On Wed, 08 Jul 2015 09:03:19 -0400, GS <g...@v.invalid> wrote:

>> It seems that if it finds one field in a column that looks as though
>> it could possibly be a date, then it will treat other fields in that
>> column as dates too, and store them as numerals.
>>
>> So even if it is formatted to *display* as text, as entered, if one
>> pronts it, or saves it in a a CSV style, it will print or store the
>> wrong value.
>
>So for 1 *last time*...
>
>If you *do not* use Excel's methods to import/output the data, but use
>VBA standard file I/O methods OR ADODB, the data transfers *'as is'*
>without being changed by Excel.
>
>Using Excel's import methods gives Excel license to *interpret* data
>type! As you're experiencing here this is not what you want to happen*!

So you have to learn VBA and create a VBA routine for keyboard entry
to get Excel to record what you actually typed? And if you don't it
changes what you typed into something else, willy nilly?

[GS]

> On Wed, 08 Jul 2015 09:03:19 -0400, GS <g...@v.invalid> wrote:
>
>>> It seems that if it finds one field in a column that looks as
>>> though it could possibly be a date, then it will treat other fields
>>> in that column as dates too, and store them as numerals.
>>>
>>> So even if it is formatted to *display* as text, as entered, if one
>>> pronts it, or saves it in a a CSV style, it will print or store the
>>> wrong value.
>>
>> So for 1 *last time*...
>>
>> If you *do not* use Excel's methods to import/output the data, but
>> use VBA standard file I/O methods OR ADODB, the data transfers *'as
>> is'* without being changed by Excel.
>>
>> Using Excel's import methods gives Excel license to *interpret* data
>> type! As you're experiencing here this is not what you want to
>> happen*!
>
> So you have to learn VBA and create a VBA routine for keyboard entry
> to get Excel to record what you actually typed? And if you don't it
> changes what you typed into something else, willy nilly?

No! You just don't use Excel's built-in import features. Once the data
is imported using VBA it can be manipulated however you like because
Excel hasn't 'interpreted' its data types during the import process.

IOW, printing your sample data over 5 versions of Excel running on 3
different OSs didn't change how the data printed. I understand that
this is what you want! Or am I mistaken?

[GS]

I forgot to mention there are many samples of code available for using
a macro to import data to a worksheet, and so you really don't have to
learn how to use VBA to run a ready-made macro. Just assign the macro
to a menuitem or button. You can store it in PERSONAL.XLS so it's
always available.

[Steve Hayes]

On Wed, 08 Jul 2015 15:19:32 -0400, GS <g...@v.invalid> wrote:

>I forgot to mention there are many samples of code available for using
>a macro to import data to a worksheet, and so you really don't have to
>learn how to use VBA to run a ready-made macro. Just assign the macro
>to a menuitem or button. You can store it in PERSONAL.XLS so it's
>always available.

I was talking primarily about data entry by keyboard.

And it seems that the solution is always to type ' at the beginning of
every cell in a column that contains numerals and slashes but is not a
date, otherwise there is a risk of Excel interpreting and storing it
as a date, so that even though it may *display* correctly, it will not
print or export correctly.

[GS]

> On Wed, 08 Jul 2015 15:19:32 -0400, GS <g...@v.invalid> wrote:
>
>> I forgot to mention there are many samples of code available for
>> using a macro to import data to a worksheet, and so you really
>> don't have to learn how to use VBA to run a ready-made macro. Just
>> assign the macro to a menuitem or button. You can store it in
>> PERSONAL.XLS so it's always available.
>
> I was talking primarily about data entry by keyboard.
>
> And it seems that the solution is always to type ' at the beginning
> of every cell in a column that contains numerals and slashes but is
> not a date, otherwise there is a risk of Excel interpreting and
> storing it as a date, so that even though it may *display* correctly,
> it will not print or export correctly.

Yes, for direct entry this is how I input values I don't want
interpreted. Though, manually typing your data into cells didn't change
in my tests and so I suspect there's a setting somewhere that's causing
printouts to use 2-digit years. Not sure where/how because it doesn't
happen at my end.

Regardless, Excel's attempts at 'helpfulness' can really be annoying at
times!

[Dora Smith]

Microsoft Office has become a hoaky product, especially Excel. You may be happier with the Libre Office suite. I often use it to avoid the Microsoft projects' antics.

What you describe below can be fixed by formatting the cells, but from what you're saying that shouldn't be necessary.

I have had to format numbers as text to keep them from showing up as dates.

Dora