Skip to content
+1-888-319-3663

COMMUNITY FORUM

SmartConnect Not “Reading” Certain Cells

johnellis asked 9 years ago
Hello: I have a SmartConnect map for the GP general ledger that successfully runs and imports data from an Excel 2007 spreadsheet. When previewing this file in the SmartConnect Data Source window, the only data from Column D that appears are cells containing
martin replied 9 years ago

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.

Martin

johnellis replied 9 years ago

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! đŸ™‚

John

Chris Hanson replied 9 years ago

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.

H

Answers
johnellis answered 9 years ago
Thanks, Chris!
Richard L answered 6 years ago
I have the same situation: some data in the column are numbers, some are text. Text are not read (because of the reason mentioned above)
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.