Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate including a date so that the date appears as a date | Excel Worksheet Functions | |||
date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
Making a date go red, if date passes todays date. | Excel Worksheet Functions | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel |