Sql-server – lookup the Primary Key in SQL Server from Excel Spreadsheet

excelimportsql serversql-server-2016

Disclaimer: I'm a database novice teaching myself for a research project.

Background: I am querying an electronic medical record (EMR) system for information pertinent to my project and I do not want to store medical record numbers (just say "no" HIPAA violations!). The database is designed with Visit_ID as a Primary Key and four fields which result in a unique combination: admission date & time, length of stay, admitting physician, and room number. All other information will be based on the Visit_ID. For example, the "Medications" table starts with Visit_ID as a Foreign Key and all other information such as start date, stop date, etc.

Problem: The EMR system obviously does not know my Visit_ID. Therefore, all of my queries will have the four fields which are defined by Visit_ID in my database.

Question: Can I resolve or lookup the Visit_ID within the database from the four columns in my Excel sheet?

Example: I query the EMR for the medications the patients had in June. My query returns an Excel spreadsheet with my four fields (Admission Date & Time, Admitting Physician, Length of Stay, and Room) and all the necessary information about the medications. Can I import data from that Excel spreadsheet into my "Medications" table but have SQL Server resolve Visit_ID instead of keeping those four fields?

Best Answer

Yes, Excel can query data from a database. On the Data tab there are tools to create and maintain connections. IIRC it uses ODBC so all major database products can be referenced.

MANUAL ALTERNATIVE

If you have the patience, you could do the following

STEP 01) Create a table using the CSV Storage Engine

When you create the table like this:

CREATE TABLE mydb.mytable
(
    i INT NOT NULL,
    c CHAR(10) NOT NULL,
    ...
    column29 INT NOT NULL,
    column30 CHAR(10) NOT NULL,
    column31 CHAR(30) NOT NULL
) ENGINE=CSV;

This will create two files under the mydb subfolder

mytable.frm
mytable.CSV

CAVEAT : I do not think NULL columns are allowed for MySQL CSV tables

STEP 02)

Using Excel, export the spreadsheet with the same number of columns as the MySQL CSV table. Empty Cells should come out as "".

STEP 03) If using MySQL in Linux, you could do this

cd /var/lib/mysql/mydb
mv mytable.CSV mytable_old.CSV

STEP 04) Copy the Excel output into /var/lib/mysql/mydb/mytable.CSV

STEP 05) In the mysql client, run

FLUSH TABLES;
SELECT * FROM mydb.mytable;

This will close and reopen all tables. If you see your data, CONGRATULATIONS !!!

STEP 06) If you get an Error

If you get an error on SELECT * FROM mydb.mytable;, then one of the cells is not field enclosed with double quotes. Just re-export the data and try Step 04 again until you get it right

For Those using MySQL for Windows

If you have MySQL for Windows, you can drop in mydb\mytable.CSV becauswe of the way Windows locks files. You will have to run net stop mysql, copy the file in, and net start mysql, skip the FLUSH TABLES; and just run SELECT * FROM mydb.mytable;

Give it a Try !!!

Best Answer

Related Solutions

Sql-server – OPENROWSET can’t import an excel spreadsheet into SQL Server 2005 Express

Mysql – Excel Spreadsheet Import to MySQL

MANUAL ALTERNATIVE

For Those using MySQL for Windows

Related Question