Welcome Guest! To enable all features please Login or Register.
Go to last post Go to first unread
#1 Posted : Thursday, August 20, 2015 10:40:06 AM(UTC)

Rank: Administration

Groups: Administrators
Joined: 8/17/2015(UTC)
Posts: 22

Was thanked: 2 time(s) in 2 post(s)
A problem sometimes arises when trying to read data from an Excel spreadsheet. There may be data in cells, but when you look at it in the preview pane in Inaport, the cells look empty.

Fundamentally, Excel is NOT a database and the columns in a spreadsheet do not have a data type associated with them in the same way that that a database does. The Excel ODBC driver has to make an assumption about the data type for a given column. The driver does this by reading ahead 8 records and looking at the data found. It then makes a decision about the data type based upon
  • the majority type of the first 8 rows.

Problems occur when the assumption about data type is proven wrong by subsequent records. Consider two scenarios….

An Excel spreadsheet has a column which contains text data. The first 5 records contain text in this column, but rows 6, 7, 8 contain numbers. The Excel ODBC driver reads this data and assumes that a text data type will be appropriate for this column. Rows 6, 7, 8 will show as blank, because the cells hold numbers and not text.

Conversely, suppose the first 5 rows hold numbers; if rows 6, 7 and 8 hold text, they will appear blank when you look at them in the preview pane.

A possible work around is to force the contents of a column to be text.

You CANNOT do this by changing the format of the column - this just change show the cell is displayed, NOT how the data is actually stored and processed.

If you have some numbers in a text column, you can preface the numbers with a single quote to make them text: '123

Another technique is to add a column in Excel, and use the text() function. This converts the contents of a cell to text. if the source cell is a number, it gets converted to text; if the source is already text, it does not get changed. For example:
  • Cell A1 holds the number 123; cell B1 has the formula =text(a1, "0"). When read by Inaport, Inaport sees text "123"
  • Cell A2 holds the text "abc"; cell B2 has the formula =text(a2, "0"). When read by Inaport, Inaport sees text "abc"
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF | YAF © 2003-2021, Yet Another Forum.NET
This page was generated in 0.078 seconds.