CSV File Import into Database Table DATE-Time Format issue

Ignition
1

Hi Gents,

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

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:

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:

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

2

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]
3

Hi Travis,

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