Alter a table's Data Type based on the data within the field (1 Viewer)

gblack · Dec 12, 2018

I have some tables that do not import correctly. Essentially, instead of posting the correct data type, all fields are pulled in as text values.

What I want to do is loop through the data in each field and reset the Data Type (for each field) based on the data within (which is currently formatted as Text).

For Example: If I loop through the data in Field1 and all the data Data is a Date, I want to change the Data-Type of the table (for said field) to "Date"... If the data is currency, then change the data type of that field to currency etc... etc...

I think I may be able to use the TypeName() function (to evaluate the data into a data-type...can I?). If so, I was also hoping that someone may already have run into this issue and had a piece of code (or a function they've used) that I could look at, so I don't have to invent the wheel.

Respectfully,
Gary

The_Doc_Man · Dec 12, 2018

I have some tables that do not import correctly. Essentially, instead of posting the correct data type, all fields are pulled in as text values.

I have seen a few solutions to this kind of problem. They usually relate to Excel as the source of the table. If that's not right, then see my (B) solution, which doesn't "care" if it is not from Excel.

A) Create the table first with the correct data types for all fields. Then import to it.

B) Import the table to a temporary table. Then create the target table (as described in (A) above). Then use an "INSERT INTO final table (field list) ... SELECT fieldA, CLng(fieldB), CDATE( fieldC), etc. ... FROM temp table" and assure the correct types are asserted by use of conversion functions. Then delete the temp table in its entirety.

C) Create the table as in (A) above. Then LINK to the table and use the "IMPORT INTO" method described in (B), then unlink the source table.

Minty · Dec 12, 2018

If you regularly import these tables, then leave things as they are, and treat the incorrect tables as a staging table. Use that to update or import to the real tables with your data.

There will almost certainly be a reason those fields aren't imported correctly, using a staging table let you handle those exceptions and report on them.

plog · Dec 12, 2018

No real specific help, just thoughts and general advice:

1. This sounds too generic. It sounds like you need something to work in all cases because you have no idea what data is coming in. Is that true? I mean, you don't know that its a Sales table and that it has sales, salespersonID, salesDate, etc in it? Generally what I do is import data to a staging table then run APPEND queries to then move the data from the staging tables to the actual tables with the correct fields.

2. Even if #1 doesn't apply, I think a better method would be to not loop through the data, but run queries testing the data. For example, to see if Field1 is comprised of all dates or numbers you would run this query:

Code:

SELECT Max(IsDate([Field1])) AS DateField, MAX(IsNumeric([Field1])) AS NumberField
FROM YourTable;

If DateField or NumberField comes back with -1, that means true.

3. Then to change the data type you run an ALTER TABLE query (https://www.w3schools.com/sql/sql_alter.asp).

gblack · Dec 12, 2018

Yes, basically I will never have any idea what the data will be in field1, field2, field3...etc...

I want to loop through the data in table1.field1 and check that data using TypeName().

If the data in field1 is say: all Integers... then I want to change the field's data type to "Number"... Or I can make a new table and append the fields (with the correct data types) to the table and move along that way... then append the overall data, once the new table is built.

What I'm looking for is some code that I can sink my teeth into, if any exists... I was simply checking, to see if anyone had done this, or had a link (I wasn't able to google anything, sadly) before I start coding it myself.

MajP · Dec 12, 2018

This is pretty close. It reads a table where all the fields are text fields, but the data in the field could be consistently another datatype. I tested by taking a data table and changing all fields to text fields. It reads the table and does its best to "elevate" to the proper data type. So if the field is mixed with numbers and strings it has to store as text. If it has longs and doubles it has to be stored as double. You cannot use TypeName because that works on a variant. If you convert a number stored as string to variant it will see it as a string. You have to runs some tests and play with the logic some. I stored doubles 1.23 and it guessed Date. That is because it is a valid date as per how MS stores dates. So need to add some additional logic.

Code:

Public Function GetFieldTypes(tblName As String) As Dictionary
  'Add a reference to MSScripting runtime
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim varVal As Variant
  Dim newType As Long
  Dim lastType As Long
  Dim FieldTypes As New Dictionary
  Set db = CurrentDb
  Set rs = db.OpenRecordset(tblName)
  For Each fld In rs.Fields
    rs.MoveFirst
    lastType = -1
    Do While Not rs.EOF
      If Not IsNull(fld.Value) Then
        varVal = CVar(fld.Value)
        If IsDate(varVal) Then
          newType = dbDate
        ElseIf IsNumeric(varVal) Then
           If CLng(varVal) = varVal Then
            If varVal = 0 Or varVal = -1 Then
              newType = dbBoolean
             Else
              newType = dbLong
             End If
          Else
            newType = dbDouble
          End If
        Else
          If Len(varVal) > 255 Then
            newType = dbMemo
          Else
            newType = dbText
          End If
        End If
        If lastType = -1 Then lastType = newType
        If newType <> lastType Then
          'convert text to memo
          If lastType = dbText And newType = dbMemo Then
              lastType = dbMemo
          'convert longs, dates, and booleans to double
          ElseIf (lastType = dbLong Or lastType = dbDate) And newType = dbDouble Then
              lastType = dbDouble
          End If
          'need to write more code code to elevate other things. a long or int would become a double or single etc
          'double would turn a date into a double etc.
        End If
      End If
      rs.MoveNext
    Loop
   ' Debug.Print "Type " & lastType
    FieldTypes.Add fld.Name, lastType
  Next fld
  Set GetFieldTypes = FieldTypes
End Function
Public Sub CreateTable(tblName As String)
  Dim db As DAO.Database
  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field
  Dim FieldTypes As Dictionary
  Dim TheKeys() As Variant
  Dim I As Integer
  Set db = CurrentDb
  Set FieldTypes = GetFieldTypes(tblName)
  TheKeys = FieldTypes.Keys
  Set tdf = db.CreateTableDef(tblName & "NEW")
  For I = 0 To UBound(TheKeys)
    Debug.Print TheKeys(I) & " " & FieldTypes.Item(TheKeys(I))
    tdf.Fields.Append tdf.CreateField(TheKeys(I), FieldTypes.Item(TheKeys(I)))
  Next I
  CurrentDb.TableDefs.Append tdf
  Application.RefreshDatabaseWindow
End Sub

Public Sub TestTable()
   CreateTable "employeesStr"
End Sub

There is probably a much smarter way to do it, but this is what came to mind.

MajP · Dec 13, 2018

Code:

Public Function GetFieldTypes(tblName As String) As Dictionary
  'Add a reference to MSScripting runtime

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim varVal As Variant
  Dim newType As Long
  Dim lastType As Long
  Dim FieldTypes As New Dictionary
  Set db = CurrentDb
  Set rs = db.OpenRecordset(tblName)
  For Each fld In rs.Fields
    rs.MoveFirst
    lastType = -1
    Do While Not rs.EOF
      If Not IsNull(fld.Value) Then
        varVal = CVar(fld.Value)
        If IsDate(varVal) And Not IsNumeric(varVal) Then
          'Assumption is the date field is formatted as string
          newType = dbDate
        ElseIf IsNumeric(varVal) Then
           If CLng(varVal) = varVal Then
            If varVal = 0 Or varVal = -1 Then
              newType = dbBoolean
             Else
              newType = dbLong
             End If
          Else
            newType = dbDouble
          End If
        Else
          If varVal = "Yes" Or varVal = "No" Or varVal = "True" Or varVal = "False" Then
            newType = dbBoolean
          ElseIf Len(varVal) > 255 Then
            newType = dbMemo
          Else
            newType = dbText
          End If
        End If
        If lastType = -1 Then lastType = newType
        If newType <> lastType Then
          'convert text to memo
          If lastType = dbText And newType = dbMemo Then
              lastType = dbMemo
          'convert longs, dates, and booleans to double
          ElseIf (lastType = dbLong Or lastType = dbDate) And newType = dbDouble Then
              lastType = dbDouble
          End If
          'need to write more code code to elevate other things. a long or int would become a double or single etc
          'double would turn a date into a double etc.
        End If
      End If
      rs.MoveNext
    Loop
   ' Debug.Print "Type " & lastType
    FieldTypes.Add fld.Name, lastType
  Next fld
  Set GetFieldTypes = FieldTypes
End Function

Update to assume dates will be stored as string formatted and yes not fields could be strings of Yes, No, True, False

Frothingslosh · Dec 13, 2018

You also need a way to deal with 'numeric' fields that require leading zeros to remain, such as account, contract, and invoice numbers and ZIP codes.

And also to account for false-positives for IsNumeric when checking something like '123456d7'.

jdraw · Dec 13, 2018

gblack,

Can you describe in simple terms why you need this? It seems you could concoct a table- and MajP seems to have a solution -- but that table could have a different structure every time you do the input/import.
Not knowing the details of your needs, it seems a more general approach is to identify what you need for the "business" from the imported/inputted data. Create a standard table of known design, and a "generic collector table/temp table" for capturing the initial import/input. Then a "conversion routine" that maps the temp table data to your standard table. That would seem to provide a standard table that could be used repeatedly.

Input-->TempTable-->Conversion/Validation Routine-->StdTable--> Main Processing

Perhaps I have missed your key requirement, but the temp table>convert>final table is an often used approach.

Note also that Allen Browne has a number of DAO related functions and examples here.

Good luck with your project.

Alter a table's Data Type based on the data within the field (1 Viewer)

gblack

Registered User.

The_Doc_Man

Immoderate Moderator

Minty

AWF VIP

plog

Banishment Pending

gblack

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

Frothingslosh

Premier Pale Stale Ale

jdraw

Super Moderator

Similar threads

Users who are viewing this thread