Import .txt object file error not a valid date

bhalpin

Hi.

I have a very large object file in .txt format in which all object dates are in the form dd-mm-yy. When I import it I get the error '14-02-14;' is not a valid date.

I can manually edit the file to change the dates to dd/mm/yy format, but that will take a while. I'm no VB whiz so that would take even longer (though a reg exp would make short work of this.)

Checking regional settings in Win 8.1 I don't see a format dd-mm-yy that I've been told might help

Is there any NAV setting that can resolve this?

Thanks!

vremeni4

Hi,

The best way to resolve this issue is to find out the regional settings (US,United Kingdom, Germany) that was set on the machine where the .txt file was exported.
IF that is not possible you can take a look at this link
http://en.wikipedia.org/wiki/Date_format_by_country
It seems that Ireland uses the date format dd-mm-yyyy.

Now got to regional settings and on the tab Formats in the field Format change the language to English (Ireland). In the short date change the settings to dd-mm-yy. click on apply button to save changes.

The import of the file should work now.
Do not forget to change the language back.

I hope this helps.
Thanks.

bhalpin

Hi. Thanks!

Well, I got the file in, but lost track of the steps I took.

I did change the country (to Mexico - Ireland didn't seem to work), and I also manually edited all the dates to switch - to /.

Next time (and there probably will be one) I'll put the time into a VB Script to un-mangle the file.

Thanks again!

bhalpin

Follow-up ...

Does anyone remember the text editor 'ED' that came with (DOS) Word Perfect? If I still had a machine that ran DOS I could have knocked off a macro in about 1 minute that would have fixed/re-arranged all the dates.

Oh well.

Savatage

you could use excel's replace function to change - to /

bhalpin

But I need to swap the order of the day and month as well. Could do with an Excel formula, but a bit of a PITA.

Savatage

How are you importing it dataport, report?

You could always use COPYSTR to extract the day. month & year then put it back together in the order you want.

bhalpin

Object Designer, File Import.