Adam,

<< Sorry my miscommunication, I don't think there is anything wrong with EDB's export routines, it definitely exports "Midnight" timestamps as "Midnight".

However, I think in certain situations EXCEL may "clean off" 00:00 from values in columns containing Timestamps when you save, and just save the date-portion. >>

Okay, because your original statement was:

"I am pretty sure that at least some versions of EDB remove the trailing "00:00" from a time-stamp if the time-stamp has it."

And I can't find any evidence that this has *ever* been an issue.  There's been plenty of *other* issues with import/export, of course, but those have all been resolved for some time now.

<< However, as soon as you save the Excel file as CSV all the saved column formats are lost, since the CSV is intentionally plain text ... so you do have to save as XLS or XLSX while you are working on the file and want to enforce the column formats. The CSV File contains the data in the correct formats ... but cannot include the complex Excel data relating to column-formatting. >>

Okay, is there any way that you can eliminate the Excel step from the equation ?  IOW, what would be required of EDB's export that would allow you to skip this step ?  The reason that I say this is that EDB has no problem importing CSV files that it has exported and haven't been changed, so if the "massage" step can be eliminated, then that makes things much simpler.

Tim Young
Elevate Software
www.elevatesoft.com

Excel is needed when there is a need to clean up data, delete rows, do some search and replace on column values etc. I use it as a reflex, because people all own it and know how to use it.

Roy's earlier suggestions on  formatting the columns to enforce data-types would help to reduce the problem.

I think I may write a basic CSV editor, specifically to open EDB exports and allow their editing. I could ensure that the necessary formats are preserved also it would remove dependencies on outside programs, and build in the specific features I need.

Adam

I don't know if xml would be any better for you. On the upside, I switched from csv to xml because of a problem with CLOBs or BLOBS (don't remember exactly) and found that xml was actually faster.


Roy Lambert

Roy

Thanks for this.

I am quite often importing data a customer may have had in their own systems (i.e. staff lists stored in Excel), and I in these cases I think CSV is the easiest format, so I might stick with this to keep my life easy.

I very rarely use BLOBs, I can imagine XML would do a better job with them.

I will bear this in mind

Adam


>I am quite often importing data a customer may have had in their own systems (i.e. staff lists stored in Excel), and I in these cases I think CSV is the easiest format, so I might stick with this to keep my life easy.

This has me interested so I'll have a look and see how Excel treats an XML export from ElevateDB

Roy

>>This has me interested so I'll have a look and see how Excel treats an XML export from ElevateDB

I had assumed it wouldn't be happy with it, but very happy to hear otherwise.

Other XML readers might also be worth checking out ... not an area I know a lot about!

Adam


Playing with it I've decided its not really a suitable approach. Tthe raw file from ElevateDB would need a small amount of modification. You need the xml header and you need to wrap the whole thing with a top level element. When saving its easy to pick the wrong format. Believe it or not my version of Excel (2007) offers two formats for save as xml - straight data and spreadsheet. The spreadsheet version has masses of crud built in.

The top level element would then need stripping out for ElevateDB to import it. Its doable, not difficult but messy. A FULLXML option would be nice (Tim are you listening)

Roy Lambert

Roy,

<< Playing with it I've decided its not really a suitable approach. Tthe raw file from ElevateDB would need a small amount of modification. You need the xml header and you need to wrap the whole thing with a top level element. When saving its easy to pick the wrong format. Believe it or not my version of Excel (2007) offers two formats for save as xml - straight data and spreadsheet. The spreadsheet version has masses of crud built in. >>

The top-level XML element issue will be fixed soon.  Jeff Cook reported it a while ago, and it needs to get resolved.  AFAIK, that's the only outstanding issue with the XML import/export in terms of interoperability.

Tim Young
Elevate Software
www.elevatesoft.com