Nope, haven't tried an ODBC connection. I was allowed a total of a half-hour billable time to get an answer..... For now they've decided they can live without sorting, the fact that Excel puts all the old dates at the bottom together is fine. But by exporting it as a text column with a 4-digit year displayed at least gives them accurate data.
Karen -----Original Message----- From: Javier Valencia <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, Nov 18, 2014 3:07 pm Subject: [RBASE-L] - Re: Problem with Excel recognizing dates Karen, Instead of “exporting” data to Excel, have you tried to “importing” the data directly from Excel through ODC? You can create a macro in Excel that connects to the database, selects the desired data and imports it into pre-set Excel columns. I used this approach to extract data from the database and import it into Excel for graphing before we had graphing capabilities in R:Base. I seem to recall the process being pretty straight forward and you could “record” the steps and then create the macro with Excel. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: Tuesday, November 18, 2014 2:35 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Problem with Excel recognizing dates Very interesting! Those posts could have been written by me because they did hit every barrier that I was coming up against! The last thing I'll try is renaming the input file from .csv to .txt which forces them to go through the import wizard, and see what happens if they specify "text" as the datatype. If I import the data as a text column in my RBase table, and then later manually change the column's datatype to Text in the spreadsheet, it still "knows" it's dates and sorts the pre-1900 as unknown dates at the bottom. Karen -----Original Message----- From: Paul Buckley <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, Nov 18, 2014 2:06 pm Subject: [RBASE-L] - Re: Problem with Excel recognizing dates Karen, Look at this link, it may help you. http://superuser.com/questions/568429/excel-csv-import-treating-quoted-strings-of-numbers-as-numeric-values-not-strin Paul Buckley From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: Tuesday, November 18, 2014 2:55 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Problem with Excel recognizing dates Got excited about the idea for a second, Paul. No matter whether I put a ' or a " preceding the text, the literal character showed up in the spreadsheet. So technically yes then I could sort the dates if I formatted them YYYY-MM-DD. But they didn't like that quote showing... They might not have a choice, though. Karen -----Original Message----- From: Paul Buckley <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, Nov 18, 2014 1:24 pm Subject: [RBASE-L] - Re: Problem with Excel recognizing dates Karen, I don’t know if this will work but it came to me as I was reading your posts. If you create a computed column in R:BASE that is the date converted to text with a preceding single apostrophe Excel may import it as text. I know when you precede anything when typing in Excel with and ‘ it makes it text. Just an idea. Paul Buckley From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: Tuesday, November 18, 2014 2:16 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Problem with Excel recognizing dates Just drives me crazy that I cannot export as a TEXT and have Excel accept it as a text... Sometimes, Excel, I actually do know what I want to do, and you don't.... Yes Jason I suppose you're right, I could download 3 separate columns and have them sort by those columns. "Wonky" to say the least. Karen -----Original Message----- From: Kramer, Jason J <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, Nov 18, 2014 1:09 pm Subject: [RBASE-L] - RE: Problem with Excel recognizing dates Dear Karen, This is a little wonky, but will allow you to sort all dates in Excel. Export your dates in four columns, one with the full date, and then one of the year, month, and day. Import all into Excel. Excel will still give you trouble with the date column, but you can just tell Excel that it is text. Sort by the year, month, and day columns, which Excel will treat as integers. I may be able to shoot you an example tomorrow if you would like. Thanks, Jason Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) From: [email protected] [mailto:[email protected]] On Behalf Of Tony IJntema Sent: Tuesday, November 18, 2014 1:56 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Problem with Excel recognizing dates Karen, It is a pity, but according to Excel the world began on January 1, 1900. Excel is not capable of working with dates earlier than that. Tony From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: dinsdag 18 november 2014 18:58 To: RBASE-L Mailing List Subject: [RBASE-L] - Problem with Excel recognizing dates The database holds patents, so it has a wide range of years, from the early 1800s to future expiration dates many years into the future. Excel 2010 apparently still does not recognize pre-1900 dates. I need to import pre-1900 dates, and they have to sort-able with other dates. If I do: SET DATE FORMAT MM/DD/YYYY I can clearly see correct years, going back to the 1800s. When I export, whether as an xls or a csv file, the raw output file looks okay. But Excel does not recognize these pre-1900 items as dates. If you sort, these rows are put at the end all by themselves out of the sort order. I tried creating a TEXT column, changing the date format to YYYY-MM-DD so the text column can be sorted. Then I did a CTXT(datecol) to the text column and exported that instead. Raw data looks good, and of course it sorts okay in RBase as text column. But again Excel tried to outsmart me. It took the YYYY-MM-DD format for the pre-1900 dates, but it "knew" the other rows were dates so it converted them to a date format and therefore cannot sort them together. Please someone tell me how the heck I can tell Excel that these are text fields, just accept them as text fields YYYY-MM-DD so we can sort them, or some other way of getting all these dates in together... This is really critical to the client. Karen
