How to Combine Excel Worksheet Data with a SQL Server View
Question details
Combine local Excel worksheet data with information from a centralized SQL Server view to format and print a custom GS1 pallet label.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating custom printed labels with specific dimensions by merging a local spreadsheet design with records imported from a large SQL database.
- Observed behavior
- The user needs an efficient method to fetch only the necessary fields from a large database and merge them seamlessly into a local label design worksheet.
Ensure you have your SQL Server name, database name, and the appropriate login credentials ready before attempting to establish a connection in Excel.
Connect and Transform SQL Data using Power Query
Use Power Query to connect directly to your SQL Server database and load only the specific fields required for your labels, preventing performance issues with large databases.
Importing an entire large database directly into a worksheet is inefficient. Power Query acts as an intermediary, allowing you to connect to the SQL Server view, transform or filter the data, and load only the necessary records into your workbook.
In Excel, navigate to the 'Data' tab, click on 'Get Data', select 'From Database', and then choose 'From SQL Server Database'.
Input your Server name and Database name in the dialog box. You may be prompted to enter your database credentials to authenticate the connection.
Select the specific SQL view you need from the Navigator window. Instead of clicking 'Load', click 'Transform Data' to open the Power Query Editor. Remove unnecessary columns and filter rows, then click 'Close & Load' to output the refined data into a new worksheet.

Design the Label and Merge Data with Lookup Functions
Create a dedicated worksheet for the label layout and use lookup functions to pull the imported SQL data into the correct fields.
Manage Database Imports and Design Custom Labels in WPS Spreadsheet
WPS Office provides excellent external data handling capabilities and robust formula support. You can easily bring external database records into WPS Spreadsheet, design precise label layouts, and use standard lookup functions to merge your data for printing.
- 1. Import External Data: Open WPS Spreadsheet, navigate to the 'Data' tab, and use the 'Import Data' function to connect to your external data source or bring in your exported database files.
- 2. Create the Label Template: Add a new worksheet and use the 'Page Layout' options to configure your custom paper size (e.g., 5.5 x 8.5 inches) for the pallet label.
- 3. Merge Data with VLOOKUP: Insert VLOOKUP formulas into your label template to automatically fetch information from your imported data table based on a unique reference ID.
- 4. Print the Label: Highlight your completed label template, set the print area, and print directly to your thermal or laser label printer.

Frequently Asked Questions
Can I automatically refresh the imported SQL Server data before printing?
Yes. You can right-click anywhere inside the imported data table and select 'Refresh'. Alternatively, you can go to the connection properties and configure the data to refresh automatically whenever the workbook is opened.
How do I accurately set up a 5.5-by-8.5-inch print area in Excel?
Navigate to the 'Page Layout' tab, click 'Size', and choose 'More Paper Sizes'. Enter 5.5 for the width and 8.5 for the height. Then, select the specific cells making up your label design, click 'Print Area', and choose 'Set Print Area'.
Why is my VLOOKUP returning an #N/A error when referencing the SQL query table?
The #N/A error generally occurs if the lookup value does not exactly match the data in the SQL table. Check for trailing spaces, invisible characters, or mismatched data types (such as numbers formatted as text in the SQL database but entered as standard numbers in your label sheet).




