Text file import to MS Access missing first value

Question

I'm using VBA and a schema file to import a tab delimited text file into MS Access. Everything is working as it should, except the first field of the first record in the resulting access table is always null even though it contains a value in the text file.

The code is very simple:

    sqlCmd = "SELECT * INTO [tblTemp] FROM [Text; Database=" & sDeskPath & ";].[" & Replace(sFileName, ".", "#") & "]"
    CurrentDb.Execute sqlCmd, dbFailOnError

And the schema is correct:

    [data.txt]
    ColNameHeader=False
    Format=TabDelimited
    CharacterSet=ANSI
    DateTimeFormat=YYYY-MM-DD
    Col1=IssueID Long Width 10
    Col2=Month Text Width 10
    Col3=Year Long Width 5
    ...

I've done this successfully many times, but never encountered this issue. What on earth is causing the first value in the table to equal null??

Is it possible that there is a rogue tab character at the beginning of your text file? I'm betting you've already checked that, but if you haven't it's worth it just in case. — Blackhawk

Gord Thompson Gord Thompson · Accepted Answer · 2014-09-04T16:03:07

I was able to recreate your issue when the "data.txt" file was encoded as UTF-8 with BOM (Byte-Order Mark). Open that file in Notepad, do File > Save As..., change the "Encoding" from UTF-8 to ANSI, and then overwrite the existing file. That should fix it.

Text file import to MS Access missing first value

1 Answers