Sometimes you may need to pull some excel files into a database, but unable to due to not having an “Import Excel” function in your sql tools, but still having the ability to run Insert and Update Functionalities on a command prompt or stripped down sql tool. The following list is what you’ll have to do to obtain a list of inserts and/or updates.
1) Know the table columns in which are required. In doing so, you can run the following queries. Change “users” to whatever table you desire:
DESCRIBE users; //one way in MySQL
SHOW COLUMNS FROM users; //Another Type in MySQL
WHERE table_name = ‘users’
2) create an excel file with column names in Line 1, explicitly mimicking the columns that can’t be null. You can exclude identity columns that auto fill, but any other columns that can’t be null need to be identified. Nullable columns can be omitted, but if you’d like them to be identified, you can do so. If an end user chooses to keep those columns blank, make sure that the cell(s) in question have at least one space for blank.
3) send off the excel file to end user or enter the data yourself.
4) when you retrieve the file back, make sure you massage single quote (apostrophes) . You can do this by adding another apostrophe. ie O’Riley will become O”Riley. or if the business rules ask to remove them, than just replace the O’Riley to show ORiley. Also search the excel files for any other delimiters that could murky up the code.
5) Once you’ve sanitized your datasets, do go to VBA Macros and add the following code:
VBA Macro (downloadable code here)
6) save the vba macro and go back to the Excel file. go over to one line over from the last column. Lets say you have 5 columns to enter, Column A to E, You will put your cursor on line F2 and enter one of the following:
For Insert you will use the function (change “users for your table name):
=Insert2DB(a2:e2, $a$1:$e$1, “users”)
For Update, you will use the following function(change “users for your table name, assuming column A and B are going to be the where clause):
=Update2DB(c2:e2, $c$1:$e$1, “users”, a2:b2, $a$1:$b$1)
Typically for updates, you will bunch your “where clause columns” together to ensure it’s easier to grab them. the above reads
the columns you will alter will come first in your function. so, if you are inserting columns A-E, you will see that comes first, than the headers follow that (making the static headers columns with a $ function. then set the table name.
insert into users(a1,b1,c1,d1,e1)
The update functionality has same base (Columns you are updating, their headers, the table name) but also adds 2 two parameters. the last two sections are for the columns field values and headers for the where functionality. so it will turn into
where a1=a2, b1=b2
Assuming the a1-e1 will be the static column names , and a2-e2 will be dynamically pulled in.
Once you complete this functionality as seen above, you will hit enter (on the column you are entering it), and you will see it will automatically turn it into an Insert or Update pending on the function you call. When you see the query dynamically created, you can do a copy paste for all following rows with a drag/drop or copy paste in all columns you desire to copy into sql. This can dynamically pull hundreds or thousands of records instantly and easily run them quickly with ease.