Avatar of myself generated using AI

Transform data from excel column to IN operator inside SQL WHERE statement

I realized I wasted plenty of time doing this in excel and decided to build a tool to do it.

I work with analysts in my work and some are more comfortable using excel rather than querying the database directly using SQL. Sometimes when data have a bit of inconsistency, there's plenty of back and forth to find out what is the issue.

Data in microsoft excel columns

Usually data in excel are in table format, rows and columns, where there are headers to represent the meaning of that particular column. eg. NRIC.

Sometimes we want to fit in that column of data, usually just a small subset to see the output in SQL, we need to pass that few data and fit into the IN operator inside the WHERE statement.

SELECT *
FROM table
WHERE NRIC IN ('S1234567A', 'S2345678B', 'S3456789C')

Here's what I usually do.

  1. Create a new column.
  2. In the new column, specify the following formula ="'"&A2"'"&"," pointing it to the cell (in this case assuming A2 holds the value)
  3. Drag down to apply formula to the original column.
  4. Copy the entire column and paste it onto SQL environment after the IN operator.
  5. Manually add in ( in front and ) at the back of the data inside the SQL environment and also remove the last comma created by the formula.

It doesn't take very long to be honest, but a little annoying after doing it a few times.

During that period of time when I have this issue, I was also tasked to come up with a data dictionary. Basically a glossary to tell the data users, the schema of the views that they are accessing. I'm also practicing HTML CSS during that time, so I thought why not I just build a page to host the data dictionary and also a tool.

So the tool needs to take in range of values via an input box, and spit out the format I want with each data is wrap around single quotes separated by a comma, and the entire thing wrap with parentheses. eg. ('S1234567A', 'S2345678B').

Building the tool

Before I build the tool, I also check in with some colleagues if they had the same issue, even though they said no, I might still build it since I need to get the data dictionary online as well. But I thought if more people have the same problem, then more people can benefit from it.

I used tailwind css for styling, and normal javascript to build it.

Conclusion

Just a few days back, another colleague ask me the same exact thing how to get the data into the IN operator. She wasn't aware of the page I built.

So I decided to take this opportunity to set up my own blog and see if more people can benefit shaving another few seconds avoiding the boring work. At the same time, I will need to rewrite the code in React since this blog is built with NextJS. I will take this opportunity to practice.

Here's a link to the tool. Feel free to share it.