Search code examples
excelvbatextexport-to-csv

Export Data from Column A into a text file based on Column B

I am looking to populate a number of SQL scripts using data from Excel.

In one column there is a list of usernames.
In an adjacent column, there is a list of locations.

I have a template of a SQL script, and I need a TXT file generated for each location, with the relevant users inserted.

For example, if this were the data set:
enter image description here

And this the script template:

LOCATIONA

update USERTABLE
set ACCOUNT_LOCKED = '1'
where USERID in
(%LOCATIONA_USERS%);

LOCATIONB

update USERTABLE
set ACCOUNT_LOCKED = '1'
where USERID in
(%LOCATIONB_USERS%);

The output would need to be like this:

LOCATIONA

update USERTABLE
set ACCOUNT_LOCKED = '1'
where USERID in
('User1','User2','User3','User4','User5');

LOCATIONB

update USERTABLE
set ACCOUNT_LOCKED = '1'
where USERID in
('User6','User7','User8','User9','User10','User11','User12');

It could either export to a single file, but with a separate script for each location, or a separate file per location.

Solution

  • I have created a similar dataset, with some formulas. You can inspire yourself on this in order to get what you need:

    • Column A contains the names of the locations.
    • Column B contains the names of the users.
    • Column C filters on "LocationA", the formula is shown in C1.
    • Column D filters on "LocationB", the formula (shown in D1) is similar to the one in C1.
    • A TextJoin based formula is used for collecting the list of users, both for "LocationA" and "LocationB", the formula (=TEXTJOIN(",",TRUE,C2:C6)) is shown preceeding the values:

    enter image description here