Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday:  Removing Blank Rows from an Excel Data Source


Excel is a very common data source for SmartConnect maps, but sometimes causes problems when SmartConnect reads blank lines from a worksheet.  This isn’t an issue with SmartConnect, but rather an issue with Excel not knowing what cells are being ‘used’.  After building a spreadsheet, sometimes Excel thinks that blank rows at the bottom of a table contain information (usually after clearing the contents).  Because this can cause problems in a SmartConnect map, it’s best to remove the blank rows from the source before running a map, but how do we do that?

The first problem is knowing if we have blank lines, and the second problem is to actually remove them.  We can check if there are blank lines using the SmartConnect Map Setup window or from Excel.  If we open up the Map Setup window in SmartConnect and click Preview, we will see a bunch of blank lines being included.

 There are a bunch of blank rows in the preview window, so we definitely have blanks.  The other way to check for blanks within Excel is to find the ‘UsedRange’ property on the worksheet being sent.  If we open up the spreadsheet, the tiny scrollbar already tells us that Excel thinks the workbook has a lot more data than it really does.

To find everything Excel thinks has stuff in it, hit CTRL-END on the worksheet you are checking.  The CTRL-END shortcut will select the bottom-right cell of that worksheet’s UsedRange property. The picture below shows where Excel thinks our data table extends down to.

Not even close!  We need to delete these 10 thousand or so rows and re-save the workbook.  You can use whatever method you want to delete these rows, but I’ll give you the keyboard shortcuts in case you aren’t familiar (or you want to impress a very particular type of geek)

  • Click A12 (the first blank cell below our table)
  • Hold Control-Shift and press down arrow to select A12 to A1048576
  • Press Shift-Space to select the entire rows
  • Press control and the negative sign to delete the rows, or right-click any of the row numbers and click delete.
  • Save the workbook (Control-S)

After saving the workbook, the scrollbar should be a more appropriate size.  If you press CTRL-END again, now it should go to the bottom of the table as expected.

After saving and closing the workbook and previewing the data again in the map setup window, we now see the correct number of rows in the preview.

Problem solved!

Leave a Comment





RECENT POSTS


eOne Sessions at Directions North America 2023
Connect with eOne Solutions at Directions North America 2023!
Tech Tuesday: Creating a Summarize List by States Between Two Systems
eOne Sessions at Days of Knowledge UK - Live from Birmingham!
Employee Spotlight: Ally Tronson, Business Development Representative

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.