Skip to content
+1-888-319-3663

COMMUNITY FORUM

excel data source shows blank lines

johng asked 4 years ago
We are using an Excel data source in order to utilize the folder datasource.  The maps run, but there is always an error regarding a blank line. “Processing failed for datarows matching vendorid = “DocumentNumber =”.  I found the document from TechTuesday that suggested to use an ODBC connection to filter the excel blanks, https://www.eonesolutions.com/tech-tuesday-understanding-restrictions-inside-of-smartconnect/ 
 
 
but we cannot use the ODBC when using the Folder Data Source.
How can we get the Excel data source to ignore the blanks while still using the folder datasource?
Answers
Lorren Zemke Staff answered 4 years ago
John,
On the Options tab of the map, you can switch the option If there are blank lines in the data Source: to Exclude Blank Keys or Exclude Blank Lines.
Pat B replied 3 years ago

Hi Lorren – I just tried this fix, but it did not work for me. I believe this is because my underlying Excel source file actually has some formulas that are returning many rows with nothing but blanks (“”). Here’s an example of my formula, which I’m guessing Smart Connect is not recognizing as “true” blank cells or “null” cells:

=IF(ISBLANK(‘Pmt Manager GL Extract Run’!BF2),””,’Pmt Manager GL Extract Run’!BF2)

Any way to get around this using the straight Excel 2013 connection, and not having to go thru an ODBC connection with a limiting query?

Smart Connect Version 20.17.0.17
Dynamics GP 2016
Excel 2013

Thanks!
Pat


If you would like to submit an answer or comment, please sign in to the eOne portal.