Import CSV and force all fields to Text format

Question

I am importing a series of CSV files into Access tables. I have the following line that imports each file:

    DoCmd.TransferText acImportDelim, , FN, F.Path, True

This import statement works and creates the necessary table. However, it creates the field types from the data, and depending on the first few rows of the data it may create a field as numeric that should be text - and then causes an error when it encounters a text value later in the file.

How can I force the field type to Text for every field in the input file? I've used Import Specifications before, but first the file format (provided by others outside my control) may change from time to time, and second it's a very "wide" file with 200+ column, so this isn't a practical answer.

Answer 1

This is not a great workaround, but I had to go through the process anyway to get around the 255 field limit in tables. In short, the import steps I ended up with are

1. Read the 1st line of the file as an inputstream
2. Split the line to get the field names, put them in a data dictionary table and then manually mark the ones I want to import
3. Use CREATE TABLE to create a new data table (selected fields only) with all of the fields set to TEXT
4. Read each line of the file as an inputstream
5. Split the line to get the data for each field
6. Use INSERT INTO to add the selected fields to the data table

Cumbersome, but it solves both problems - I'm not limited to 255 fields in the input files and I can control the data type of the fields as I create them.

The code, if anyone cares, is

Function Layout()

Set db = CurrentDb()
Folder = DLookup("[data folder]", "folder")
Dim FSO As New FileSystemObject
Set flist = FSO.GetFolder(Folder).Files
db.Execute ("delete * from [data dictionary]")

For Each F In flist
    FN = Left(F.Name, InStr(F.Name, ".") - 1)
    FT = Mid(F.Name, InStr(F.Name, ".") + 1)
    If FT <> "csv" Then GoTo Skip

    If TestFile(F.path) = "ASCII" Then
        Set instream = FSO.OpenTextFile(F.path, ForReading, , 0)
        Else: Set instream = FSO.OpenTextFile(F.path, ForReading, , -1)
        End If

    header = instream.ReadLine
    Data = Split(header, ",")
    For i = LBound(Data) To UBound(Data)
        SQL = "insert into [data dictionary] ([table], [field], [index]) select "
        SQL = SQL & "'" & FN & "','" & Data(i) & "','" & i & "'"
        db.Execute SQL
        Next i
Skip: Next F

End Function

Function TestFile(ByVal path As String)
   Dim buffer As String
   Dim InFileNum As Integer
   Dim firstByte As Integer
   Dim secondByte As Integer
   Dim thirdByte As Integer

   buffer = String(100, " ")

   InFileNum = FreeFile

   Open path For Binary Access Read As InFileNum

   Get InFileNum, , buffer

   Close InFileNum

   firstByte = Asc(Mid(buffer, 1, 1))
   secondByte = Asc(Mid(buffer, 2, 1))
   thirdByte = Asc(Mid(buffer, 3, 1))

   If (firstByte = 255 And secondByte = 254) Then
       TestFile = "Unicode"
   ElseIf (firstByte = 254 And secondByte = 255) Then
       TestFile = "Unicode"
   ElseIf (firstByte = 239 And secondByte = 187 And thirdByte = 191) Then
       TestFile = "Unicode"
   Else
       TestFile = "ASCII"

   End If

End Function

Function import()

Folder = DLookup("[data folder]", "folder")
Set db = CurrentDb()
Dim FSO As New FileSystemObject

Set Tlist = db.OpenRecordset("select [table] from [data dictionary] where ([required]<>'') group by [table]")
Tlist.MoveFirst
Do While Not Tlist.EOF
    TN = Tlist.Fields("table").Value
    Delete_table (TN)
    Set flist = db.OpenRecordset("select * from [data dictionary] where [required]<>'' and [table]='" & TN & "'")
    flist.MoveFirst
    Text = ""
    Do While Not flist.EOF
        FN = flist.Fields("Field")
        Text = Text & "," & FN & " " & IIf(InStr(FN, "Date") > 0 Or InStr(FN, "_DT") > 0, "DATETIME", "TEXT")
        flist.MoveNext
        Loop
    SQL = "CREATE TABLE " & TN & "(" & Mid(Text, 2) & ")"
    db.Execute SQL

    path = Folder & "\" & TN & ".csv"
    If TestFile(path) = "ASCII" Then
        Set instream = FSO.OpenTextFile(path, ForReading, , 0)
        Else: Set instream = FSO.OpenTextFile(path, ForReading, , -1)
        End If

    header = instream.ReadLine
    Do While Not instream.AtEndOfStream
        Line = parser(instream.ReadLine)
        Data = Split(Line, ",")
        flist.MoveFirst
        Text = ""
        Do While Not flist.EOF
            n = flist.Fields("index").Value
            Text = Text & ",'" & Data(n) & "'"
            flist.MoveNext
            Loop
        SQL = "insert into [" & TN & "] values(" & Mid(Text, 2) & ")"
        db.Execute SQL
        Loop

    Tlist.MoveNext
    Loop
x = MultipleCodes()
MsgBox ("done")
End Function

Function parser(S)
parser = S
i = InStr(S, Chr(34))
If i = 0 Then
    parser = S
    Else
        j = InStr(i + 1, S, Chr(34))
        T = Mid(S, i + 1, j - i - 1)
        T = Replace(T, ",", ";")
        parser = Left(S, i - 1) & T & parser(Mid(S, j + 1))
    End If
End Function