Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

How does SQList export numeric values?

It is important to understand how SQList exports numbers, especially when they have decimals and/or are defined to be displaied as percentages.

The basic rule for decimal positions it that SQList will use the column definition to determine how many decimals the correspondent column in the SQL table will contain:
  • when the "Number of decimal places" is set to 1, 2, 3, etc., SQList will export the column as a DECIMAL(18, 1), DECIMAL(18, 2), etc.;
  • when the "Number of decimal places" is set to "Automatic", SQList will export the column as a FLOAT rather than a DECIMAL;
  • when the "Number of decimal places" is set to "Zero", SQList will export the column as a INT rather than a DECIMAL.
Something else happens however, when the column is also set to "Show as percentage": in this case, SharePoint will not store the number "as you see it on the screen" but it will store it divided by 100. This will increase the actual number of decimals by two digits.

Warning: Because the SharePoint web services return numbers as they are stores, when SQList exports them to the SQL table there may be a loss of precision due to the actual precision of the number being higher than the precision defined by the column.

To work around the issue of loss of precision, you can simply increase the number of decimals for the column (e.g. from 2 to 4) at which point the whole number will be stored in the SQL table. However, as we understand that this is not an ideal solution, we are adding a feature to automatically handle this situation in version 4.3 of SQList.

This table lists all combinations for the number 123.4567:

Number of decimal places Show as percentage Stored by SharePoint as SQList exports it as SQL type
Automatic No  123.4567  123.4567 FLOAT
Zero No 123.4567  123 (rounded) INT (by default)
DECIMAL(18, 0)

(user configurable)
No 123.4567 123.5 (rounded) DECIMAL(18, 1)
2 No 123.4567 123.46 (rounded) DECIMAL(18, 2)
3 No 123.4567 123.457 (rounded) DECIMAL(18, 3)
4 No 123.4567 123.4567 DECIMAL(18, 4)
Automatic Yes 1.234567 (divided by 100)  1.234567 FLOAT
Zero Yes 1.234567 (divided by 100) 1 (rounded) INT (by default)
DECIMAL(18, 0)

(user configurable)
Yes 1.234567 (divided by 100) 1.2 (rounded) DECIMAL(18, 1)
2 Yes 1.234567 (divided by 100) 1.23 (rounded) DECIMAL(18, 2)
3 Yes 1.234567 (divided by 100) 1.235 (rounded) DECIMAL(18, 3)
4 Yes 1.234567 (divided by 100) 1.2346 (rounded) DECIMAL(18, 4)