2014 has been the most awesome year since starting Chandoo.org
Thank you so much for making it happen. This year, more than 10 million people visited our site, learned something and took first step to become awesome in their work. Each & every day of 2014 felt invigorating, exciting & blessed. I could meet 100s of you face to face during my travels to Houston & Dallas in September this year.
A big thanks to you, my reader, for supporting me and choosing chandoo.org as partner in your journey to awesomeness.
Apart from our readers, there are also countless people, websites, books, companies which helped me have a fantastic year. This message is my way of saying thanks to them.
People who helped me in 2014
Teachers & gurus:
Running a small business focusing on knowledge requires a lot of training, help and mentoring. Thanks to all these wonderful people who motivated me, taught me, inspired me and corrected me in this year.
Excel folks:
Hui, Jon Peltier, Debra Dalgleish, Mike Alexander, Dick Kuslieka, Rob Collie, Bill Jelen, Jordon Goldmeir, Colin Legg, Mike Girvin, Francis Hayes, & David Hager
Forum members:
SirJB7, Hui, Luke, Narayank, Bobhc, Debraj, Faseeh, Sajan, Shrivallabha, Kaushik and more.
Business & entrepreneurship:
Pat Flynn, MJ Demarco, MrMoneyMustache, Patrick & Amy Porterfield
Health & fitness:
adc, Thomas Andersen & Tim Ferris
Authors & books
This year has been incredibly satisfying in-terms of reading books. I read quite a books and learned so much.
Some of the authors & their books that inspired me are,
- Andre Agassi – Open
- Sachin Tendulkar – Playing it my way
- Tim Ferris – 4 Hour body
- John Foreman – Data Smart
- Mike Girvin – Ctrl + Shift + Enter
- Steven Levitt & Stephen Dubner – Think like a freak
- William Dalrymple – In Xanadu
- Wayne Winston – Data analysis & business modeling
Note: all the books are Amazon Affiliate links. That means, if you click and purchase a book thru above links, I will get a few cents from Amazon.
Partners, Affiliates & Supporters
Chandoo.org is able to stand tall & help millions of users world wide because it stands on the shoulders of many giants & supporters.
Our partners:
BizNet Software: An email from Hanna (who works at BizNet Software) started it all. I am very thankful to staff at BizNet (especially Lori, Hanna, Eric & George) for inviting me to Excelapalooza 2014 conference in Dallas. It was a very memorable event in 2014 and I really enjoyed spending time with all of you & your users. Thank you.
Jocelyn & Robert Collie: Thanks for helping me with ground logistics for running Advanced Excel & Power Pivot Masterclass in Houston
PASS BA & Jen Stirrup: Thank you so much for inviting me to speak at PASS BA 2015 conference. I am super excited to be part of this prestigious conference and eagerly looking forward to it. Know more & signup.
Plum Solutions: Thank you Danielle for doing all the ground work to conduct another round of Excel & Power Pivot masterclasses in Australia in 2015. Know more & signup here.
EduPristine: Thanks to Pawan, Paramdeep & rest of the staff at EduPristine for partnering with Chandoo.org in running Financial Modeling classes.
Our Affiliates:
This year was great for many of our affiliates too. Thanks to their support, we had more customers and they had more revenues. Some of our most prominent affiliates are,
Dashboard Spy, Francis, Daniel Ferry, Debra Dalgleish, Philip, Ken Puls, Oscar, Jimmy Pena, Victor Chan, Alan Murray, Brad Edgar and many more.
Customers & Readers
In 2014, more than 6,250 people purchased courses, templates, ebooks or products from Chandoo.org. More than 100,000 people are now members of our newsletter / RSS feed. More than 8,000 people regularly tune in to Chandoo.org podcast too. Many more people discover and join our little community every day. Thank you so much for inviting me to your life & letting me help you. My sincere & heart-felt thanks to each and every one of you.
Many thanks to Novartis India for hiring me as their Excel trainer. Special thanks to KONE Cranes, Canon, Xerox and AtlasAir for purchasing team licenses of our training programs.
I am also thankful to our Excel Forum members, who continue to share their knowledge & skills selflessly.
Special thanks also to,
- Delegates of my Advanced Excel & Power Pivot Masterclass in Houston, USA
- Attendees of my sessions in Excelapalooza 2014 in Dallas, USA
- Shon, Jim & Brett from Lloyd’s Register for sharing awesome video testimonials
- Rosalyn & Kari from Amaxra for sharing a cup of coffee & interesting discussions while I was in Houston
- All our podcast listeners
- Everyone who bought a copy of The VLOOKUP Book thru Amazon
- David Hager, for treating me to a pizza & sharing many Excel insights while I was in Houston.
- Rick, Oz & Jordan for inviting me to be a part of Excel TV interview.
Our staff
Chandoo.org staff are the silent soldiers helping me achieve our mission – “to make you awesome in Excel”. I am deeply thankful to their efforts & work ethic.
- Ravindra: for handling student enrollments, customer support & email work
- Joya: for creating podcast transcripts
- Pothi: for maintaining Chandoo.org webservers and helping our site run smoothly
- Narayan: for answering student questions & doubts
- Chittibadrayya: for taking care of our accounting & financial reporting stuff
- Vijay: for teaching VBA and answering student questions
- Jo (my wife): for helping me with customer support emails.
Companies & websites that helped me
I am thankful to Microsoft for creating Excel and helping me make a living out of it.
I am also thankful to,
Email & Productivity: Google, iPhone
Website, Hosting & E-commerce: WordPress, GoDaddy, Wishlist Member, KnownHost, Amazon, PayPal, E-Junkie,2Checkout, EBS, GumRoad, FastSpring, Thesis, libsyn
Community & Connection: Twitter, Facebook, Youtube, Skydrive, pinterest
Software: Paint.NET, Mozy, Notepad ++, Camtasia & Snagit, Skype, Rescue Time, Audacity
Apps: Flipboard, Feedly, Amazon Kindle
There are many other software, companies and websites that help me every day. I am really thankful to each and every one of these. Detailed listing here.
Last but not least…
I am able to perform at my best levels & help you because there is someone else that support, encourage and inspire me every day.
- My family: Jo & kids support me and Chandoo.org in numerous ways. They shower me with love, humor and support everyday so that I can be awesome at what I do.
- All my close friends & relatives: for supporting me & encouraging me to do better.
PS… something for you:
Here is a nice little surprise for you. Open a new Excel file & in A1 type
=SUBSTITUTE(LEFT(ADDRESS(2^3^2, SUMPRODUCT(MID(REPLACE(REPT("10",6), 7,2,10^2),
ROW($Z$1:$Z$13),1)+0,2^(13-ROW($Z$1:$Z$13))),BIN2DEC("100"),1),3)
& SUBSTITUTE("PIE",LEFT(ADDRESS(10^2, HEX2DEC("EF"),(7-3),1),2),"Y N")
& ADDRESS(11^2+2^2+2+1,20*20*10+2*1*1+1,2^2)
& SUBSTITUTE(SUBSTITUTE("BEARD",DEC2HEX(REPT(1,2)),""),"D",""),"Y128", " Y")
41 Responses to “SQL Queries from Excel”
I use this method very often.
I always use =SUBSTITUTE (ColumnWithText,"'","''")
to be sure that potential apostrophe in text columns are doubled as required in SQL.
Awesome ! I don't use excel very often so the substitute thing is gold to me 🙂 thanks !
@Leonid.. that is a good technique to use substitute to clean up text apostrophes. thanks
Goal:
Generate update statement in excel where the columns that can be updated are dynamic
You want the columns which are not updated to keep the same value
(or not be overwritten with NULL values with the new generated statement)
the statement can be applied to multiple rows in excel for the same column headers
(This is why the '$' exist for the column headers that are being set)
A1 = First_Name
B1 = Last_Name
C1 = Middle_Name
="
UPDATE PERSONS "&CHAR(10)&
" SET 1 = 1 "&CHAR(10)&
IF(LEN(TRIM($A2))=0,"",", "&$A$1&" = '"&$A2&"'"&CHAR(10))&
IF(LEN(TRIM($B2))=0,"",", "&$B$1&" = '"&$B2&"'"&CHAR(10))&
IF(LEN(TRIM($C2))=0,"",", "&$C$1&" = '"&$C2&"'"&CHAR(10))&
" WHERE name = 'staticordynamicvalue' AND gender = 'staticordynamicvalue'
"
Output (if all columns are set):
UPDATE PERSONS SET 1 = 1,
First_Name = 'Joe',
Last_Name = 'ORien',
Middle_Name = 'Richard'
WHERE age = 28 AND gender = 'm'
Output (if only First _Name (A1) is set):
UPDATE PERSONS SET 1 = 1,
First_Name = 'Joe'
WHERE age = 28 AND gender = 'm'
Possibly my post above is confusing without the actual table to look at. I will do the same example with the table used here. Instead of an insert statement I will generate an update statement for the columns, Cust_Name, Phone & E-mail
where we can generate an update statement for any column individually or together. 🙂 I hope this can help.
=”
UPDATE table “&CHAR(10)&
” SET 1 = 1 “&CHAR(10)&
IF(LEN(TRIM($A2))=0,”",”,Cust_Name = ‘”&$B3&”‘”&CHAR(10))&
IF(LEN(TRIM($B2))=0,”",”, Phone = ‘”&$C3&”‘”&CHAR(10))&
IF(LEN(TRIM($C2))=0,”",”, E-mail = ‘”&$D3&”‘”&CHAR(10))&
” WHERE Cust_Name = ’Bill Gates'
”
Thanks, it has been very useful !
It saved me at least 30 minutes, and time is the most expensive thing in our world...
Hey Paul,
What if any of A2, B2, or C2 is a date field?
The formula above is taking date as string. Any solution?
Even I faced the same problem. If any of the above columns are date, it is taken as string. Any work around for this?
I've found the string concatenation method works well.
At the risk of sounding spammy I would mention that
if it's something your are doing regularly it might be worth investigating a tools
that make it easier, such as QueryCell, an excel add-in I've developed.
It gives you a right click menu option that will produce and then customize insert statements for the selected region of Excel data.
Cheers
Sam
Hi,
For inserting the excel data to your SQL table, you can create insert statements in excel file according to your columns.
then just execute the statements all at once, it will insert the required data to sql server table.
thanks,
How...?
I tried to generate t-sql insert queries from the above example
="insert into values('" &A2 &"','" & B2& "');"
but it generates on one record instead of all records from excel sheet.
I'm using Excel 2003 and the excel sheet contains 922 records.
Most data bases can generate DDL for any object but not a lot of them allow generation of INSERT statements for the table data.
The workaround is to make use of ETL Tools for transferring data across servers. However, there exists a need to generate INSERT statements from the tables for porting data.
Simplest example is when small or large amount of data needs to be taken out on a removable storage media and copied to a remote location, INSERT..VALUES statements come handy.
There is a number of scripts available to perform this data transformation task. The problem with those scripts that all of them database specific and they do not work with textiles
Advanced ETL processor can generate Insert scripts from any data source including text files
http://www.dbsoftlab.com/generating-insert-statements.html
Super Aiticle. Thanks for this post.
I used to deal with the same problem, until found this awsome and free tool.
http://www.xtrategics.com/shapp/String%20Handler.application
regards,
Hi ,
i need a sql query to update a DB in excel 2010..
i have the query(SQL) for insert in excel as ,
="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');"
similarly i need q sql query for update in excel
i want clear formulas only for insert,delete,update,select
Hi !
I would like to thank you so much ! This trick saves me a lot of time. Thank you so much. Really appreciate it !
-Ankit
You may like to take advantage of this unique tool 'Excel to Database'.
(free for 60 days)http://leansoftware.net The Excel-to-Database utility enables you to validate and transfer data from Microsoft Excel or text file to a database table or stored procedure process. Any text data can be pasted into the application, this may be from another Excel sheet or from text files such as CSV format. SQL Server, Access, MySQL, FoxPro .. Application features Some unique features of Excel to Database include: ?Easy to use color coded/traffic light data validation ?Data is validated as soon it is typed or pasted into Excel ?Upload Excel data to a table or stored procedure process ?Allow default values ?Mandatory/must have fields can be specified ?Allow user friendly column names ?Allow excel formula / calculated fields ?Multiple database type support: Microsoft SQL Server, Access, MySQL and others (to be tested) ?Supports Custom SQL scripts, with SQL/Excel merge fields ?Database validation checks ensure you comply with any rules defined within the database ?Multiple Task configuration ?For co-operative use, Tasks can be shared across a network ?Task configuration is password protected http://leansoftware.net
Its works fine for single record.
I want to update 1000 records in DB. Can you help me.
[...] [...]
Excel database tasks 2.3 (EDT)
you can now load directly from any source into Excel, validate and upload to most SQL database platforms including SQL Server with automatic transaction wrapping.
You can also use EDT as a multi-user application by easily designing your own Edit data tasks and deploying EDT on your users workstations.
Automatically creates UPDATE/INSERT statements based on the primary key. Default SQL can be modified as you require.
Makes the best use if Excel power - formatting, formula, validation, conditional formatting.. without creating any problematic spreadsheets!
Release details on the blog:
http://leansoftware.net/forum/en-us/blog.aspx
Thanks for the interest
Richard
Thanks for the valueable information, it really help me alot.
Thanks again.
As I do with a field of type date?
= "UPDATE SET business datetime =" & "'" & A2 & "' WHERE ID =" & B2 & ""
the date is not 03/10/2012 is 41246. Even putting quotes ...
Please show how to do it properly with dates as well as when those dates are empty. Thanks!
In a separate column make the date to Text using below formula
=TEXT(C2,"mm/dd/yyyy") Then Refer this text column in your update statement
Great post saved me a a load of time on a task i had to complete
thanks for sharing article... helpful!
Thanks 🙂
Hello,
Nice article.
I have also created one tool for create table script using excel http://devssolution.com/create-table-in-sql-using-excel/
Please check it.
Thanks & Regards,
Sandeep Bhadauriya
[…] Excel formula used – http://chandoo.org/wp/2008/09/22/sql-insert-update-statements-from-csv-files/ […]
If any one can help me out with following.
I want to know a SQL query of below excel formula:
=LOOKUP(0,-SEARCH(LEFT(F2,LEN($B$2:$B$100))+0,$B$2:$B$100),$A$2:$A$100)
Excel data is as below;
Name Codes
names1 992
names2 57
names3 856
names4 297
names5 63
if there is a number (29756789) then it should search in sql by taking the prefix of number (297) from (29756789) and return the name field (name4).
Codes can be of two digit or three.
Thanks
Here is a link to an Online automator to convert CSV files to SQL Insert Into statements:
CSV-to-SQL: http://csv-to-sql.herokuapp.com
http://stackoverflow.com/questions/1570387/how-to-insert-data-from-an-excel-sheet-into-a-database-table/37409790#37409790
="INSERT INTO table VALUES (" &A3 &",'" & B3 & "','"&C3&"','" & D3 & "','" & E3 & "'," & F3 & "," & G3 & "," & H3 & ",'" & I3 & "'," & J3 & ");"
B3 has date data that looks like 9/22/17 but with the formula above b3 is coming out as 43000?
how do i fix that?
I just want to insert the Excel records in Sql table without Visiting SQL.
basically i m just want to run a command in Excel Only.
Help Me..plz..?
Hi I have a question maybe you guys have an answer for me
="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');" where B3, C3, D3 refer to above table data.
the above technique works but is there a way to write it so it takes a range instead of individual columns. because I have an extremely wide table
="insert into customers values(B3:D3);" where B3, C3, D3 refer to above table data.
Awsome
Its Great Effort to help everyone who working with excel.
Thanks for the mini-tutorial on SQL from Excel. Didi it several years ago, but couldn't remember the syntax! All the dialogue was really helpful as well!
The formula above is taking date as string. Any solution?