# CSV File Import into Database Table DATE-Time Format issue

**URL:** <https://forum.inductiveautomation.com/t/csv-file-import-into-database-table-date-time-format-issue/2280>\
**Category:** Ignition\
**Created:** [October 20, 2010, 1:45pm UTC](https://forum.inductiveautomation.com/t/csv-file-import-into-database-table-date-time-format-issue/2280 "2010-10-20T13:45:05Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![focus-eng](https://avatars.discourse-cdn.com/v4/letter/f/fbc32d/32.png) [@focus-eng](https://forum.inductiveautomation.com/u/focus-eng)\
**Post date:** [October 20, 2010, 1:45pm UTC](https://forum.inductiveautomation.com/t/csv-file-import-into-database-table-date-time-format-issue/2280/1 "2010-10-20T13:45:05Z")

</div>

Hi Gents,

I am trying to read in a CSV File with the following data.

```auto
Date First Weight Weight1 DateSecondWeight Weight2
20/05/2009 08:09 16240 20/05/2009 08:38 10940 5300 106012 9896 PEAT SCREEN DEB
20/05/2009 08:21 44400 20/05/2009 08:40 16980 27420 106014 9861 INCOMMING PEAT
20/05/2009 09:50 42520 20/05/2009 10:06 18240 24280 106015 9863 INCOMMING PEAT
20/05/2009 10:16 44740 20/05/2009 10:42 17060 27680 106016 9889 INCOMMING PEAT
```

The problem is that the date/time format is dd/mm/yyyy hh:MM, what i need to log into database is  
yyyy-mm-dd hh:MM:ss

What i have so far tried is this:

```auto
import system
path = fpmi.file.openFile("csv")
if path != None:
   text = system.file.readFileAsString(path)
   line = text.split("\n")

   for i in range(len(line)-1):
      #This will get the words as a list of strings
      Column = line[i].split(",")
      #Date format we have 20/05/2009 08:09
      #We Need 2009-05-20 08:09:00
      result = system.db.runQuery("SELECT STR_TO_DATE('"+ Column[0]+"','%d/%m/%Y %h:%i')")    
      print "Before :\t\t / After: "
      print [str(Column[0])+ ", " + str(row[0]) for row in result]
```

The Idea here is that the converted date then gets inserted into the Database TABLE CSV\_IMPORT  
with all the other data.

However the printed output is this:

```auto
Before : / After: 
['20/05/2009 08:09, Wed May 20 08:09:00 BST 2009']
Before : / After: 
['20/05/2009 08:21, Wed May 20 08:21:00 BST 2009']
Before : / After: 
['20/05/2009 09:50, Wed May 20 09:50:00 BST 2009']
Before : / After: 
['20/05/2009 10:16, Wed May 20 10:16:00 BST 2009']
```

Any Help would be great, thanks

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [October 20, 2010, 3:27pm UTC](https://forum.inductiveautomation.com/t/csv-file-import-into-database-table-date-time-format-issue/2280/2 "2010-10-20T15:27:52Z")

</div>

Java has great functions for parsing and formatting dates. Here is an example that can parse a given date format into a date and then into a string format for the db:

[code]from java.util import Date  
from java.text import SimpleDateFormat

StringFormat = ‘dd/MM/yyyy HH:mm’  
DBFormat = ‘yyyy-MM-dd HH:mm:ss’

StringDate = ‘20/05/2009 08:09’

format = SimpleDateFormat(StringFormat)  
dbFormat = SimpleDateFormat(DBFormat)

date = format.parse(StringDate)  
DBStringDate = dbFormat.format(date)

print DBStringDate[/code]You can use this in your script like so:

[code]import system  
from java.util import Date  
from java.text import SimpleDateFormat

StringFormat = ‘dd/MM/yyyy HH:mm’  
DBFormat = ‘yyyy-MM-dd HH:mm:ss’  
format = SimpleDateFormat(StringFormat)  
dbFormat = SimpleDateFormat(DBFormat)

path = fpmi.file.openFile(“csv”)  
if path != None:  
text = system.file.readFileAsString(path)  
line = text.split("\n")

for i in range(len(line)-1):  
#This will get the words as a list of strings  
Column = line[i].split(",")  
#Date format we have 20/05/2009 08:09  
#We Need 2009-05-20 08:09:00

```
  date = format.parse(Column[0])
  DBStringDate = dbFormat.format(date)
  
  print "Before :\t\t / After: "
  print Column[0], DBStringDate[/code]
```

---

<div class="post-metadata">

**Author:** ![focus-eng](https://avatars.discourse-cdn.com/v4/letter/f/fbc32d/32.png) [@focus-eng](https://forum.inductiveautomation.com/u/focus-eng)\
**Post date:** [October 20, 2010, 8:43pm UTC](https://forum.inductiveautomation.com/t/csv-file-import-into-database-table-date-time-format-issue/2280/3 "2010-10-20T20:43:20Z")

</div>

Hi Travis,

You’re a gent. It worked like a charm, thanks