Google Sheets is the most popular free collaborative spreadsheet application in the world. It allows users to work together in real-time, making it a convenient tool for many businesses and individuals. If you’re looking to import data from Google Sheets into SQL Server, you’re in luck! In this article, we’ll guide you through the process step by step. So let’s get started!
Step 1: Launch the Data Pump Wizard
To begin the data import process, open the Object Explorer in SQL Server Management Studio. Right-click on the desired database, hover over Data Pump, and click on Import Data.
Step 2: Select the Google Sheets Source
In the Source File page of the wizard, select the Google Sheets import format. If you have saved import options as a template, you can load them from the User Templates section. Specify the ID/URL of your Google Sheets document, and click Next.
Note: After entering the required URL and clicking Next, the Data Pump wizard will open your browser, prompting you to select the necessary Google account and authorize dbForge to access your Google Sheets. You will receive an email confirmation once dbForge has obtained access to your linked Google account.
Step 3: Choose the Destination
On the Destination page, select the server connection, database, and schema. Then, choose whether the data will be imported into a New Table (specify the table name) or an Existing Table. Click Next to proceed.
Step 4: Configure Import Options
On the Options page, you can configure the formatting options for the imported data. If the source data is spread across multiple sheets, specify the ones to be imported in the Data Location section. You can also specify the data range to be imported by unchecking the Automatically detect range option and entering the start and end cell values or by selecting the required cells in the Preview section. This specified range will be highlighted in green, while the ignored rows will be highlighted in white.
You can also set the header options in the Header section. Choose between No Header, First row/column in range, or Position. If you select Position, you can specify the header’s position in the required cell. If no header position is specified, the imported columns will have default names like “column1,” “column2,” etc. The header will be highlighted in blue in the Preview section.
Step 5: Data Formats
The Data Formats page allows you to specify common formats for empty strings, thousands and decimal separators, boolean variables, dates, and times. By default, the Automatically detect date and time format option is selected.
The second tab, Column Settings, allows you to configure format settings for specific columns. Here, you have four options: Null String, Left Quote, Right Quote, and Date and Time. Note that if no format mask is defined, the application will automatically identify date/time values.
Step 6: Column Mapping
On the Mapping page, you can map the source columns to the target columns. If you’re importing data into a new table, all columns will be created and mapped automatically. Click Edit to specify column names and corresponding data types. The results will be displayed in the Preview section.
Note: If you’re importing into an existing table, only columns with the same name will be mapped, and the rest will need to be mapped manually. If no matching column names are found, they will be mapped sequentially – the first column from the source with the first column from the target, and so on.
You can click Show on the toolbar to view column properties. To clear the mapping of all columns, click Clear Mappings. To restore the mapping, click Fill Mappings.
Step 7: Select the Import Mode
On the Modes page, select an import mode. There are five available modes:
- Add: Adds records to the target table.
- Update: Updates a target table record with a corresponding record from the source.
- Add/Update: Updates a record if it exists in the target table; otherwise, it adds a new record.
- Delete: Deletes records in the target table that match the records from the source.
- Repopulate: Deletes all records in the target table and repopulates it from the source.
Additionally, you can check the boxes for Use Single Transaction and Use Bulk Insert. The latter reduces the number of instructions and speeds up the import process but may impact error handling.
Step 8: Choose the Output
On the Output page, you have three options:
- Open the data import script in the internal editor.
- Save the data import script to a file. Specify the file path and name. You can also check the boxes to Append a Timestamp to the File Name and Open in Internal Editor.
- Import the data directly into the database.
Step 9: Error Handling
On the Error Handling page, you can specify how errors should be handled. Choose from three options: Prompt the User for Action, Ignore All Errors, or Abort on First Error. You can also choose to save the 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.
Step 10: Import Data
Click Import to start the data import process. Once the import is successfully completed, you can review the log file or click Finish to exit the wizard.
That’s it! You have now successfully imported data from Google Sheets to SQL Server. If you want to learn more about data import and access other tutorials, visit Crawlan.com.
Remember, with Google Sheets and SQL Server, the possibilities are endless. Happy importing!
