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

Question

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.

Answer 1

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.

Answer 2

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")

Answer 3

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 

Answer 4

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")

Answer 5

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