--- title: "Map Excel Columns to SQL Fields Manually" slug: "manually-setup-a-mapping-of-excel-columns-to-fields-in-a-sql-server-table" description: "Create a Database Mapping by hand to export Excel columns to SQL Server fields. A Database Import is still needed to load the data in." updated: 2026-11-05T18:05:23Z published: 2026-11-05T18:05:23Z canonical: "sqlspreads.com/manually-setup-a-mapping-of-excel-columns-to-fields-in-a-sql-server-table" --- > ## Documentation Index > Fetch the complete documentation index at: https://sqlspreads.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # Manually setup a mapping of Excel columns to fields in a SQL Server table ## Create a new Database Mapping Note: You will have to also create a Database Import that will load the data from SQL Server into Excel, the Database Mapping only exports the data from Excel to SQL Server. Note: For a quick start, use the SQL Spreads Designer to create a setup and then modify the Database Import and Database Mapping that was created by the Designer. 1. Click the **Database Mapping** button in the SQL Spreads tab in Excel. ![Advanced Setup Database Mapping 7.1](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Advanced%20Setup%20Database%20Mapping%207.1.PNG) Note: To enable the Advanced Setup menu, go to **Document Settings** > **Other Settings** tab and check the **Show Advanced Setup menu** checkbox. 1. This will bring up the **Database Mappings** dialog where you can see your existing Database Mappings. 2. Click **New**. ![New mapping.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/New%20mapping.png) 3. Select the cells that should be exported to SQL Server. If all rows should be exported, just select the header row. ![Select cells.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Select%20cells.png) 4. Then in the drop-down lists, select the **Database** and **Table** where the data should be updated. ![Select database.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Select%20database.png) 5. SQL Spreads will try to automatically map the spreadsheet columns to the database fields. Check that the mapping is fine and click **OK**. ![Check mapping.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Check%20mapping.png) 6. In the **Database Mappings** dialog, click **OK** again. 7. Click **Refresh**. ![Advanced Setup Refresh button 7.1](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Advanced%20Setup%20Refresh%20button%207.1.PNG) 8. Do a few changes to the data in the exported cells and click the **Save** button to write your changes back to SQL Server. 9. Go to SQL Server Management Studio to validate that the data was updated in SQL Server. Note: If your columns in Excel has the same header name as the fields in the database, SQL Spreads will automatically map the spreadsheet columns to the database fields. Note: You can create several Database Mappings in the same Excel document. ## Selecting which cells that should be exported SQL Spreads can either export all rows below the header row, or a specified number of rows. #### To export all rows 1. Select the header row (or the first row of data if you do not have a header row. Then also uncheck the 'First row contains headers' checkbox) ![Select headers.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Select%20headers.png) 2. Check the **Export all rows below selection that contains data** checkbox at the bottom of the Database Mapping dialog: ![Export all rows.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Export%20all%20rows.png) #### To export a specific range of cells 1. Select the range of cells in Excel to export. In this example, three columns in row 5 to 14 will be exported. ![Select range.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Select%20range.png) 2. Uncheck the **Export all rows below selection that contains data** check box ![Uncheck export all.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Uncheck%20export%20all.png) ## Mapping Excel Columns to Database Fields in SQL Server In SQL Spreads you can map exactly which columns in Excel that should be exported to specific fields in the database table. SQL Spreads also has a few built-in application columns that can insert application data into a field in the database table. To map a column in Excel to a field in the SQL Server table, select the Excel column in the drop-down for the database field. ![Map column.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Map%20column.png) Note: If you use a Header row in Excel with the same column names as the fields in the database, SQL Spreads automatically map the Excel columns to the database fields. The Application columns are: #### CreatedBy Will keep the Windows Login name of the user who created the row. #### CreatedOn Will insert the current date and time when the row was created. #### UpdatedBy Will keep the Windows Login name of the user who lastly updated the row. #### UpdatedOn Will insert the current date and time when the row was updated. #### Do Not Use Will not insert any data into the database field Note: If a database field is set to **Do Not Use** and you allow users to add new rows, you must set a **default value** for the field in the database. #### Unmapped Columns If SQL Spreads cannot automatically map the column, it will be set the ***Unmapped Colum*** #### Insert Data From Filter If you have created an Import Data Filter, you can let SQL Spreads insert the selected filter value into a column in the database table. To map a Database Column to a selected filter value, select the filter item in the DropDown List under the section --- Insert Value From Filter ---. ## Use an Excel formula to update the value in a database column There is a feature in SQL Spreads that allows you to use a formula in Excel to calculate the value that should be stored in a column in SQL Server. ![Formula.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Formula.png) The feature is called Formula Columns and will give you a blank column where you can add a formula that calculates a value based on other columns values. Follow these steps to set up a column in SQL Spreads as a Formula Column: 1. Create a setup using the SQL Spreads designer or manually. 2. Go to the Advanced Setup menu, select Database Import and edit the Database Import for your setup. For the column where you would like to add an Excel formula, set the column alias to FormulaColumn. Note that you **must include the AS** keyword when setting the alias: ![ForecastDiff.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/ForecastDiff.png) 3. Go to the Database Mapping and check the “Always update all columns” checkbox at the bottom. This will force SQL Spreads to always update all columns in a row. (When saving to the database, SQL Spreads by default only updates the columns in the database for which the user has changed a value in Excel) ![Always update.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Always%20update.png) 4. Add a formula to the column(s) in Excel. 5. Click the Refresh button in SQL Spreads and check that the formula is preserved in the column and not overwritten by the value from the database. 6. Change a value that will affect the formula, then save to the database and check that the result from the formula is saved to the database. Note: When setting a column alias to FormulaColumn, you must include the AS the keyword. You must also ensure that the cell format for the formula column is set to General. #### Formulas that depends on values outside of the database row If you have formulas that depend on values in cells that are not in the same database row, you can force SQL Spreads to always update all rows by checking the "Update for all rows". In that way, you can have a formula with eg an index that should be applied to all rows. ![Update for all rows.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Update%20for%20all%20rows.png) #### Using several Formula Columns in a row If you have several Formula Columns, set the alias to a FormulaColumn1, FormulaColumn2 etc. ## Enabling Conflict Detection SQL Spreads has a built-in Conflict Detection that will check if a row has been updated by another user since the last time the data was loaded into the spreadsheet. If a conflicting row is detected, SQL Spreads will show a dialog with the actual conflicting rows in the database and in the spreadsheet. The user will have the option to Overwrite or keep the conflicting changes. To enable conflict detection: 1. You will need two columns in the database table; one column to keep the user who updated the row (eg of type nvarchar), and one column to keep the time when the last change was done (eg of type DateType2). 2. Map the two columns to the application columns **UpdatedOn** and **UpdatedBy**. ![Updated on.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Updated%20on.png) 1. Check the **Enable Conflict Detection** checkbox. ![Enable-Conflict-Detection_zpsisevnslz.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Enable-Conflict-Detection_zpsisevnslz.png) 1. In the drop-down list **Spreadsheet Column containing Last Save Date Time**, select the Excel Column that contains the date and time when the row was modified. ![Lastupdated.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Lastupdated%281%29.png) Note: To use conflict detection, you must have one column in your SQL Server table that stores the last time when the row was updated and one column to store which user updated the row. ## Settings #### First row contains headers If checked, SQL Spreads will use the column names in the first row: ![FirstRowContainsHeaders_zpsbayu7y9t.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/FirstRowContainsHeaders_zpsbayu7y9t.png) If unchecked, SQL Spreads will use the Excel column names, like **Column - A, Column - B** etc. ![MapWithExcelColNames_zpswk43c5iw.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/MapWithExcelColNames_zpswk43c5iw.png) #### Export all rows below selection that contains data If selected, SQL Spreads will grab all the rows with data below the selection and export them to the SQL Server table. See the section [Selecting which cells that should be exported](https://sqlspreads.com/docs/manually-setup-a-mapping-of-excel-columns-to-fields-in-a-sql-server-table#selecting-which-cells-that-should-be-exported) above for more details. Note: If you would like to export all rows with data in a sheet, just select the header row, and then check the Export all rows below selection that contains data checkbox. #### Allow Insert New Rows If checked, the user will be allowed to insert new rows in the spreadsheet, and the rows will be inserted in the database table. #### Allow Delete Rows If checked, the user will be allowed to delete rows in the spreadsheet, and the rows will also be deleted in the database table.