How NOT to get the date
 

Hello,

We need to import a large number of spreadsheets with suspect data. Some
very involved database structures have been developed to translate the
received data. Dates represent a large part of this questionable data. The
process assumes dates are poorly formed and the poorly formed string is
ultimately used as a lookup value.

The problem is, when these spreadsheets are read in via OleDB, the
String "Sep-05" returns as 9/1/2005. Now I understand that for many (if not
most) applications, this translation is desired, but in this case the desired
value to be retrieved from the cell is, exactly, "Sep-05". Nothing we have
tried so far suppresses this 'help' from Excel.

Is there a way to get the actual value and not the one Excel thinks we
want?

How NOT to get the date
 

Would going into the PreImport data and putting an apostrophe before the
values help?


"mklapp" wrote:

Hello,

We need to import a large number of spreadsheets with suspect data. Some
very involved database structures have been developed to translate the
received data. Dates represent a large part of this questionable data. The
process assumes dates are poorly formed and the poorly formed string is
ultimately used as a lookup value.

The problem is, when these spreadsheets are read in via OleDB, the
String "Sep-05" returns as 9/1/2005. Now I understand that for many (if not
most) applications, this translation is desired, but in this case the desired
value to be retrieved from the cell is, exactly, "Sep-05". Nothing we have
tried so far suppresses this 'help' from Excel.

Is there a way to get the actual value and not the one Excel thinks we
want?

How NOT to get the date
 

I should have mentioned it in the original post. No that is not a viable
option. These sheets hold over 1000 rows, they come from independent outside
sources and users have about a heartbeat to process them.

The case has been made to forego the whole spreadsheet step but that is a
complex solution with several components (technical and human) whose time has
not yet come.

We have come up with an approach that works but is flawed.

If the retrieved field evaluates to a date, we are replacing it with a
String matching the format of the ones on the sheet where the problem was
discovered. The flaw is that there is more than one string format that can
be evaluated as a date. This means we could be replacing source data with
new data which is semantically equivalent but syntactically distinct. While
this is not a serious technical shortcoming, we have had to prepare an
explanation for the users when they discover their data has changed from the
source format.

"Access101" wrote:


Would going into the PreImport data and putting an apostrophe before the
values help?


"mklapp" wrote:

Hello,

We need to import a large number of spreadsheets with suspect data. Some
very involved database structures have been developed to translate the
received data. Dates represent a large part of this questionable data. The
process assumes dates are poorly formed and the poorly formed string is
ultimately used as a lookup value.

The problem is, when these spreadsheets are read in via OleDB, the
String "Sep-05" returns as 9/1/2005. Now I understand that for many (if not
most) applications, this translation is desired, but in this case the desired
value to be retrieved from the cell is, exactly, "Sep-05". Nothing we have
tried so far suppresses this 'help' from Excel.

Is there a way to get the actual value and not the one Excel thinks we
want?

How NOT to get the date
 

Hello again. Any luck?

What about this idea:

Before the import, you search and replace the months:

Sep-05 with xSep-05
Dec-04 with xDec-04

Then after it's imported into Excel, you replace

xDec-04 with Dec-04

or

xDec-04 with ' Dec-04

Would this help?

Thanks,
Michael


"mklapp" wrote:

I should have mentioned it in the original post. No that is not a viable
option. These sheets hold over 1000 rows, they come from independent outside
sources and users have about a heartbeat to process them.

The case has been made to forego the whole spreadsheet step but that is a
complex solution with several components (technical and human) whose time has
not yet come.

We have come up with an approach that works but is flawed.

If the retrieved field evaluates to a date, we are replacing it with a
String matching the format of the ones on the sheet where the problem was
discovered. The flaw is that there is more than one string format that can
be evaluated as a date. This means we could be replacing source data with
new data which is semantically equivalent but syntactically distinct. While
this is not a serious technical shortcoming, we have had to prepare an
explanation for the users when they discover their data has changed from the
source format.

"Access101" wrote:


Would going into the PreImport data and putting an apostrophe before the
values help?


"mklapp" wrote:

Hello,

We need to import a large number of spreadsheets with suspect data. Some
very involved database structures have been developed to translate the
received data. Dates represent a large part of this questionable data. The
process assumes dates are poorly formed and the poorly formed string is
ultimately used as a lookup value.

The problem is, when these spreadsheets are read in via OleDB, the
String "Sep-05" returns as 9/1/2005. Now I understand that for many (if not
most) applications, this translation is desired, but in this case the desired
value to be retrieved from the cell is, exactly, "Sep-05". Nothing we have
tried so far suppresses this 'help' from Excel.

Is there a way to get the actual value and not the one Excel thinks we
want?