SQL Import

[mozga628]

i have created an excel table with imported data from a SQL server.
the data being imported in a single column is the users Domain\UID.
Columns B, C, D, etc are used to MANUALLY enter application user ID's (mainframe, etc).

today a new hire was added to the SQL table and was successfully inserted into column A in my excel spreadsheet...
the problem is that all the rows with application ID's did not shift down to accommodate the inserted new user record.
now everything below the new hire is off by 1 row.

how can i import updates with inserted new users AND insert blank cells on the same row as the new domain\uid?

BEFORE
Adam userid1 userid2 userid3
Beth userid4 userid5 userid6
David userid7 userid8 userid9
Eddie userid10 userid11 userid12

AFTER
Adam userid1 userid2 userid3
Beth userid4 userid5 userid6
Cathy userid7 userid8 userid9
David userid10 userid11 userid12
Eddie

SHOULD BE
Adam userid1 userid2 userid3
Beth userid4 userid5 userid6
Cathy
David userid7 userid8 userid9
Eddie userid10 userid11 userid12

if this has anything to do with the SQL, the command am using is:
Select AD_ID From "DATABASENAME" order by AD_ID

 

[mrshl9898]

So SQL returns column A, while BC and D are static data in the spreadsheet?

There's no reason for them to shift with the new data.

You'll need to create a table with all the names and other data and keep adding to it as a new name appears.


I'd grab the before, copy and paste on a new tab "Data", then on the SQL tab use VLOOKUPs to return the information. When you come across N/As as the name list grows, then add that name and IDs to the bottom of the Data tab.

 

[mozga628]

obviously i was hoping for an automated solution, but your idea is a good one, and well within my excel skillset.
i have already built tabs for reporting purposes using index /match refencing this data, and to build another table is going to make this spreadsheet excessively bulky very quickly. i appreciate your response and if no automated solution presents itself, its seems like the path i will take.

 

[mrshl9898]

Unless the other data is found in your database somewhere I don't see any option here.

There's always PowerQuery if you're worried about size. You'd still need to manually update the table, but you could have that plus the SQL in the background linked by ID, then just insert the combined table into your spreadsheet.

 

[John_w]

An automated solution could use Worksheet_Change to trigger when the table cells have changed and move rows in the adjacent columns. You would need to store the current column A values in an array in order to determine which row has changed.