Dates before 1900, MySQL-->OBDC-->Excel

I'm successfully importing tables and views from MySQL into Excel via ODBC,
except dates before 1900 and incomplete dates of any period. It's historical
data about the convicts with dates ranging from the 1700s to the mid 1900s.

Excel's query window shows the dates in the right format - eg. 1832-11-19,
or 1917-00-00, or blank for Null, but the cells in Excel don't accept them.
Nulls appear as empty cells - that's ok. Any date with a "00" for month or
day also appear as a blank cell - that's not ok. Complete dates before 1900
appear as "0/01/1900" - that's not ok. Complete dates after 1900 appear in
their correct date format as set by Excel.

I know that Excel can't handle dates before 1900 or incomplete dates. Does
anyone know a work-around so the pre-1900 and incomplete dates can appear as
text strings in the format "yyyy-mm-dd".

One workaround I found is to copy a table or results of a query or view in
Navicat and paste into Excel. That's not completely satisfactory. I have the
same problem with ODBC and Access where I want the latest versions of the
data without having to know what's been changed in MySQL. Using Navicat's
expert to Excel has the same problem.

MySQL vs. 5.0.20-nt
ODBC vs. 3-51-14
Excel vs.2002


Gary Luke
Sydney, Australia

Gary wrote:
> I'm successfully importing tables and views from MySQL into Excel via ODBC,
> except dates before 1900 and incomplete dates of any period. It's historical
> data about the convicts with dates ranging from the 1700s to the mid 1900s.
>
> Excel's query window shows the dates in the right format - eg. 1832-11-19,
> or 1917-00-00, or blank for Null, but the cells in Excel don't accept them.
> Nulls appear as empty cells - that's ok. Any date with a "00" for month or
> day also appear as a blank cell - that's not ok. Complete dates before 1900
> appear as "0/01/1900" - that's not ok. Complete dates after 1900 appear in
> their correct date format as set by Excel.
>
> I know that Excel can't handle dates before 1900 or incomplete dates. Does
> anyone know a work-around so the pre-1900 and incomplete dates can appear as
> text strings in the format "yyyy-mm-dd".
>
> One workaround I found is to copy a table or results of a query or view in
> Navicat and paste into Excel. That's not completely satisfactory. I have the
> same problem with ODBC and Access where I want the latest versions of the
> data without having to know what's been changed in MySQL. Using Navicat's
> expert to Excel has the same problem.
>
> MySQL vs. 5.0.20-nt
> ODBC vs. 3-51-14
> Excel vs.2002
>
>
> Gary Luke
> Sydney, Australia
>
>
>

And your MySQL question is?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Gary schreef:
> I'm successfully importing tables and views from MySQL into Excel via ODBC,
> except dates before 1900 and incomplete dates of any period. It's historical
> data about the convicts with dates ranging from the 1700s to the mid 1900s.
>
> Excel's query window shows the dates in the right format - eg. 1832-11-19,
> or 1917-00-00, or blank for Null, but the cells in Excel don't accept them.
> Nulls appear as empty cells - that's ok. Any date with a "00" for month or
> day also appear as a blank cell - that's not ok. Complete dates before 1900
> appear as "0/01/1900" - that's not ok. Complete dates after 1900 appear in
> their correct date format as set by Excel.
>
> I know that Excel can't handle dates before 1900 or incomplete dates. Does
> anyone know a work-around so the pre-1900 and incomplete dates can appear as
> text strings in the format "yyyy-mm-dd".
>

because Excel wont recognize dates before 1900 you will have to send
TEXTs to Excel

so, you are giving the 'workaround' yourself

i think you can convert a date to a string in MySQL using the
DATE_FORMAT() function.



--
Luuk

Gary wrote:
> Gary
> Luke --------------------------------------------------------------------------------
> Big City Production Services P/L Unit E, 13-21 Mandible St., Alexandria NSW
> 2015 Phone: (02) 9318-2354 Fax: (02) 9319-3929 Email: gary@bigcity.net.au
> Mobile: 0439-451-571 www.bigcity.net.au
> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
> news:M7OdnYRLreEULVfanZ2dnUVZ_qTinZ2d@comcast.com. ..
>> Gary wrote:
>>> I'm successfully importing tables and views from MySQL into Excel via
>>> ODBC, except dates before 1900 and incomplete dates of any period. It's
>>> historical data about the convicts with dates ranging from the 1700s to
>>> the mid 1900s.
>>>
>>> Excel's query window shows the dates in the right format - eg.
>>> 1832-11-19, or 1917-00-00, or blank for Null, but the cells in Excel
>>> don't accept them. Nulls appear as empty cells - that's ok. Any date with
>>> a "00" for month or day also appear as a blank cell - that's not ok.
>>> Complete dates before 1900 appear as "0/01/1900" - that's not ok.
>>> Complete dates after 1900 appear in their correct date format as set by
>>> Excel.
>>>
>>> I know that Excel can't handle dates before 1900 or incomplete dates.
>>> Does anyone know a work-around so the pre-1900 and incomplete dates can
>>> appear as text strings in the format "yyyy-mm-dd".
>>>
>>> One workaround I found is to copy a table or results of a query or view
>>> in Navicat and paste into Excel. That's not completely satisfactory. I
>>> have the same problem with ODBC and Access where I want the latest
>>> versions of the data without having to know what's been changed in MySQL.
>>> Using Navicat's expert to Excel has the same problem.
>>>
>>> MySQL vs. 5.0.20-nt
>>> ODBC vs. 3-51-14
>>> Excel vs.2002
>>>
>>>
>>> Gary Luke
>>> Sydney, Australia
>> And your MySQL question is?
>
>
> This was in my message.
>
> "Does anyone know a work-around so the pre-1900 and incomplete dates can
> appear as text strings in the format "yyyy-mm-dd"."
>
>
>
>
>
>

OK, then you just need to use DATE_FORMAT() to convert to a string.

Or use something other then Excel.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Gary
Luke --------------------------------------------------------------------------------
Big City Production Services P/L Unit E, 13-21 Mandible St., Alexandria NSW
2015 Phone: (02) 9318-2354 Fax: (02) 9319-3929 Email: gary@bigcity.net.au
Mobile: 0439-451-571 www.bigcity.net.au
"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:M7OdnYRLreEULVfanZ2dnUVZ_qTinZ2d@comcast.com. ..
> Gary wrote:
>> I'm successfully importing tables and views from MySQL into Excel via
>> ODBC, except dates before 1900 and incomplete dates of any period. It's
>> historical data about the convicts with dates ranging from the 1700s to
>> the mid 1900s.
>>
>> Excel's query window shows the dates in the right format - eg.
>> 1832-11-19, or 1917-00-00, or blank for Null, but the cells in Excel
>> don't accept them. Nulls appear as empty cells - that's ok. Any date with
>> a "00" for month or day also appear as a blank cell - that's not ok.
>> Complete dates before 1900 appear as "0/01/1900" - that's not ok.
>> Complete dates after 1900 appear in their correct date format as set by
>> Excel.
>>
>> I know that Excel can't handle dates before 1900 or incomplete dates.
>> Does anyone know a work-around so the pre-1900 and incomplete dates can
>> appear as text strings in the format "yyyy-mm-dd".
>>
>> One workaround I found is to copy a table or results of a query or view
>> in Navicat and paste into Excel. That's not completely satisfactory. I
>> have the same problem with ODBC and Access where I want the latest
>> versions of the data without having to know what's been changed in MySQL.
>> Using Navicat's expert to Excel has the same problem.
>>
>> MySQL vs. 5.0.20-nt
>> ODBC vs. 3-51-14
>> Excel vs.2002
>>
>>
>> Gary Luke
>> Sydney, Australia
>
> And your MySQL question is?


This was in my message.

"Does anyone know a work-around so the pre-1900 and incomplete dates can
appear as text strings in the format "yyyy-mm-dd"."

Gary wrote:
> Gary
> Luke --------------------------------------------------------------------------------
> Big City Production Services P/L Unit E, 13-21 Mandible St., Alexandria NSW
> 2015 Phone: (02) 9318-2354 Fax: (02) 9319-3929 Email: gary@bigcity.net.au
> Mobile: 0439-451-571 www.bigcity.net.au
> "Luuk" <Luuk@invalid.lan> wrote in message
> news:a8qq95-7t6.ln1@leafnode.a62-251-88-195.adsl.xs4all.nl...
>> Gary schreef:
>>> I'm successfully importing tables and views from MySQL into Excel via
>>> ODBC, except dates before 1900 and incomplete dates of any period. It's
>>> historical data about the convicts with dates ranging from the 1700s to
>>> the mid 1900s.
>>>
>>> Excel's query window shows the dates in the right format - eg.
>>> 1832-11-19, or 1917-00-00, or blank for Null, but the cells in Excel
>>> don't accept them. Nulls appear as empty cells - that's ok. Any date with
>>> a "00" for month or day also appear as a blank cell - that's not ok.
>>> Complete dates before 1900 appear as "0/01/1900" - that's not ok.
>>> Complete dates after 1900 appear in their correct date format as set by
>>> Excel.
>>>
>>> I know that Excel can't handle dates before 1900 or incomplete dates.
>>> Does anyone know a work-around so the pre-1900 and incomplete dates can
>>> appear as text strings in the format "yyyy-mm-dd".
>>>
>> because Excel wont recognize dates before 1900 you will have to send TEXTs
>> to Excel
>>
>> so, you are giving the 'workaround' yourself
>>
>> i think you can convert a date to a string in MySQL using the
>> DATE_FORMAT() function.
>
>
> Thanks, but date_format(thedate, '%Y-%m-%d') doesn't work. I tried it in a
> view of a table. The column where that should appear doesn't get read into
> Excel. It reformats the appearance of the date field but doesn't change it
> to string data. Try it with date_format(thedate, '%Y-%a-%d) which reformats
> 1937-10-23 to 1937-Oct-23. Dates in this format sort on the real date, not
> on the character string of the month.
>
> Also, if it did work, it would mean creating a set of views in parallel with
> the existent tables, views and queries in MySQL just for the Excel export.
>
> The function str_to_date() exists, but there doesn't seem to be anything
> like a date_to_str() function.
>
>
> Gary
>
>>
>>
>> --
>> Luuk
>
>
>

DATE_FORMAT will return the date as a string if it is used in a string
context. I haven't tried this particular thing, but maybe something
like CONCAT(DATE_FORMAT(...), '') will do it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Gary
Luke --------------------------------------------------------------------------------
Big City Production Services P/L Unit E, 13-21 Mandible St., Alexandria NSW
2015 Phone: (02) 9318-2354 Fax: (02) 9319-3929 Email: gary@bigcity.net.au
Mobile: 0439-451-571 www.bigcity.net.au
"Luuk" <Luuk@invalid.lan> wrote in message
news:a8qq95-7t6.ln1@leafnode.a62-251-88-195.adsl.xs4all.nl...
> Gary schreef:
>> I'm successfully importing tables and views from MySQL into Excel via
>> ODBC, except dates before 1900 and incomplete dates of any period. It's
>> historical data about the convicts with dates ranging from the 1700s to
>> the mid 1900s.
>>
>> Excel's query window shows the dates in the right format - eg.
>> 1832-11-19, or 1917-00-00, or blank for Null, but the cells in Excel
>> don't accept them. Nulls appear as empty cells - that's ok. Any date with
>> a "00" for month or day also appear as a blank cell - that's not ok.
>> Complete dates before 1900 appear as "0/01/1900" - that's not ok.
>> Complete dates after 1900 appear in their correct date format as set by
>> Excel.
>>
>> I know that Excel can't handle dates before 1900 or incomplete dates.
>> Does anyone know a work-around so the pre-1900 and incomplete dates can
>> appear as text strings in the format "yyyy-mm-dd".
>>
>
> because Excel wont recognize dates before 1900 you will have to send TEXTs
> to Excel
>
> so, you are giving the 'workaround' yourself
>
> i think you can convert a date to a string in MySQL using the
> DATE_FORMAT() function.


Thanks, but date_format(thedate, '%Y-%m-%d') doesn't work. I tried it in a
view of a table. The column where that should appear doesn't get read into
Excel. It reformats the appearance of the date field but doesn't change it
to string data. Try it with date_format(thedate, '%Y-%a-%d) which reformats
1937-10-23 to 1937-Oct-23. Dates in this format sort on the real date, not
on the character string of the month.

Also, if it did work, it would mean creating a set of views in parallel with
the existent tables, views and queries in MySQL just for the Excel export.

The function str_to_date() exists, but there doesn't seem to be anything
like a date_to_str() function.


Gary

>
>
>
> --
> Luuk

Gary
Luke --------------------------------------------------------------------------------
Big City Production Services P/L Unit E, 13-21 Mandible St., Alexandria NSW
2015 Phone: (02) 9318-2354 Fax: (02) 9319-3929 Email: gary@bigcity.net.au
Mobile: 0439-451-571 www.bigcity.net.au
"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:IbidnYTcw6FMVlfanZ2dnUVZ_h3inZ2d@comcast.com. ..
> Gary wrote:
>> Gary
>> Luke --------------------------------------------------------------------------------
>> Big City Production Services P/L Unit E, 13-21 Mandible St., Alexandria
>> NSW 2015 Phone: (02) 9318-2354 Fax: (02) 9319-3929 Email:
>> gary@bigcity.net.au Mobile: 0439-451-571 www.bigcity.net.au
>> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
>> news:M7OdnYRLreEULVfanZ2dnUVZ_qTinZ2d@comcast.com. ..
>>> Gary wrote:
>>>> I'm successfully importing tables and views from MySQL into Excel via
>>>> ODBC, except dates before 1900 and incomplete dates of any period. It's
>>>> historical data about the convicts with dates ranging from the 1700s to
>>>> the mid 1900s.
>>>>
>>>> Excel's query window shows the dates in the right format - eg.
>>>> 1832-11-19, or 1917-00-00, or blank for Null, but the cells in Excel
>>>> don't accept them. Nulls appear as empty cells - that's ok. Any date
>>>> with a "00" for month or day also appear as a blank cell - that's not
>>>> ok. Complete dates before 1900 appear as "0/01/1900" - that's not ok.
>>>> Complete dates after 1900 appear in their correct date format as set by
>>>> Excel.
>>>>
>>>> I know that Excel can't handle dates before 1900 or incomplete dates.
>>>> Does anyone know a work-around so the pre-1900 and incomplete dates can
>>>> appear as text strings in the format "yyyy-mm-dd".
>>>>
>>>> One workaround I found is to copy a table or results of a query or view
>>>> in Navicat and paste into Excel. That's not completely satisfactory. I
>>>> have the same problem with ODBC and Access where I want the latest
>>>> versions of the data without having to know what's been changed in
>>>> MySQL. Using Navicat's expert to Excel has the same problem.
>>>>
>>>> MySQL vs. 5.0.20-nt
>>>> ODBC vs. 3-51-14
>>>> Excel vs.2002
>>>>
>>>>
>>>> Gary Luke
>>>> Sydney, Australia
>>> And your MySQL question is?
>>
>>
>> This was in my message.
>>
>> "Does anyone know a work-around so the pre-1900 and incomplete dates can
>> appear as text strings in the format "yyyy-mm-dd"."
>>
>>
>>
>>
>>
>>
>
> OK, then you just need to use DATE_FORMAT() to convert to a string.
>
> Or use something other then Excel.

It doesn't work. It reformats how the date appears to the user, but doesn't
convert it to string data. See my other reply for details.

Thanks though for pitching in with ideas.


Gary


>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================
>

Gary schreef:
> Gary
> Luke --------------------------------------------------------------------------------
> Big City Production Services P/L Unit E, 13-21 Mandible St., Alexandria NSW
> 2015 Phone: (02) 9318-2354 Fax: (02) 9319-3929 Email: gary@bigcity.net.au
> Mobile: 0439-451-571 www.bigcity.net.au
> "Luuk" <Luuk@invalid.lan> wrote in message
> news:a8qq95-7t6.ln1@leafnode.a62-251-88-195.adsl.xs4all.nl...
>> Gary schreef:
>>> I'm successfully importing tables and views from MySQL into Excel via
>>> ODBC, except dates before 1900 and incomplete dates of any period. It's
>>> historical data about the convicts with dates ranging from the 1700s to
>>> the mid 1900s.
>>>
>>> Excel's query window shows the dates in the right format - eg.
>>> 1832-11-19, or 1917-00-00, or blank for Null, but the cells in Excel
>>> don't accept them. Nulls appear as empty cells - that's ok. Any date with
>>> a "00" for month or day also appear as a blank cell - that's not ok.
>>> Complete dates before 1900 appear as "0/01/1900" - that's not ok.
>>> Complete dates after 1900 appear in their correct date format as set by
>>> Excel.
>>>
>>> I know that Excel can't handle dates before 1900 or incomplete dates.
>>> Does anyone know a work-around so the pre-1900 and incomplete dates can
>>> appear as text strings in the format "yyyy-mm-dd".
>>>
>> because Excel wont recognize dates before 1900 you will have to send TEXTs
>> to Excel
>>
>> so, you are giving the 'workaround' yourself
>>
>> i think you can convert a date to a string in MySQL using the
>> DATE_FORMAT() function.
>
>
> Thanks, but date_format(thedate, '%Y-%m-%d') doesn't work. I tried it in a
> view of a table. The column where that should appear doesn't get read into
> Excel. It reformats the appearance of the date field but doesn't change it
> to string data. Try it with date_format(thedate, '%Y-%a-%d) which reformats
> 1937-10-23 to 1937-Oct-23. Dates in this format sort on the real date, not
> on the character string of the month.
>
> Also, if it did work, it would mean creating a set of views in parallel with
> the existent tables, views and queries in MySQL just for the Excel export.
>
> The function str_to_date() exists, but there doesn't seem to be anything
> like a date_to_str() function.
>
>
> Gary
>
>>
>>
>> --
>> Luuk
>
>

what you need is the correct value coming out of MySQL, and i think you
already have done that.

but the real 'problem' you have it that excel does something when importing

i.e when importing these 6 lines:
1850-00-00
1850-01-01
1900-00-00
1900-01-01
1950-00-00
1950-01-01

then excel will only recognize the 4th en 6th line as a DATE

This is because Excel 'thinks' that lines 1,2,3 and 5 contain no DATE-value!

If you want to have these 6 values in excel you have to read them as TEXT

The copying/pasting stuff you did from Navicat, probably just takes the
TEXT-values to Excel, and does not 'import' them

hm, in fact Jerry is right, this does not seem a MySQL question... ;-)

--
Luuk

>Thanks, but date_format(thedate, '%Y-%m-%d') doesn't work. I tried it in a

Have you considered:

date_format(thedate, '"%Y-%m-%d"')