Toad World® Forums

Importing dates with no delimiters


#1

I am trying to import from an Excel spreadsheet with dates in numeric format (eg 20180328 for March 28, 2018). I can’t figure out how to get the Import Table Data wizard to convert these to dates so that they can be imported into a date column.

Things I have tried: selecting “YMD” as the date order (this gives conversion errors in the preview); selecting “YMD” as the date order and nothing as the date delimiter (this gives the immediate error “date and time delimiters must be specified”); and selecting custom plus “yyyymmdd” as the date order (this gives nulls in the date columns).


#2

I was able to make it work in the current beta with the Custom date order and YYYYMMDD.

If you have blanks in the date/time delim fields, put something in there to avoid the error about them having to be specified.

Another option is to use the expression builder. I used an expression like this and it worked for me. [ DATETIMEFROMSTRING([FIELD1], YYYYMMDD, ‘’, ‘’) ]

if you are still having trouble, post your table ddl along with your spreadsheet (use fake date if the real data is sensitive).


#3

My first try was with a text file. I tried it again with XLSX. Saw the nulls as you mentioned with in the preview pane (using custom and yyyymmdd), but I continued anyway and ran it. It created the correct date values in my table when it ran.


#4

Ah-hah, it did import them! Slooooowly, though (30 rows/second).

Thank you!


#5

Next time you do an import, check the “Array DML” box on the last page and it will go much faster.

I’ve fixed the blank preview problem for the next version.