I have well formatted CSV files with quite a lot of rows.

The CSV includes DATE fields, which are well formatted DD/MM/YYYY.

I use EDB's IMPORT TABLE command all the time and it works flawlessly.

In this case the destination table contains TIMESTAMP fields (not DATEs).

The IMPORT routine falls over trying to push the CSV DATE field into the EDB Table TIMESTAMP.

--

I can:

* Try to rewrite the CSV adding " 00:00" to the end of every date.
* Import into a Temporary Table first (with DATE columns) then
  pass the data across CASTing to TIMESTAMP.

But is there a way of getting EDB to accept the DATE and pass it into a TIMESTAMP?

Adam,

<< But is there a way of getting EDB to accept the DATE and pass it into a TIMESTAMP? >>

Not at this time, no.  I can certainly see about adding support for this, but I do have some concerns about doing so, namely that improperly-formatted data will pass through without exceptions, which may or may not be the desired result for someone that really needs both the date and time portions of the timestamp to be specified correctly in the incoming data.

Tim Young
Elevate Software
www.elevatesoft.com

>>I do have some concerns about doing so, namely that improperly-formatted data will pass through without >>exceptions, which may or may not be the desired result for someone that really needs both the date and time >>portions of the timestamp to be specified correctly in the incoming data.

I guess there could be possible options for formatting TIME FORMAT part of the IMPORT TABLE command, to set whether the user wanted to allow data without a time portion to be accepted or rejected?

Adam,

<< I guess there could be possible options for formatting TIME FORMAT part of the IMPORT TABLE command, to set whether the user wanted to allow data without a time portion to be accepted or rejected? >>

The problem with that approach is that you've got a date format and a time format, and if you simply ignore any time portions in incoming timestamps when the time format is blank, then you've completely eliminated the ability to import data that has both items present in the data - time fields and timestamp fields without a time specified.

I think the only decent way forward is a separate TIMESTAMP FORMAT clause, but even that has the same issues of incoming data where one timestamp field might have the time specified and another not.

Tim Young
Elevate Software
www.elevatesoft.com

One issue of note.

The main route for me for creating CSV files to import to EDB is Excel, basically to manually clean up files, reformat rows and columns on their way "through" from other export-processes (often EDB!).

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.

I have just been through this. A few hundred rows of data where a fairly small number saved to CSV as DATE while the rest where true TIMESTAMP, although the original export was all TIMESTAMP. Obviously EDB (rightly) refused to import the whole table.

This is particularly tedious, as it means opening the CSV searching for the incorrectly formatted timestamps which are now dates and manually adding in a " 00:00".

EDBs import process is great, super efficient and effective. I have used in reliably for years, however new applications I am writing now are making greater use of TIMESTAMP and this is less reliable.

If the destination field is a TIMESTAMP and the incoming date lacks a TIME portion, I do feel it might be sensible to allow the IMPORT routine to disregard the fact that it is missing, and import it with a value of " 00:00". I am happy to defer on this if other people disagree.

Adam


Have you thought of just applying a custom format to the whole column in Excel?

Not an ideal solution maybe but a lot easier than manually editing a csv file.

Roy Lambert

Roy

This is really helpful. I am such a know-nothing with Excel I try to touch it as little as possible. If I enforce formatting on a column then this issue might disappear.

It still means some work in Excel ... and I still maintain that EDB ought to default to 00:00 on timestamps if no time-part is present, but it definitely solves the problem, thanks.

Adam

>This is really helpful. I am such a know-nothing with Excel I try to touch it as little as possible. If I enforce formatting on a column then this issue might disappear.

Thank god(s) for that. I thought you might have tried and I'd just be rubbing salt into the wounds

>It still means some work in Excel .

I did try it out before suggesting it - first hunt the ribbon for cell formatting

The way I'd do it is whatever you're doing now then just select the column, not individual cells and just ALT-H-O-e (or Home tab, Cells category, format, format cells) Then Number, Custom and you'll find the timestamp one about 3/4 of the way down.

>.. and I still maintain that EDB ought to default to 00:00 on timestamps if no time-part is present, but it definitely solves the problem, thanks.

I sort of agree with you but have mixed views - I often think its better to generate an error rather than silently do something that may not have been wanted.

Roy

Adam,

<< The main route for me for creating CSV files to import to EDB is Excel, basically to manually clean up files, reformat rows and columns on their way "through" from other export-processes (often EDB!).

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. >>

I haven't touched the export code in EDB for quite some time, and I just tried an export of a table with rows containing timestamps with a time of 12:00:00 AM (00:00, or no time specified).  The export of the timestamp values work properly every time, and do not leave out the time portion.

Are you sure that you aren't seeing the effects of Excel here ?  Excel will interpret/reformat the data as it sees fit, so you should always use a text editor to ensure that you're seeing the raw CSV contents.

<< I have just been through this. A few hundred rows of data where a fairly small number saved to CSV as DATE while the rest where true TIMESTAMP, although the original export was all TIMESTAMP. Obviously EDB (rightly) refused to import the whole table. >>

Yes, but this is the same issue as already discussed.  It *doesn't* indicate that there's something wrong with EDB's export.

If you can show me an example of an EDB export that leaves out the time portion of a timestamp column, I'll be happy to look into it.  But, I certainly don't see anything like that happening here with EDB.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

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.

I definitely experienced this, with an export from EDB which I opened in Excel (for editing) saved, and then Imported into a different EDB DB, but the exact order of events is a bit hazy, as I was doing several batches of records.

Roy's advice to force column-formats in the Excel file is a good one. I will probably try to do this not just for Timestamp but also for Date and other data-types for extra safety.  

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.