How to import any Google Sheets or Excel to SQL using formulas

We will also talk about the problems that you should anticipate when writing such a formula. Here’s a basic Excel table with some sample data.

We want to generate an INSERT query for each row. In a new column, let’s write this Excel formula. By the way, this article is available as a YouTube video, you can check it out:

We start writing a normal SQL query as a formula. Put the equals symbol at the beginning. The static parts of the query must be in string, we enclose them in double quotes.

Dynamic values are cell references and we combine them with strings using the ampersand character. So if you want to insert a dynamic value, you put a double quote at the end of the string part, then an ampersand, then the cell address or function, then an ampersand and double quotes again.

We don't write the ID column because it's usually autocomplete and the database will assign the number itself. But if you need a specific number, feel free to specify it.

="INSERT INTO people (id, name, age, city) VALUES (" & A2 & ", '" & B2 & "', " & C2 & ", '" & D2 & "');"

Let’s break it down. A2, B2, C2, D2 are our data columns. We're wrapping string values like name and city in single quotes. The formula builds a full SQL INSERT statement. After dragging the formula down, here’s what we get:

INSERT INTO people (id, name, age, city) VALUES (1, 'John', 28, 'New York');
INSERT INTO people (id, name, age, city) VALUES (2, 'Alice', 34, 'Chicago');
INSERT INTO people (id, name, age, city) VALUES (3, 'Michael', 25, 'Los Angeles');

Just like that — we have our SQL ready to go! Now copy the generated queries from Excel and Paste them into your SQL editor (like MySQL Workbench, phpMyAdmin, or DBeaver).

Run the script and you're done! This method works with any kind of table structure — just update the formula with your actual column names and cell references.

But there are a few pitfalls that can complicate your life, because SQL requires a certain query syntax, and we are inserting unprepared data into the formula that may not have been intended to be inserted into SQL.

I use this method quite often, so I hope I have mentioned all possible variations, but if you know more - feel free to post in the comments. Let's look at some examples.

Single quotes

If your text contains single quotes ', you’ll need to escape them.

In Excel, you can do this using SUBSTITUTE function:

="INSERT INTO people (id, name, age, city) VALUES (" & A2 & ", '" & SUBSTITUTE(B2,"'","''") & "', " & C2 & ", '" & SUBSTITUTE(D2,"'","''") & "');"

This replaces one single quote with two single quotes, which is the proper way to escape single quotes in SQL.

Note that only one quote will be saved to the database instead of the two specified quotes.

Decimals

Here’s another thing to watch out for — decimal separators. In some countries, Excel uses commas for decimal numbers instead of dots.

But in SQL, decimal numbers must use a dot, not a comma. Otherwise, you will get an error when executing the query.

You can avoid this by wrapping the number in single quotes so that it is treated as a string and converted to a decimal when the query is executed.

But the problem is that in a string, SQL usually doesn't understand that a comma is a decimal separator, so it will take only the integer before comma and turn the part after the comma into 0.

To fix this, you can use the SUBSTITUTE function. Replace commas with dots using this function.

="INSERT INTO products (id, price) VALUES (" & A2 & ", " & SUBSTITUTE(TEXT(B2, "0.00"), ",", ".") & ");"

Then we will get correct decimals values that SQL can understand, even if they are passed as strings. This ensures your numbers are formatted correctly for SQL.

Empty cells

However, the idea of putting a number in quotes is not so bad, because if you can't guarantee that in a large table some row in some table will have a blank instead of a zero - it will cause an error in the query. So just enclose all cells, even numbers, in quotes.

Date format

Excel might store dates in different formats. SQL requires a special date format where first comes the year, dash, month with a leading zero, dash and day with a leading zero - YYYY-MM-DD. We are lucky to have a special function to format the date into this format. So convert it explicitly:

=TEXT(D2, "yyyy-mm-dd")

Spaces

Trailing or leading spaces can break uniqueness or cause unexpected behavior. Use a trim function to avoid this.

=TRIM(B2)

Formatted tables

In the formulas above, we refer to cells by their addresses, but this can be a problem if the table structure has changed and you are using an old formula. If you are working with Excel, you can use the "smart" or "formatted" table feature - this is a separate object on an Excel sheet that adds advanced features. 

One of the features we need is the ability to refer to a cell in the same row not by cell number, but by column name. This way, if you download the file in a few months time and copy the old formula, if the structure of the file changes - you will immediately notice it because of an error in the formula.

Press Ctrl T to create a formatted table. Make sure that "My table has headers" is selected.

This is to ensure that Excel automatically recognizes your table's header row and does not add a new one above it. Click OK and retrieve the table.

Then replace the cell addresses in the formula with the column names in a special format. You can do this by simply clicking on the cells.

After editing the formula in one row, it will apply to the whole column of the table, which is sometimes useful.

UPDATE, DELETE

You can also generate UPDATE or DELETE statements:

="UPDATE people SET name='" & B2 & "', age=" & C2 & ", city='" & D2 & "' WHERE id=" & A2 & ";"

="DELETE FROM people WHERE id=" & A2 & ";"

Batch INSERT

If you're inserting many rows, it’s often better to use a single batch INSERT instead of multiple single-row statements. Here’s how you can do it in Excel. First, generate only the values part of each row:

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

Then, in a separate cell (or at the top), build the full query by combining the header and the concatenated values:

="INSERT INTO people (id, name, age, city) VALUES " & TEXTJOIN(", ", TRUE, F2:F100) & ";"

Replace F2:F100 with the actual range of your generated values. This will produce:

INSERT INTO people (id, name, age, city) VALUES 
(1, 'John', 28, 'New York'), 
(2, 'Alice', 34, 'Chicago'), 
(3, 'Michael', 25, 'Los Angeles');

It's clean, fast, and great for large datasets.

Word wrap and quotes

If you apply all of the above tips, you will probably notice that the formula has become very large and hard to read. The good news is that we can format the formula by breaking it into multiple rows.

The bad news is that when we copy the resulting query from an Excel cell that contains line breaks, the query is often enclosed in double quotes around the edges, which causes it to be treated as a string and results in an error.

The other good news, however, is that we can remove the line breaks in the entire formula and get a nice readable formula and, when copied, a stripped down version without line breaks.

To do this, we wrap the formula in the SUBSTITUTE function, replacing line breaks, whose character we get with the CHAR function with a character code value of ten, with regular spaces.

Great:

Of course, if you are constantly receiving and saving the same data in a structured format every day, it is more correct to write a script to validate, prepare and save this data automatically. But I think there are cases where you have a spreadsheet in Excel or Google Sheets that may never be updated again. Or it will be very soon. But you need to quickly import it into the database while transforming some data in the cells.

That’s it! Now you know how to generate dynamic SQL queries directly from Excel and Google Sheets. This is a powerful technique for quickly importing or manipulating data. 

If you found this video helpful, please give it a like, and don’t forget to subscribe for more tech tips like this! See you next time!