Can field type be change with VB from TEXT to Date ?

[gtaborda]

Hi there

I am importing a TEXT file into an Access table, but need to change some fields from TEXT to Date type

The reason is because the imported is not always the same...Field1 might me text one day, and the next it will be a date.

Can it be changed with VB?

thanks

 

[Remou]

This may suit:

[tt]strSQL = "ALTER TABLE Table1 ALTER COLUMN Field1 DateTime"
CurrentDb.Execute strSQL[/tt]

 

[SkipVought]

Hi,

"...Field1 might me text one day, and the next it will be a date."

When you try to put TEXT in DATE field --TILT!

Which rasies a question in my mind regarding the data that you are receiving. Are you REALLY SURE that the values that look to you like dates are REALLY DATES?

The text string, "2007/05/16" is NOT a date, as it appears. It can be converted to a date using a number of techniques, at which time the DATE VALUE is 39218. The Format that is Displayed, 5/16/2007, is just that; a FORMAT of a NUMBER.

I would import as text, analyze the import, due to this mixed content and then CONVERT the text to dates in a query.

Skip,

_[red][/red]

 

[gtaborda]

hi

I've tried your methond Remou and it seems to work fine, except that I get the following error:

error 3190 ('Too Many fields defined')

I am trying to define 4+ fields and the first 3 look ok, works, however I get error 3190 after defining the 4 the field...is there a limit ???

thanks

 

[gtaborda]

Skipvought

thanks for your thoughts, the dates look like dates, but might be (looks like they are) text.
When I receive the file, which is delimited by ;, the text is in """ however the dates are not in """

I am interested to know about the Query you mentioned...how do I export a text field to another table as a date via a query?

 

[SkipVought]

Please post representative samples of the "date" data you are importing.

Skip,

_[red][/red]

 

[gtaborda]

find example below of one of the rows on the file.

XXXX123232;"23563232323";"RTEEND CO., LTD.";"";"XXXXXX TEAM S.L.U.";"Laem Chabang";26/02/2007;"Le Havre";26/03/2007;29/03/2007;"MSC TORONTO";"MSCU8461210";"40HC";"CY/CY";325;5753.30;65.000;22/03/2007;30/03/2007;02/04/2007;02/04/2007;

 

[SkipVought]

... the imported is not always the same..."

So what you are saying is that on THIS record, fields 7, 9, 10, 18-21 are date strings, but not on EVERY record?

Do you mean that there may be date strings in DIFFERENT columns or that the date strings are ALWAYS in the same columns, but sometimes there is no date sting VALUE in the column?

Are the date strings ALWAYS in dd/mm/yyyy format?


Skip,

_[red][/red]

 

[gtaborda]

sorry,

I process different files (over 30).

Fields are correct on same file, they might differ on different files.

So on file XXXX.CSV, columns 10,12,15 are dates but on file ZZZZZ.csv columns 5,6,8 are dates

So what I do is rename each file as I need them, to to be a TEMP.CSV (TEMP.CSV is is LINKED to my Access application), then change the field type on the table CURRENT_TEMP (after copying the data from TEMP.CSV) then process the file as normal.

Yes, the original format comes as dd/mmm/yyyy.

thanks for your time

 

[SkipVought]

So what you need to do is for EACH of these files, in the IMPORT process, determine the date fields and have them parsed accordingly.

Skip,

_[red][/red]

 

[SkipVought]

...and if file xxxx.csv ALWAYS has the same format, time after time, then SAVE the FileSpec for EACH of these (xxxx, yyyy, zzzz etc) and it will save time on the next and succeeding imports.

Skip,

_[red][/red]

 

[gtaborda]

Thanks for your comments

I did think of doing that however new files are continuously being received, and are again different.

The Access application is being used by users which don't know anything about Access, so cannot be creating new FileSpec.

Also the Application must run without my intervention, so it hass to be a fully automated system.

The idea is that it can process any type of file, regardless the columns where the dates are, this is why I thought of changing the field type with VB

What a hassle, eh?

 

[Remou]

Some of this may be useful, depending on the size of your files:

Sub ConvertTable()
Dim db As DAO.Database
Dim rs As DAO.Recordset, rsDateOK As DAO.Recordset
Dim strTable, strSQLB, strSQL, strField, strFieldOk, strCannotConvert
Dim astrFieldOK

Set db = CurrentDb
strTable = "Table1"
Set rs = db.OpenRecordset(strTable)

strSQLB = "SELECT * FROM " & strTable & " WHERE "

'Check each field to see if it is a null or a date
For Each fld In rs.Fields
    strSQL = strSQLB & "Not IsDate([" & fld.Name _
    & "]) AND [" & fld.Name & "] Is Not Null"
    Set rsDateOK = db.OpenRecordset(strSQL)
    If rsDateOK.EOF Then
        'No false records returned, so this is a date field or empty
        strFieldOk = strFieldOk & "," & fld.Name
    Else
        'Formatting and record count
        rsDateOK.MoveLast
        strField = Space(15)
        Mid(strField, 1, Len(fld.Name)) = fld.Name
        
        'These are not empty or have a date error
        If rsDateOK.RecordCount = rs.RecordCount Then
            strCannotConvert = strCannotConvert & strField & ": All" & vbCrLf
        Else
            strCannotConvert = strCannotConvert & strField & ": " & rsDateOK.RecordCount & vbCrLf
        End If
    End If
Next

rsDateOK.Close
rs.Close
Set rsDateOK = Nothing
Set rs = Nothing

'Convert the fields that will convert to date
If MsgBox("Cannot Convert" & vbCrLf & strCannotConvert, vbOKCancel) = vbOK Then
    astrFieldOK = Split(Mid(strFieldOk, 2), ",")
    For i = 0 To UBound(astrFieldOK)
        strSQL = "ALTER TABLE " & strTable & " ALTER COLUMN " _
        & astrFieldOK(i) & " DateTime"
        
        db.Execute strSQL, dbFailOnError
    Next
End If
End Sub

 

[gtaborda]

Hi Remou

I tried your code but it seems to also cause the same limitation problem....after 3 fields have been changed from Text to DateTime, it stops

I've also tried to manually change the field type directly on the table, and gives me the same problem...

So perhaps the problem is not with the code, but the table/Access perhaps?

I have also asked the person who creates the reports to send them with fields separated by ";" instead of TAB, and seems like this has fixed (part of) the problem

thanks to all for your input