Tips for making Excel recognize exported CSV dates as dates? (We're hosted)

Keri Barnhart

Hi all,

I'm about to pull my hair out because among other things that have the same annoyance, I run a weekly report on open Opportunities for our management team that details such exciting things as when the Opportunity was created, the deadline, when it was changed to Asked, when it is Expected, and if/when it was Funded.


I export into CSV from a query, but when I open the file in Excel to run analysis and format it as needed, NONE of those dates are read as dates. If I use the Data Import feature, I have to manually select each date column as MDY, or if I just open the CSV, I have to use Text-to-Columns to get the same thing. On each column individually. Every time I need to use dates. The report I mentioned above is the worst offender, but far from the only one.


Does anyone have any ideas for how to fix this? I never had an issue with dates until we moved from on-prem to hosted, and as far as I'm aware, my coworkers only have the problem sporadically (I think it's when they export to CSV vs Excel, only using CSV for a few especially large exports). It's not the end of the world, but it is really annoying to have to stop what I'm doing and text-to-columns five or six dates every single time I open a file.

Aaron Rothberg

Hi Kerri-


It makes sense that exporting to CSV would strip your cells of any formatting so that's why the dates are not showing up as dates. Your suggestion to import the data into Excel was going to be my first suggestion. Seeing as you mention specifically you want to know how to solve this exporting from a Query, I find that when I click the export button in Query, I can change my Export file type to Excel 97-2000 (XLS) which does preserve the date formatting when you open the XLS. If you have a reason for exporting specifically to CSV this solution of course won't work.

 

I suspect, but could be wrong, that when you hosted your own server, you would have had the same experience exporting to CSV. That is, dates getting their formatting stripped. Is it possible you were exporting to some format other than CSV, but that format is not available in the hosted environment which prompted you to switch to CSV and as such believe these symptoms are new?


 

Keri Barnhart

I find that when I click the export button in Query, I can change my Export file type to Excel 97-2000 (XLS) which does preserve the date formatting when you open the XLS. If you have a reason for exporting specifically to CSV this solution of course won't work.

I was unable to export to Excel formats beginning in the summer of 2017 with a Windows update, and had not attempted it since we moved to Blackbaud Hosting in May 2018, because most of the external reports I built use data import or are in Access - requiring a text file (CSV) to work. I just tried it and the dates came out properly formatted (YYYY-MM-DD), which surprised me - I would have thought they'd be in MM/DD/YYYY like everywhere else in RE. Unfortunately, now all of the ID columns (which are integers for us) are coded as text, and I can't simply update the data source in my reports. Win some and lose some, I suppose!

I suspect, but could be wrong, that when you hosted your own server, you would have had the same experience exporting to CSV. That is, dates getting their formatting stripped. Is it possible you were exporting to some format other than CSV, but that format is not available in the hosted environment which prompted you to switch to CSV and as such believe these symptoms are new?

Actually, I did not have any problems with my dates! When I opened the CSV in Excel, it automatically parsed the cells as dates without me having to do anything. When we switched to Blackbaud Hosting, I suddenly had tons of problems - RE keeps rejecting my date input as "invalid fuzzy date" if I don't type it in exactly MM/DD/YYYY format (using the - instead of / only works half the time, and typing in the ISO 8601 standard format works at best 10% of the time), but I had been typing YYYY-MM-DD (or YYYY-MM for fuzzy) into RE for years without problems when we were on-prem.


At least now I know the XLS export isn't broken anymore, and for my date-heavy reporting, it might be less annoying to copy-paste the Excel data than try to reconfigure the data import all the time. Of course, the simplest solution would be to set up a data import that remembers the file name/location and doesn't need to be reconfigured but we can't make life that easy for ourselves, can we?  (This is partly my fault for using a wireless device that randomly loses access to our shared file storage so that I have to save everything locally as well in the shared department space.)

Aaron Rothberg

I just ran the same test and you are right. I exported a CSV with gift dates from Query and the dates display fine when I open the document in Excel. The fact that you see the dates as you expect in query, but not as you expect when you open the results in Excel makes me wonder if the date is being interpreted by Excel for display purposes and it is Excel affecting that change? I didn't see anywhere in Raiser's Edge to configure the date format when exporting from Query. ¯\\_(ツ)_/¯

Dariel Dixon 2

Excel does strange things in regards to dates in CSVs.  There is a knowledgebase article in regards to it.  https://kb.blackbaud.com/articles/Article/42507  It refers to DeceasedRecordFinder, but the information about Excel and CSVs are listed at the bottom.  I don't know if this can help at all, but I did want to make you know you are not the only one having issues like this.

Gracie Schild

Hi - I might be way off base, but can't you export the thing as a regular Excel file and then just save it as a CSV file outside of RE? Would that fix the problem?

 

James Andrews

Have you ever tried exporting to character separated text instead of CSV?  That adds in text qualifiers and you can change your delimiter. I find Excel to be very good at recognizing stuff in those files and besides you can define your column types upon opening.  The only thing is for some reason RE defaults to a .CHR extension on there instead of .txt, which sometimes confuses older versions of excel.