How to import data from Google Sheets to SQL Server

Google Sheets is the world’s most popular free collaborative spreadsheet application.

To import data from Google Sheets:

1. In Object Explorer, right-click a database, point to Data Pump, and then click Import Data.

2. On the Source file page, select the Google Sheets import format. Load import options from a template file in User Templates if you saved it previously. Specify the ID/URL of your Google Spreadsheet and click Next.

Source file

Note

After you insert the required URL and click Next, Data Pump opens the browser for you to select the required Google account and allow dbForge to access your Google spreadsheets. If you do so, you will receive an email confirming that dbForge was granted access to your linked Google account.

3. On the Destination page, select a server connection, a database and its schema. Then select whether the data will be imported into a New table (in this case, you need to specify its name) or into an Existing table. Click Next.

Destination

4. On the Options page, you can configure the formatting options for imported data:

  • If the source data is located on several sheets, specify which of them to choose for import in the Data Location section.

  • In the Data Location section, you can also specify the data range to be imported. You can see it highlighted with green in the Preview section. If you want to import data partially, clear the Detect a range automatically check box and enter the Start cell and the End cell values or click the button next to these fields and double-click the required cells in Preview. The specified range will be highlighted with green, and the skipped lines will be highlighted with white.

  • You can configure the header in the Header section. You can select either No header, or First row/column in a range, or Position. In the latter option, you can specify the header position in the required cell. If you don’t specify the header position, the imported columns will get default names - column1, column2, etc. The header will be highlighted with blue in Preview.

Options

5. On the Data formats page, you have two auxiliary tabs. The first one is Common Formats, where you can specify the formats for null strings, thousand and decimal separators, boolean variables, date and time. There is also a check box Autodetect Date and Time format, selected by default.

The second one is Column Settings, where you can configure the format settings for separate columns. You have 4 options here: Null String, Left Quote, Right Quote, and Date and Time. Note that if a format mask is not set, the application will identify date/time values automatically.

6. On the Mapping page, you can map the source columns to the target ones. If you are importing data into a new table, the application will automatically create and map all the columns. You can click Edit to specify the column names and the corresponding data types. The results are shown in the Preview section.

Mapping

Note

If you are importing into an existing table, only columns with the same names will be mapped, and the rest should be mapped manually. If no columns with the same name are found, they are mapped in succession - the 1st column in Source with the 1st column in Target, etc.

You can click View on the toolbar to view column properties.

Finally, to clear the mapping of all columns, click Clear Mappings on the toolbar. To restore it, click Fill Mapping.

7. On the Modes page, select an import mode. There are 5 available modes:

  • Append - add records to the target table
  • Update - update a record in the target table with a matching record from the source
  • Append/Update - update a record if it exists in the target table; otherwise, add a record
  • Delete - delete records in the target table that match records in the source
  • Repopulate - delete all records in the target table and repopulate them from the source

Optionally, you can select the check boxes Use a single transaction and Use bulk insert (the latter of which reduces the quantity of statements and speeds up import, but can affect the error handling mechanism).

8. On the Output page, you have 3 options:

  • Open the data import script in the internal editor.
  • Save the data import script to a file. To use this option, specify the path and the file name. Additionally, you can select the check boxes to Add timestamp to the file name and Open in the internal editor.
  • Import data directly to the database.

9. On the Errors handling page, you can specify the error processing behavior (using one of the three available options: Prompt the user for an action, Ignore all errors, or Abort at the first error) and opt to write reports to a log file with a specified path.

Note

If you want to save your import settings as templates for recurring scenarios, click Save Template.

10. Click Import. When your data import is successfully completed, you can view the log file or click Finish to exit the wizard.