Import VBScript General Date Time Into MS Access Date/Time Column

Setup

I have a VBScript for driving the stress testing of a web service. The script creates a data file of measurements, with each record timestamped with a general date/time:

FormatDateTime(Now(), 0)

This creates dates like

mm/dd/yyyy hh:mm:ss [AM|PM]

I need to import this data file into a MS-Access 2003 database. The table in the Access database has a column called TimeStamp with a data type of Date/Time and format of General Date.

My import spec has a field named Timestamp with a Date Type of Date/Time. In the Dates, Times, and Numbers frame the following options are selected:

Date Order: MDY
Date Delimiter: /
Time Delimiter: :
Four Digit Years: Checked

The data file is tab delimited with field names in the first row.

Problem

When I import my data file, each record fails on the Timestamp field with a Type Conversion Failure error.

Question

Is there a way to import the data as-is, or do I have to first "massage" the timestamp field into 24-hour date/time format? If the latter, is there a way to format the timestamp in my VBScript code to write the date/time in 24-hour format? I tried

FormatDateTime(Now(), "mm/dd/yyyy hh:mm:ss")

but VBScript barfed at that.

From stackoverflow

• VBScripts FormatDateTime function does not supprot general format strings only a constrained set of constants and 0 is the only choice you have to include both date and time.

  Could it be the prescence of the AM/PM that is causing confusion?

  You are in a US locale?

  Try SetLocale(2057)

  This puts the script in to the en-GB (UK) locale. The default date time format is dd/mm/yyyy hh:mm:ss. Its 24 hour perhaps your import would prefer that format.

• Why not output it in ISO format. That way, it can't screw up the import when you change locals, etc. The code to do this was found here:

  'Returns current system date and time in the 
  'ISO 8601 format YYYY-MM-DDThh:mm:ss.
  
  ISODateandtime = Format$(Now(), "yyyy-mm-ddTHH:MM:SS")
  

  Patrick Cuff : That link is flagged by our web washer, so I can't get there from work; I'll have to check when I get home. This is VBScript though, so I don't think Format() is supported.

  Patrick Cuff : After checking the link, this would work in VB and VBA, but not VBScript.

• Why not skip the data file and write the results directly to the database?

  EDIT: Or use this script to move data from the file to the DB.

  
  Set DataFile = CreateObject("Scripting.FileSystemObject").OpenTextFile("FileName",1)
  
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=database.mdb"
  
  Set objRecordset = CreateObject("ADODB.Recordset")
  objRecordset.CursorLocation = 3
  objRecordset.Open "SELECT * FROM TableName" , objConnection, 3, 3
  
  Do Until DataFile.AtEndOfStream
      arrTemp = Split(DataFile.ReadLine, vbTab)
      If IsArray(arrTemp) Then
       objRecordset.AddNew
       objRecordset("FieldName1") = arrTemp(0)
       objRecordset("FieldName2") = arrTemp(1)
       objRecordset("FieldName3") = arrTemp(2)
       objRecordset.Update
      Else
       'Only one item of data is on this line
      End If 
  Loop 
  
  DataFile.Close
  objConnection.Close
  Set DataFile = Nothing
  Set objRecordset = Nothing
  Set objConnection = Nothing 
  

  Patrick Cuff : That's a thought, but would require some extensive surgery to the scripts I inherited. Perhaps in the long run...

• From this link; I can mimic VB's Format() function to format the date as 24-hour date/time:

  Function Format(vExpression, sFormat) 
  
      set fmt = CreateObject("MSSTDFMT.StdDataFormat") 
      fmt.Format = sFormat 
  
      set rs = CreateObject("ADODB.Recordset") 
      rs.Fields.Append "fldExpression", 12 ' adVariant 
  
      rs.Open 
      rs.AddNew 
  
      set rs("fldExpression").DataFormat = fmt 
      rs("fldExpression").Value = vExpression 
  
      Format = rs("fldExpression").Value 
  
      rs.close: Set rs = Nothing: Set fmt = Nothing 
  
  End Function
  

  From the article:

  The MSSTDFMT object depends on the availability of MSSTDFMT.dll, which is installed by Visual Studio 6.0.

  The MSSTDFMT.dll appears to be available on Windows XP and Windows 2003 servers; I checked a few machines that have never had MS Visual Studio installed and the DLL was present.

  I changed my VBScript code to use this function to put the dates in a format Access can import:

  Format(Now(), "mm/dd/yyyy hh:mm:ss")
  

  CodeSlave : Excellent. Regardless, I still think you should import as ISO rather than under the local date format - MS is notorious for getting it wrong when there is some ambiguity.

  Patrick Cuff : Unfortunately, it doesn't seem Access recognizes ISO dates when importing either, no matter how the column is defined.

• I don't know if this would help, but is there any way for you to process your date output in your VBScript to make sure that the dates have leading zeroes and 4-digit years? If you do that, then you could import it as a text field and then process it, because you would know that the first 10 characters are the unambiguous date, and then you could parse the time with a little bit of data massaging. If that isn't possible, but you know that there's a SPACE between the date and the time, then you could again import as text and parse the date from the time using the space as your delimiter, and then process the time field.

  I know you said you didn't want to have to parse the data after import, but if you can't get the import data into a format that Access can process, then you don't have a choice.

  Another thought:

  Have you tried Excel's import functionality? It may resemble the Access import superficially, but it often gets you completedy different results on the exact same data.

  Sorry I can't offer anything definitive, though.

  --
  David W. Fenton
  David Fenton Associates