Upload any DateTime from CSV in Django

Question

I am uploading CSVs in a django project but it shows error from laptop to laptop.

Models.py

    date = models.DateTimeField(blank=True, null=True, default=datetime.date.today)

views.py

csv_file = request.FILES['file']
data_set = csv_file.read().decode('UTF-8')
io_string = io.StringIO(data_set)
next(io_string)

uploaded_by = request.user

for column in csv.reader(io_string, delimiter=',', quotechar='|'):
    _, created = ItemBatch.objects.update_or_create(name=column[0], pid=column[1], quantity=column[2],date=column[8])

The problem is that it takes only this format :

YYYY-MM-DD HH:MM

I updated settings.py with this:

DATETIME_INPUT_FORMATS = [
'%Y-%m-%d %H:%M:%S',     
'%d-%m-%Y %H:%M:%S',     
'%Y-%m-%d %H:%M:%S.%f',  
'%Y-%m-%d %H:%M',        
'%Y-%m-%d',              
'%m/%d/%Y %H:%M:%S',     
'%m/%d/%Y %H:%M:%S.%f',  
'%m/%d/%Y %H:%M',        
'%m/%d/%Y',              
'%m/%d/%y %H:%M:%S',     
'%m/%d/%y %H:%M:%S.%f',  
'%m/%d/%y %H:%M',        
'%m/%d/%y',
'%d-%m-%Y %H:%M' 
]




LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'Asia/Kolkata'

USE_I18N = True

USE_L10N = False

USE_TZ = False

error:

["'10-7-2019 12:00' value has an invalid format. It must be in YYYY-MM-DD HH:MM[:ss[.uuuuuu]][TZ] format."]

What changes do I need to make such that it accepts every datetime format?

EDIT as per rudraa's suggestion I defined my formats in the CustomManager function:

def get_date_value(self, value):
    FORMATS = [
        '%d-%m-%y %H:%M',
        '%d-%y-%m %H:%M',
        '%m-%d-%y %H:%M',
        '%m-%y-%d %H:%M',
        '%y-%m-%d %H:%M',
        '%y-%d-%m %H:%M',
        '%d/%m/%y %H:%M',
        '%d/%y/%m %H:%M',
        '%m/%d/%y %H:%M',
        '%m/%y/%d %H:%M',
        '%y/%m/%d %H:%M',
        '%y/%d/%m %H:%M'
    ]
    input_formats = FORMATS

But while uploading the file with 15/7/2019 18:30 format i.e '%d/%m/%Y %H:%M' it says:

redefinition of group name 'd' as group 6; was group 1 at position 133

ruddra · Accepted Answer · 2019-07-15 07:47:38Z

2

+50

From what I can see, DATETIME_INPUT_FORMATS is being used in django forms only. It will validate any date given in formats with DATETIME_INPUT_FORMATS. But when you are using the model directly to store the data, it will only accept data if its given in YYYY-MM-DD HH:MM. It evaluates the datetime string against this regex. So I would recommend to write a custom manager to convert the values to datetime format, and use it to create/update the value. For example:

from django.utils import formats


class CustomManager(models.Manager):
    def custom_update_or_create(self,*args, **kwargs):
        date_time_value = kwargs.pop('date', None)
        if date_time_value:
            kwargs['date'] = self.get_date_value(date_time_value)
        return super(CustomManager, self).update_or_create(*args, **kwargs)

    def get_date_value(self, value):
         input_formats = [
            '%d-%m-%y %H:%M',
            '%d-%y-%m %H:%M',
            '%m-%d-%y %H:%M',
            '%m-%y-%d %H:%M',
            '%y-%m-%d %H:%M',
            '%y-%d-%m %H:%M',
            '%d/%m/%y %H:%M',
            '%d/%y/%m %H:%M',
            '%m/%d/%y %H:%M',
            '%m/%y/%d %H:%M',
            '%y/%m/%d %H:%M',
            '%y/%d/%m %H:%M'
         ]

         for format in input_formats:
            try:
                return self.strptime(value, format)
            except (ValueError, TypeError):
                continue

    def strptime(self, value, format):
        return datetime.datetime.strptime(value, format)

And Use it in the model:

class ItemBatch(models.Model):
    date = models.DateTimeField(blank=True, null=True, default=datetime.date.today)
    # rest of the fields
    objects = CustomManager()

Finally use that method to create/update new instances:

ItemBatch.objects.custom_update_or_create(name=column[0], pid=column[1], quantity=column[2],date=column[8])

edited Jul 15, 2019 at 7:47

answered Jul 15, 2019 at 6:26

ruddra

52.1k7 gold badges82 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rahul Sharma Over a year ago

I have to put this CustomManager in models.py ? and also in my model ItemBatch instead of date = models.DateTimeField(blank=True, null=True, default=datetime.date.today) I have to writedate = CustomManager() ?

ruddra Over a year ago

not instead, you need to put both. Please see my updated answer

Rahul Sharma Over a year ago

I tried that and it works like a charm but Can I check which formats I can upload or does it work with all the formats ?

Rahul Sharma Over a year ago

I uploaded the file with 15-07-2019 18:30 a null entry is passed in the table

Rahul Sharma Over a year ago

Instead of importing the formats like this input_formats = formats.get_format_lazy('DATETIME_INPUT_FORMATS') Can I specify the formats in CustomManager itself ?

|

kamran · Accepted Answer · 2019-07-12 11:16:29Z

0

You will need to add following in settings.py:

DATETIME_INPUT_FORMATS = [
'%Y-%m-%d %H:%M:%S',     # '2006-10-25 14:30:59'
'%Y-%m-%d %H:%M:%S.%f',  # '2006-10-25 14:30:59.000200'
'%Y-%m-%d %H:%M',        # '2006-10-25 14:30'
'%Y-%m-%d',              # '2006-10-25'
'%m/%d/%Y %H:%M:%S',     # '10/25/2006 14:30:59'
'%m/%d/%Y %H:%M:%S.%f',  # '10/25/2006 14:30:59.000200'
'%m/%d/%Y %H:%M',        # '10/25/2006 14:30'
'%m/%d/%Y',              # '10/25/2006'
'%m/%d/%y %H:%M:%S',     # '10/25/06 14:30:59'
'%m/%d/%y %H:%M:%S.%f',  # '10/25/06 14:30:59.000200'
'%m/%d/%y %H:%M',        # '10/25/06 14:30'
'%m/%d/%y',              # '10/25/06'
]

answered Jul 12, 2019 at 11:16

kamran

1246 bronze badges

4 Comments

Rahul Sharma Over a year ago

I did but it still says ["'10-7-2019 12:00' value has an invalid format. It must be in YYYY-MM-DD HH:MM[:ss[.uuuuuu]][TZ] format."]

kamran Over a year ago

By default USE_L10N is True, which will ignore custom datetime format settings, please add USE_L10N=False, in settings.py

kamran Over a year ago

Also if you would like to disable timezone support, add USE_TZ = False

Rahul Sharma Over a year ago

I have update the question with my settings.py and I already have USE_L10N=False and USE_TZ = False

Collectives™ on Stack Overflow

Upload any DateTime from CSV in Django

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related