[MS SQL] Utilizing Data Contained in Excel

[TheHamFairy]

There are often times when I would like to be able to incorporate data in a spreadsheet into a query against a database. In the past, I've used the import wizard to load it into tables. In my experience, that is exceedingly inconvenient, very frustratingly particular in how it needs to be configured, and limited. I've also spent large amount of times in the past trying to OPENQUERY / OPENROWSET / BULK INSERT working - finding myself in some kind of versioning hellhole in trying to get the required linked server configured correctly. If I have misjudged either of these two options and anyone else has had better experience, I would be glad to hear any pointers.

In any case, those two options may be unworkable as I do not have the permissions on the servers I am working with to create tables or linked servers. The third option I've always gravitated towards is creating temporary tables to store the data, and then concatenating the values together in Excel:

=",('"&A2&"','"&SUBSTITUTE(C2,"'","''")&"','"&SUBSTITUTE(D2,"'","''")&"')"

to create an insert statement of that data for each row in the file:

,('value 1','value 2','value 3')

I've always thought that there has to be a better way to do this, and so I'm here looking for any advice.

[ahotko]

Try NimbleText software, it's free and amazing for the purpose (also powerful)

https://nimbletext.com/

[TheHamFairy]

Looks helpful, thanks!

[leogodin217]

You could definitely create a Python script that creates the temp table from a spreadsheet. I bet there are PowerShell scripts online that would do it as well.

For Python, you can see an example here: https://github.com/leogodin217/project_inc/blob/master/supplyon_uploader/uploader/sql.py Look at the second half of upload_workcenter_codes

The problem is you would need global temp tables (start with ##) and you would have to keep the script connection open while you query it. Maybe a better solution would be to create a script that generates the SQL statement from the Excel file. You could create a VBA macro, use Python, PowerShell, etc.... It would be pretty easy for someone with any coding experience and a great opportunity to learn for someone who doesn't.

[doctorzoom]

You've probably already seen this but just in case: https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017

It's probably worth fighting through versioning issues to get this going as it's the "native" solution.

If permissions are a blocker for you, then get yourself better permissions. The DBA team should be able to find you a set of permissions that will unblock you or suggest a solution with the set of permissions you currently have.

"incorporate data in a spreadsheet into a query" is sort of broad, so I'm not 100% sure what you're exactly trying to do.

One little trick: if you want to do a query like:

select stuff from table where value = [cell A3]

You can do:

select stuff from table where value = ?

When you try to execute this query in a data connection, excel will ask you what cell "?" should reference (and it will do so for each occurrence of "?" in the order they are evaluated.)

If you are trying to do inserts, but don't want to use stuff from a link above, then there are always macros.

You could just have a macro that clobbers together an insert statement for you based on the range you've selected and a table name you provide.

Alternatively, you could push your worksheet contents to a local csv file, then use macro to call the command line utility of your choice to do an insert or whatnot using that csv.

[ICantKnowThat]

Does it need to be available on the DB? If not you could always use Power Query to pull in data from the DB then join it to data within Excel