SmartConnect Not “Reading” Certain Cells
John – I have a couple of suggestions and questions for you.
1. How are you connecting the spreadsheet? Are you using the Excel Data Source or an ODBC connection?
2. What other data is on columns D?
3. The Excel connector looks at the first few rows (8 rows I think) to determine the data type of that column. If the first few rows are seen as a number, it assumes the whole column is numbers and ignores strings – and vice versa.
4. Take a look at the formatting of that column to ensure it is consistent. Try sorting the data differently to see if the results change.
Thanks, Martin! It looks like you’re right! Once I sorted the spreadsheet for that column from Z to A, SmartConnect was able to read all of the values.
The format of the column was already “General”. So, this sorting was the only means of supplying a workaround.
Again, thanks, for that brilliant insight! 🙂
You could also try formatting the cell as “Text” in place of general. Text takes whatever is there always treats it as a string, while General does sometimes assume a numeric value if enough of them are numbers.
The map is not going to be run by me. So I cannot relied on the users to remember to format the column to “text” or sort it “Z-A”
Is it possible to add a line at the beginning of the Excel file (by Smartconnect), with the needed column as “AAAAAAA”, so that it will be read as string for the rest of the file?
If you would like to submit an answer or comment, please sign in to the eOne portal.