Sql spreads

Author: v | 2025-04-24

Use Excel to Update Data in SQL Server; COMPARE SQL SPREADS. SQL Spreads vs Power BI; SQL Spreads vs Management Studio; SQL Spreads vs Custom web Getting started with SQL Spreads Getting up and running with SQL Spreads. An Intro to the SQL Spreads Data Management solution. Is SQL Spreads the right choice for me? In this video, we will walk you through how to: Evaluating SQL Spreads using the free 14-days trial; How to evaluate the Basic, Pro and Premium plans in SQL Spreads

SQL Spreads release - SQL Spreads

Product KEY FEATURESExcel to SQL Server Write-backExcel users can swiftly update and write-back data to SQL Server.Filter huge data setsImport large-scale data sets into Excel, even from tables with millions of rows.Foreign Key LookupsKey value lookups enable easy and integrity-focused relational data management for users.Explore all features Menu ItemInvolve Data OwnersDistinct Designer and Editor roles to protect your setup.Automatic Data ValidationValidate Excel entries against SQL Server data types to stop unsafe inputs.Track all changesKeep track of changes in the database – see who changed a row, and when.Explore all features USE CASES Data Management for BI projectsFinancial Forecasting and planningData Cleansing and TranslationUse Excel to Update Data in SQL Server COMPARE SQL SPREADS SQL Spreads vs Power BISQL Spreads vs Management StudioSQL Spreads vs Custom web appSQL Spreads vs Power AppsSQL Spreads vs Power Query CUSTOMERS CustomersTestimonialsUse Cases SUPPORT KnowledgebaseVideo LibraryContact support Resources VIDEOS An introduction to SQL Spreads in 2 minutes v How to get up and running with SQL Spreads quickly Explore all videos SQL Spreads Financial Forecasting 2024 DOCS Getting StartedInstallation and DeploymentSQL Spreads Security OUR BLOG How to Insert Excel to SQL ServerUpdate a SQL table from ExcelExcel as a Front-end for SQL Server GUIDES Centralize data in SQL Server from Excel PricingCompanyABOUTCAREERSCONTACTTry for freeBuy now Written by Johannes Åkesson Reviewed by Johannes Åkesson Founder of SQL Spreads Johannes Åkesson With over 15 years in the BI industry and a passion for helping clients find efficient, sustainable ways to manage SQL Server data from Excel, SQL Spreads founder Johannes Åkesson also shares his expertise here to empower and educate. Love Excel? Need to update SQL Server data? Then check out SQL Spreads, it’s the product my company has created to use Excel as a front-end to update and manage data in Microsoft SQL Server. Learn more Download the free 14-day trial for the SQL Spreads Excel Add-in if you want to easily let your non-technical users update and manage SQL Server data from Excel. SQL Spreads 4.2.5 is now available for download.Fixed issue with working with SQL Spreads when the Excel document is in Read-Only mode.Fix issue with cursor not returning from wait state after loading document.Minor other fixes and stability improvements.Download the latest version of SQL Spreads » Related Articles Try SQL Spreads for free Try SQL Spreads during 14 days for free. The free trial is the fully functional, time-limited only product.

SQL Spreads release 7.5.6 - SQL Spreads

Product KEY FEATURESExcel to SQL Server Write-backExcel users can swiftly update and write-back data to SQL Server.Filter huge data setsImport large-scale data sets into Excel, even from tables with millions of rows.Foreign Key LookupsKey value lookups enable easy and integrity-focused relational data management for users.Explore all features Menu ItemInvolve Data OwnersDistinct Designer and Editor roles to protect your setup.Automatic Data ValidationValidate Excel entries against SQL Server data types to stop unsafe inputs.Track all changesKeep track of changes in the database – see who changed a row, and when.Explore all features USE CASES Data Management for BI projectsFinancial Forecasting and planningData Cleansing and TranslationUse Excel to Update Data in SQL Server COMPARE SQL SPREADS SQL Spreads vs Power BISQL Spreads vs Management StudioSQL Spreads vs Custom web appSQL Spreads vs Power AppsSQL Spreads vs Power Query CUSTOMERS CustomersTestimonialsUse Cases SUPPORT KnowledgebaseVideo LibraryContact support Resources VIDEOS An introduction to SQL Spreads in 2 minutes v How to get up and running with SQL Spreads quickly Explore all videos SQL Spreads Financial Forecasting 2024 DOCS Getting StartedInstallation and DeploymentSQL Spreads Security OUR BLOG How to Insert Excel to SQL ServerUpdate a SQL table from ExcelExcel as a Front-end for SQL Server GUIDES Centralize data in SQL Server from Excel PricingCompanyABOUTCAREERSCONTACTTry for freeBuy now Written by Johannes Åkesson Reviewed by Johannes Åkesson Founder of SQL Spreads Johannes Åkesson With over 15 years in the BI industry and a passion for helping clients find efficient, sustainable ways to manage SQL Server data from Excel, SQL Spreads founder Johannes Åkesson also shares his expertise here to empower and educate. Love Excel? Need to update SQL Server data? Then check out SQL Spreads, it’s the product my company has created to use Excel as a front-end to update and manage data in Microsoft SQL Server. Learn more Download the free 14-day trial for the SQL Spreads Excel Add-in if you want to easily let your non-technical users update and manage SQL Server data from Excel. SQL Spreads 4.4.0 is now available for download.Added new buttons to the SQL Spreads tab in Excel for saving and refreshing the data between Excel and SQL Server.Added new change tracking setting to separately track database row creation with date, time and Windows user name.Added a release license feature for transferring a license from within the application.Added a warning with instructions if the current machine settings require to add SQL Spreads/Obnex Technologies as a trusted publisher.Added a tool tip effect in the Designer to show SQL Server table column details, such as data types etc by hovering over the “Show details” link in the Columns tab in the SQL Spreads Designer.Improved the SQL Spreads Designer to now preserve Excel table formatting to keep e.g. Header styling, Conditional Formatting, Slicers etc.Improved the User/Filter panel on the left side will now remember the width of the panel per document and also remembers if it is shown or hidden per document.Improved the layout of the User/Filter panel on the left side will auto-adjust when made

SQL Spreads release 7.5.5 - SQL Spreads

Duplicate rows are entered into the import sheet. Click OK and you should see a confirmation that the table was created in SQL Server: Click the Design mode button twice to close and reopen the Design mode. This will reload the tables and you should see your new table in the table list in the Designer on the right side. Select your table in the list and it will be loaded into Excel: 3. Add the SQL query to match the rows into the destination tableWe have now created the stage table in SQL Server and an Excel sheet where we can enter the updates that should be imported into your destination table in SQL Server. We will now create a small SQL script that will take the rows from the stage table and update those into the destination table.To run the script automatically as soon as any new rows are saved into the stage table, we use a post-processing feature in SQL Spreads called Post-Save SQL query. The Post-Save SQL query will run a SQL query immediately after the data from Excel is saved into a table in SQL Server.To enter the SQL script, go to the SQL Spreads tab and click the Document Settings button. Then select the second tab Other Settings and click the Post-Save SQL query button to bring up the dialog to enter the Post-Save SQL query: The SQL query that will match the rows looks like this:UPDATE destination SET destination.[ListPrice] = stage.[New List Price], -- Specify how the columns in destination table (PriceList) should be updated by changes from Excel, stored in the stage table (PriceListMatchedUpdates) destination.[ModifiedBy] = stage.[Modified By]FROM [SQLSpreadsDemo].[dbo].[PriceList] AS destination, -- Destination table where the changes from Excel should finally be updated [SQLSpreadsDemo].[dbo].[PriceListMatchedUpdates] AS stage -- Stage table to temporary store the changes from ExcelWHERE destination.[ProductCategory] = stage.[Product Category] -- Specify the matching keys here AND destination.[ProductNumber] = stage.[Product Number]-- Delete of all rows in the stage tableDELETE FROM [SQLSpreadsDemo].[dbo].[PriceListMatchedUpdates]Copy the SQL query above and paste it into the Post-Save SQL query dialog.Then modify the following part of the query:The names of the columns that should be updated (red marked below)The names of your stage and destination tables in the format [DatabaseName.[dbo].[TableName] (green marked below).The names of the columns that are used to match the rows between the stage and destination tabled (blue marked below) When finished, you can click the Execute button to test run the query. When the result from your test run shows "Query executed successfully" you can click OK twice to go back to the Excel document.4. Testing the solutionNow you are ready to test the solution.Add some rows to the ImportTable sheet and click Save to database. When you see the confirmation dialog, click OK and your updates will be saved to the stage table.When the changes are saved, the Post-Save SQL query will be executed and will update the destination table with the changes from the stage table.Optional settingsThese are a few optional settings that. Use Excel to Update Data in SQL Server; COMPARE SQL SPREADS. SQL Spreads vs Power BI; SQL Spreads vs Management Studio; SQL Spreads vs Custom web Getting started with SQL Spreads Getting up and running with SQL Spreads. An Intro to the SQL Spreads Data Management solution. Is SQL Spreads the right choice for me? In this video, we will walk you through how to: Evaluating SQL Spreads using the free 14-days trial; How to evaluate the Basic, Pro and Premium plans in SQL Spreads

SQL Spreads release 7.6.3 - SQL Spreads

First approach used the SQL Spreads Add-In for Excel. SQL Spreads is a lightweight Data Management solution to easily use Excel to update and manage data in Microsoft SQL Server. We looked at four features of SQL Spreads that makes it easy for general business users to perform data cleansing in Excel and save the data to SQL Server.We also looked at Data Quality Services (DQS) which is a component of the SQL Server suite. It includes two main activities – defining and maintaining knowledge bases that contain the ‘gold standard’ against which you want to check the quality of your data, and the cleansing process itself. DQS is a solid product built on top of SQL Server and would probably mainly be used by data quality analysts and engineers.To learn more about SQL Spreads, download the trial version here. Article by Andy McDonald Andy has worked 20+ years in the Engineering, Financial, and IT sectors with data analysis and presentation using tools such as SQL Server, Excel, Power Query and Power BI.Writes for SQL Spreads about Excel and SQL Server and how to tie those two together.

SQL Spreads release 7.5.4 - SQL Spreads

At non-technical users.Data cleansing in SQL Server using Excel and SQL SpreadsExcel is often used in data wrangling and data cleansing processes by a variety of users using an array of techniques. The SQL Spreads Add-In for Excel makes this easier by allowing you to directly make updates to data in your SQL tables from within the familiar Excel UI.SQL Spreads has the following features to make your data cleansing jobs simpler and easier:easily connect to tables in SQL Server and view and change data directly from within Excel before saving back to SQLuse Excel files to review and clean data and then create new tables in SQL Server with the push of a buttonuse lookup columns and drop-down lists to ensure users enter valid valuesuse ‘post-save’ queries in SQL Spreads to validate data before it is pushed to your SQL tablesWe’ll briefly look at these capabilities.Import data from SQL Server, clean in Excel and save back to SQL ServerUsing the SQL Spreads Add-In for Excel it is very easy to create a connection to SQL Server and then import data from a table. The screenshot below shows the SQL Spreads Designer pane – to import the sample Company table I just needed to expand the relevant database and then click on the correct table; once selected, the data is loaded into Excel.Once the data is in Excel, I can work on cleaning it up. In this simple example, I’ve copied and pasted the ‘correct’ versions of the company names and standardized the City and State names. It’s then just a case of clicking on the ‘Save to Database’ button to have the data written back to the table in SQL Server.Clean data in Excel and create new tables in SQL ServerIn data cleansing, it is common to get sent

SQL Spreads release 6.2.1 - SQL Spreads

Download the free 14-day trial for the SQL Spreads Excel Add-in if you want to easily let your non-technical users update and manage SQL Server data from Excel. We come across ‘dirty’ data all the time. It’s one of the realities of working with data on a regular basis. With the rise of data democratization in the workplace, more people across the organization are exposed to the problem of dirty data and are also more likely to be involved in the cleansing process.In this article we’re going to look at how non-technical people can perform data cleansing in SQL Server using 2 different methods: SQL Spreads and Data Quality Services.What is Data Cleansing?Data cleansing is the process of detecting and correcting (or removing) corrupt or inaccurate records from a record set, table, or database. Data cleansing may be performed interactively with data wrangling tools, or as batch processing through scripting in languages like T-SQL or R.How does data become corrupt or inaccurate (ie “dirty”) in the first place? Some of the more common reasons are listed below:Human error during manual inputCorruption during data transferDifferent data definitions being used in sources of merged data setsDifferent formats (eg dates) being used in sources of merged data data setsThe type of data that we’re talking about here is often master data or reference data. Here is a very simple example of some data that needs to be cleansed:The data cleansing process for this example may include the following:Normalization of the Phone number field to use a standard format of {area code-XXX-XXXX}Formatting all dates to be {YYYY-MM-DD}Looking up the ‘approved’ values for City and State (eg finding that “NY” is a synonym for New York)Rejecting the 4th record because the Order Date value is incomplete (note that we may choose to accept the 3rd

SQL Spreads release 7.0.8 - SQL Spreads

Did you find this summary helpful? Thank you for your feedbackIntroThe VBA-based demo solution to import a list of changes in Excel into a table in SQL Server can import maybe a few hundred rows per minute.If you need to import a larger number of rows, you can use this demo solution that uses a SQL query to do the matched import.This solution can import 500 rows into a table with 10.000 rows in less than 0.5 seconds.FeaturesMatched import of new updates.Optimized for larger databases and number of rows.Supports insertion of new rows.Prevents duplicates in the imported rows.Does not require any VBA codePrerequisitesYou will need to be authorized to create a new table in SQL Server where the updates are temporarily stored.You will need to make smaller adjustments to the matching SQL query by replacing the demo table and column names with your table and columns names.The solution requires that you have the SQL Spreads Excel Add-In installed.How to create the solutionThis solution will use a stage table in SQL Server where you import the list with changes from Excel using SQL Spreads.When the changes are imported into the stage table in SQL Server, a small SQL script will do the matching and updating of the changes into your final destination table.1. Create a new Excel sheet where you enter the changes to be imported into SQL ServerThis sheet should include:The Key column(s) used to match each imported row with the row in SQL ServerThe value column(s) that should be updated in SQL ServerA couple of rows of example dataNote:Please make sure that your data is in an Excel table. If not, select a cell within your data and press CTRL + T to create an Excel table.This is how this Import sheet looks in our Price List example:In our example the columns 'New List Price' and 'Modified By' should be updated in the database, and the key columns used to match the rows are the 'Product Category' and 'Product Number' columns.2. Create the stage table to temporarily store the updatesWe will now use the Excel table from step 1 to create the stage table in SQL Server, using the SQL Spreads Table Creator.Go to the Excel sheet created in section 1 above.Head over to the SQL Spreads tab in Excel and click the Design Mode button and then the Create New SQL Table button: The Create SQL Server Table dialog will open. Type the name of the stage table and select in which Database the table should be created: Click the More Settings... link-button to show the part of the dialog where you can adjust the data types. Adjust the data types and the length for each of your columns from Excel. These types and length will be used to validate the data entered into Excel so it is recommended to use the same data types as in your final destination table. If you check the Primary Key checkboxes for your key/match columns, you will get a warning whenever. Use Excel to Update Data in SQL Server; COMPARE SQL SPREADS. SQL Spreads vs Power BI; SQL Spreads vs Management Studio; SQL Spreads vs Custom web

SQL Spreads release 7.2.5 - SQL Spreads

DataWith SQL Spreads, you can easily add SQL data validation rules from within Excel using a data post-processing script.The Data Post-processing script is a SQL query that will be executed as the last step in the transaction that updates the database with the changes made in Excel. By default, SQL Spreads will always validate all entered values against the Data Types in SQL Server. The process described below is a more advanced option that is similar to the CHECK constraint in SQL Server.The example below shows a Post-Save SQL query that checks the Company field to see if the contents match the ‘approved’ values:IF (SELECT COUNT([Company])FROM [Demo].[dbo].[Company]WHERE Company NOT IN ('Microsoft Corporation', 'Marvel Entertainment, LLC'))> 0RAISERROR('The Company name is not one of the approved values',16,1);The screenshot below shows the validation in action:Data cleansing in SQL Server using Data Quality ServicesData Quality Services (DQS) is a component of the SQL Server suite. You can install it as an additional feature as part of the installation process.DQS is made up of two components:Data Quality Server: this is installed on top of the SQL Server Database Engine, and includes three databases:DQS_MAIN contains DQS stored procedures, the DQS engine, and published knowledge bases.DQS_PROJECTS contains the data quality project information.DQS_STAGING_DATA is the staging area where you can copy your source data to perform DQS operations, and then export your processed data.Data Quality Client: this is a standalone application that enables you to connect to Data Quality Server, and provides you with an interface to perform data-quality operationsIn simple terms, you use DQS to discover, build and manage knowledge about your data. Using this knowledge, DQS can then be used to cleanse your data using computer-assisted and interactive methods. The main console of the DQS client shows these two main activities (along with an Admin section).Knowledge

SQL Spreads release 7.0.5 - SQL Spreads

You can do to further improve the solution.Automatically clear the ImportTable sheet after every importTo automatically clear the import sheet after the changes are saved to SQL Server, go to the Document Settings (open Design mode to enable it) and check the Refresh import data after data is saved to database: Show any unmatched rowsIf you would like to see any unmatched rows after the update is done, you can do a small change to the Post-Save SQL query.Modify the Delete statement to only delete the matched rows. Then the unmatched rows will be kept in the stage table and you will see them immediately after the update if you also apply the "Automatically clear the ImportTable sheet after every import" option above..The change you need to do is to replace the Delete statement in the SQL script: with the Delete statement below:-- Delete only matched rows - keep unmatched for feedbackDELETE stageFROM [SQLSpreadsDemo].[dbo].[PriceListMatchedUpdates] AS stage, -- The temporary storage of the updates from Excel [SQLSpreadsDemo].[dbo].[PriceList] AS destination WHERE destination.[ProductCategory] = stage.[Product Category] -- By specifying the match here, we keep the unmatched rows in the stage table as a feedback to the user AND destination.[ProductNumber] = stage.[Product Number] This updated Delete statement will keep any unmatched rows in the stage table.Insert new rows into the tableIf there are rows that do not match, and your users need to insert those rows into the destination table, you can create a separate import sheet where new rows can be inserted into the destination table:Create a new sheet in your Excel document.Go to the SQL Spreads Designer and select your destination table.Go to the columns tab and select the columns that the user must fill out to insert new rows.Go to the Settings tab in the Designer and check the Only allow inserting new rows. This setting will prevent users from modifying any existing rows in the table. You will now have a separate Import sheet in the document where the users can insert new rows into the table.Show warning for duplicate rowsIf you would like to give users a warning when they enter duplicate rows into the ImportTable sheet, make sure that you check the Primary Key checkboxes for your key/match columns when you create the stage table: Hide the Id columnTo hide the Id column from the ImportTable sheet follow these steps:Open the SQL Spreads DesignerGo to the Columns tabMake sure your key/match columns are set as Key columnsUncheck the Is key checkbox for the Id columnUncheck the Show checkbox for the Id columnIf you need any help to get the solution up and running, please send an email to support@sqlspreads.com. Use Excel to Update Data in SQL Server; COMPARE SQL SPREADS. SQL Spreads vs Power BI; SQL Spreads vs Management Studio; SQL Spreads vs Custom web

Using SQL Spreads with VBA - Designing SQL Spreads solutions

 MB Psychic DictionaryMB Free Psychic Dictionary is Mystic Boards latest offering in making the Mystic Board the ultimate destination for everything related to the psychic and paranormal world. This glossary describes all the basics as well as the definitions for the concepts ...Category: EducationDeveloper: MysticBoard.com| Download | FreeMB Aromatherapy Dictionary SoftwareMB Free Aromatherapy Dictionary Software is an extensive collection of words related to the field of aromatherapy, which is an alternative method of healing. This field is related to the sense of smell or aroma and the use of different oil, scents, fragrances ...Category: EducationDeveloper: MysticBoard.com| Download | FreeAdvertisementMB Occult Dictionary SoftwareMB Free Occult Dictionary Software is a collection of all possible Occult related words. This software is an easy reference of words and definitions related to occult, metaphysics, witchcraft and pagan. This software is Mystic Board's latest offering ...Category: EducationDeveloper: MysticBoard.com| Download | FreeMB Runes DictionaryMB Free Runes Dictionary is an extensive collection of words related to the runes methodology of divination. This includes the meanings of the different rune stones, spreads, and other esoteric meanings. This FREE online Runes dictionary is a very useful ...Category: EducationDeveloper: MysticBoard.com| Download | FreeMB Tarot DictionaryMB Free Tarot Dictionary is an extensive collection of words related to the tarot methodology of divination. This includes the meanings of different spreads, decks, cards, symbols and other esoteric meanings. All terms have been clearly explained in simple ...Category: EducationDeveloper: MysticBoard.com| Download | FreeMB Numerology DictionaryMB Free Numerology Dictionary is an extensive collection of numerology related terms. This software describes the significance of the different calculations used in numerology to understand the personality of a person as well as the significance of each ...Category: EducationDeveloper: MysticBoard.com| Download | FreeMB Dream DictionaryMB Free Dream Dictionary is an extensive collection of interpretations of dream symbols. Dreams tell us a lot more than we can understand in reality. Understanding dreams helps a person overcome his inhibitions and lead a better quality of life. All terms ...Category: EducationDeveloper: MysticBoard.com| Download | FreeMB Divination DictionaryMB Free Divination Dictionary Software is the largest esoteric dictionary online with more than 5,500 words.This software is an easy reference of words and definitions related to Astrology, Tarot, Runes, Psychic, Numerology and Dream Interpretations.Category: EducationDeveloper: MysticBoard.com| Download | FreeSQL Dictionary Italian FrenchDictionary Database Italian-French to integrate into your applications in SQL Format. The demoversion includes the letter P with 3399 Keywords. Fullversion Italian-French 33354 keywords with 359444 entries and French-Italian Dictionary 33714 keywords ...Category: Database SolutionsDeveloper: TT-Software| Download | Price: -SQL Dictionary German SwedishDictionary Database German Swedish to integrate into your applications in SQL Format. The demoversion includes the letter P with 1572 keywords. Fullversion German-Swedish Dictionary 36816 keywords with 303819 entries and Swedish-German Dictionary 34817 ...Category: Database SolutionsDeveloper: TT-Software| Download | Price: -SQL Dictionary Swedish GermanDictionary Database Swedish German to integrate into your applications in SQL Format. The demoversion includes the letter O with 1220 keywords. Fullversion Swedish-German Dictionary 34817 keywords with 299075 entries and German- Swedish Dictionary 36816 ...Category: Database SolutionsDeveloper: TT-Software| Download