TransferText incorrectly imports strings like "FS###" as Currency

Question

I am using the VBA DoCmd.TransferText command to import data from a CSV text file into a new table in my Access database. I have run into an issue where the text data in the first three columns in some of the files is imported as currency. I cannot figure out what is happening.

Here is a test database along with one CSV that imports correctly (VollintineLines.csv) ...

PipeID,UpstreamMH,DownstreamMH,Diameter,GISLength,Status
WS010353S,WS010353,WS010163,36,227.1984614,Fully Surveyed as Phase Work
WS011155S,WS011155,WS011154,8,418.5435318,Not Surveyed
WS011154S,WS011154,WS011153,8,303.9618911,Fully Surveyed as Phase Work

... and one that doesn't (CourtLines.csv).

PipeID,UpstreamMH,DownstreamMH,Diameter,GISLength,Status
FS020628S,FS020628,FS020462,10,278.72,Not Surveyed
FS020463S-1,FS020463,FS020462,12,248.39,Not Surveyed
FS020216S,FS020216,FS020215,12,227.53,Fully Surveyed as Phase Work

(Please ignore the unnamed objects in the database, it was just to figure out what is going on here and I didn't bother naming things.)

Here is the import code, you have to enable the Microsoft Office 16.0 Object Library Reference.

Private Sub Command0_Click()
Dim Path As FileDialog
Dim FileName As Variant
DoCmd.SetWarnings False
DoCmd.Hourglass True
Set Path = Application.FileDialog(msoFileDialogFilePicker)

    With Path
    .AllowMultiSelect = False
    .Title = "Select your File"
    .Filters.Add "All Files", "*.*"
    If .Show = -1 Then

        For Each FileName In .SelectedItems
        DoCmd.TransferText acImportDelim, , "TempPipeData", FileName, True
        Next FileName

    Else
        MsgBox "No File Selected to Import."
    End If

    End With
DoCmd.SetWarnings True
DoCmd.Hourglass False

End Sub

Answer 1

You have apparently encountered a rather obscure bug affecting TransferText calls that do not use an Import Specification. (It is also discussed on another site here.)

Workarounds include:

1. Use an Import Specification as described in this answer.
2. Create the table first, specifying the desired column types (Text in this case), and then import from the CSV file into the existing (empty) table.
3. If neither of the above options is desirable, then you could use COM Automation to
   • launch an instance of Excel,
   • have Excel open the CSV file,
   • save it to XLS or XLSX,
   • use TransferSpreadsheet in Access VBA to import the Excel data, then
   • delete the temporary XLS[X] file.