dttm_cvt(x) and dt_cvt(x) doesn't work correctly if year is represented with two numbers

[Meliowant]

I'm now trying to retrieve data from MS Access 2000 DB, and original code for dttm_cvt(x) as well as dt_cvt(x) fails, because year is represented as 2-digit value i.e. "11/19/96 00:00:00". Thus it reports:

ValueError: invalid literal for int() with base 10: '01/0'  # while parsing date
ValueError: invalid literal for int() with base 10: '00:00:0'  # while parsing time

So I updated a original source, as follows:

def dttm_cvt(x):
    if py_v3:
        x = x.decode('ascii')
    if x == '': return None
    x = x.ljust(26,'0')
    db_date = x[0:x.find(' ')]
    db_time = x[x.find(' ')+1:]
    if len(db_date) < 10:
        # Datetime format looks like: 01/01/76 (month/day/year)
        first_sep = db_date.find('/')
        last_sep = db_date.find('/', first_sep+1)
        db_month = int(db_date[0:first_sep])
        db_day = int(db_date[first_sep+1:last_sep])
        db_year = int(db_date[last_sep+1:])
        if len(str(db_year)) < 4:
            if db_year < 20:
                db_year += 2000
            else:
                db_year += 1900

        first_sep = db_time.find(':')
        last_sep = db_time.find(':', first_sep+1)
        db_hour = int(db_time[:first_sep])
        db_minute = int(db_time[first_sep+1:last_sep])
        db_second = int(db_time[last_sep+1:])

        return datetime.datetime(db_year, db_month, db_day, db_hour, db_minute, db_second)
    else:
        return datetime.datetime(int(x[0:4]),int(x[5:7]),int(x[8:10]),int(x[10:13]),int(x[14:16]),int(x[17:19]),int(x[20:26]))

def dt_cvt(x):
    if py_v3:
        x = x.decode('ascii')
    if x == '': return None
    else:
        db_date = x[0:x.find(' ')]
        if len(db_date) < 10:
            first_sep = db_date.find('/')
            last_sep = db_date.find('/', first_sep+1)
            db_month = int(db_date[0:first_sep])
            db_day = int(db_date[first_sep+1:last_sep])
            db_year = int(db_date[last_sep+1:])
            return datetime.datetime(db_year, db_month, db_day)
        else:
            return datetime.date(int(x[0:4]),int(x[5:7]),int(x[8:10]))

It works OK with datetime and date, but I'm not sure if this is only MS Access issue (or maybe even just only my own issue), and if there is a better way to implement this code.

Regards, Meliowant

P.S. This is my first post here, so sorry, if I didn't make everything as expected

[braian87b]

Thanks for the contribution, we will leave this here for future reference... Since you are using python, have you think about migrating to SQLite? or the Access DB is shared with another app?

[Meliowant]

That's exactly, what I'm trying to solve now - migrate from MS Access to MySQL (or any other SQLAlchemy-supported DB)

[braian87b]

Mh, if you have a spare SQLServer probably would be easier to migrate to SQLServer first and from there to SQLite... (in SQL Server you even could export to text-plain .sql files with create-tables and inserts and edit them there as necessary) try this: http://sqlite2009pro.azurewebsites.net/ or this: http://mdb2sq3.codeplex.com/ (feature says "Import data from Ms Access") if you had lots of money you could try this: https://convertdb.com/access/sqlite this could help you too with the project (I recommend to have an app like this one around for digging the db): http://sqlitebrowser.org/

I actually do not recommend you to write your own AccessDB-to-SQLite migrator from scratch, unless you actually want to, because even if you share it on internet, these days, will not has a lot of people using it. You should try to migrate a current working copy to allow you finish your SQLite based app, and when its ready do a final migration with updated data... (that is what I will do if I had to)

Good luck!

[Meliowant]

Thank you. There are two issues, I'm running in: a) I'm using Linux and only Linux; b) I will need it (I believe) just only few times to import data from the existing MS Access DB to my SQL schema. It won't be shared, because it is rather a simple and hard-coded converter from one schema to other, that will extract only useful data, and not everything.

[braian87b]

Great!