Query on Load to Worksheet adding Extra Columns to ListObject Table

sifar 1 Reputation point
2020-11-20T19:16:57.127+00:00

I am trying to place a Query Load at a specific Column and Row in my workbook. However, when the query loads the data to the worksheet, it adds extra columns to the created ListObject Table, depending upon the number of columns that were offset From Column A.

for .e.g.

1] If i Load the Query data to Cell B5, the Table adds 1 extra Column at the end.

2] If i Load the Query data to Cell D5, the Table adds 3 extra Columns at the end.

3] Also the Table always gets highlighted (Selected) whether loaded "Manually" OR via "VBA Code".

I am not sure if this is the default Query load behavior or a Problem with the Query itself. It would be nice if someone could review the attached sample workbook, Query and csv files and assess where the problem lies?

BTW, i am using Excel 2016 Pro 64 bit version and to be sure it is not an Excel issue, i just reset (repaired) my Excel to the default condition.

Here is a screenshot:
41507-test.gif
Here are the files:

Sample CSV files

Sample Test workbook

Hope someone has an idea and possible solution to remedy this issue.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,965 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,640 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erin Ding-MSFT 4,456 Reputation points
    2020-11-23T10:57:23.937+00:00

    @sifar

    I didn’t reproduce your issue with Excel 2016 Professional Plus on my test environment.

    You could try to open Excel in safe mode to eliminate interference from add-ins and startup items.
    Press Windows+R, type excel /safe, click OK.

    If nothing is wrong when opening Excel in safe mode:

    • Move the files in C:\Users\<User Name>\AppData\Roaming\Microsoft\Excel\XLSTART to other location temporarily, then check if the issue still exists.
    • If it doesn't work, go to File>Options>Add-Ins. Towards the bottom of the window, where it says Manage, click the Go button. In the dialog box, uncheck the add-ins to disable them.

    Besides, you could also create a copy of the Query data to check the issue.

    Regards,
    Erin

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.