locked
ADO query to text file returning nulls on certain column RRS feed

  • Question

  • Hi,

    I am using ADO in Excel VBA to retrieve data from a .txt file, with a comma delimiter. To help my explaination, here is what a small sample of the data looks like:

    ColumnA,ColumnB,ColumnC,ColumnD,ColumnE,ColumnF
    FR0000073298,ABC,122444,B188NJ2,FR,COM
    FR0000121014,ABC,13413,4061412,FR,COM
    TH0015010000,EFG,227400,6889924,TH,COM
    BRRDCDACNOR3,EFG,41900,B1Z8B68,BR,COM

    The problem I am having is that my ADO query (a simple 'SELECT *') to this text file is returning NULL for ColumnD when the value does not contain purely numerical data.

    Does anyone know why this might be happening?

    For more information about my setup, I am using the following:

    • Excel 2003
    • ADO v2.7
    • Connection string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & msInputFileFolder & _
              ";Extended Properties=""text;HDR=Yes;FMT=Demlimited(", ")"";" & _
              "Persist Security Info=False;"
    • SQL = "Select * From [ABC.txt]"

    Many Thanks!


    Wednesday, May 30, 2012 4:16 PM

All replies

  • Do the first rows in Column D all contain numeric values? If so, ADO decides that it is a number field, and it will discard all non-numeric values.

    Regards, Hans Vogelaar

    Wednesday, May 30, 2012 7:59 PM
  • Even if I change the text file to have text in the first row, the same thing happens. I think you are along the right lines though.

    Interestingly, when inspecting the ADO recordset object with VBE Watch, the data type for columnD is set to 'adInteger', rather than 'adVarWChar' as the other columns within the recordset are:

    Any idea how to override this behaviour? Or any work arounds?

    Its worth me saying that around 70% of the data within ColumnD is numerical. This might be why ADO is assigning it an Integer type, would be daft if it is though.



    • Edited by Dave_JB Thursday, May 31, 2012 9:02 AM
    Thursday, May 31, 2012 8:26 AM
  • Adding a dummy first row with data of the correct type usually helps - no idea why ADO persists in seeing a number field if the first value is text...

    Regards, Hans Vogelaar

    Thursday, May 31, 2012 2:20 PM
  • For anyone else who runs into a similar problem, it seems that the Column before ColumnD (ColumnC) was numeric, so for some reason ADO was applying the same data type to ColumnD. If I moved the data from ColumnD to a position inbetween two Text columns it works OK.

    Unfortunately though for the project I am trying to use this for, the format of this text file is fixed.

    To work around it, in code I extract all the data from the text file, place it into a temporary spreadsheet and then pull my ADO dataset from that.

    Far from ideal, but its a work around for this bug.

    Wednesday, June 6, 2012 9:30 AM