Reading Excel file with SQL returns null due to empty column

I am attempting to read in an Excel File from a third party but it falls over and returns null due to empty column.

The file has 50 columns, a blank column and a further 48 columns. I only want the first 50 columns. If I delete or populate the empty column in Excel it works but I need to read 50+ files one after the other, regularly, so opening/deleting/saving isn’t an option.

Function read_excel_file(StrPath, StrFile, ByRef TotalFileArray() As Variant)

Dim ReadFileArray() As Variant

Dim connectionString As String
connectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=""" & StrPath & StrFile & """;" & _
    "Extended Properties=""Excel 12.0;HDR=Yes;"""
'This assumes the Excel file contains column headers -- HDR=Yes


 'Routine to get unknown sheet name
    Set conn = CreateObject("ADODB.Connection")
    conn.connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=""" & StrPath & StrFile & """;" & _
    "Extended Properties=""Excel 12.0;HDR=No"""
    
    conn.Open
    Set bs = conn.OpenSchema(20) ' 20 = adSchemaTables
    Do Until bs.EOF = True
        'Debug.Print bs.Fields!Table_Name.Value
        SheetName = bs.Fields!Table_Name.Value
        bs.MoveNext
    Loop
    bs.Close: conn.Close
    Set bs = Nothing
    Set conn = Nothing

Dim sql As String
'sql = "SELECT Meter Id, Date, 00:30, 01:00, 01:30, 02:00, 02:30, 03:00, 03:30, 04:00, 04:30, 05:00, 05:30, 06:00, 06:30, 07:00, 07:30, 08:00, 08:30, 09:00, 09:30, 10:00, 10:30, 11:00, 11:30, 12:00, 12:30, 13:00, 13:30, 14:00, 14:30, 15:00, 15:30, 16:00, 16:30, 17:00, 17:30, 18:00, 18:30, 19:00, 19:30, 20:00, 20:30, 21:00, 21:30, 22:00, 22:30, 23:00, 23:30, 24:00:00 FROM [" + SheetName + "]" '

sql = "SELECT * FROM [" + SheetName + "]" '

'Dim rs As Object 'New adodb.Recordset
'Set rs = CreateObject("ADODB.Connection")
Dim rs As New ADODB.Recordset

rs.Open sql, connectionString

ReadFileArray() = rs.GetRows 'Puts the data from the recordset into an array
rs.Close
Set rs = Nothing

I did try using specific requests for the columns I wanted but this still returned null. I attempted a single column but again returned null.

Is there a way around the empty column? Even if reading the file in a different way. (I nearly always read in csv files which are a breeze to get into an array and deal with.)

Picture of source data headers

Answer

Given that you have no choice but to work with the files in the format provided, the best approach/workaround (rather than manually opening/deleting/saving/closing the files individually) would appear to be to pre-process them using VBA to delete the offending column, before then running your query.

Advertisement

Answer