none
Excel driver data types RRS feed

  • Question

  • This link here says the following and I find it kind of ambiguous.  If all numeric columns are interpreted as doubles, then why did they list Currency?  Can I set up the worksheet so numeric cells import as Currency rather than doubles? 


    Issues with data types

    Data types

    The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). SSIS maps the Excel data types as follows:

    • Numeric - double-precision float (DT_R8)

    • Currency - currency (DT_CY)

    • Boolean - Boolean (DT_BOOL)

    • Date/time - datetime (DT_DATE)

    • String - Unicode string, length 255 (DT_WSTR)

    • Memo - Unicode text stream (DT_NTEXT)


    Wednesday, December 11, 2019 6:24 PM

Answers

  • Hi M.S. Gregory,

    You only made it half  way. The explanation is below.

    1. Again, MS Excel doesn't have a notion of any data types. It was a wrong expectation.
    2. SSIS has XML Source Adapter. It will generate for you an XSD Schema file, covering overall structure, data elements, and their data types.
      Check it out here: Importing XML documents using SQL Server Integration Services
    3. There is an additional benefit that you can query XML file as a virtual DB table on the file system.

    P.S. You can find me on LinkedIn and Skype.

    Thursday, December 12, 2019 9:31 PM

All replies

  • Hi M.S. Gregory,

    • SSIS is metadata driven.
    • On the other hand, MS Excel doesn't have any notion of data types. It is like a virtual electronic paper. It tolerates any scribble on it.

    Microsoft ACE OLEDB Provider is guessing what data types are in Excel file.

    That's why it is better not to use MS Excel files for data feeds.

    It is much better to use XML files enforced by an XML Schema for that.


    Thursday, December 12, 2019 12:24 AM
  • Hi M.S. Gregory,

    If all numeric columns are interpreted as doubles, then why did they list Currency?

    Can I set up the worksheet so numeric cells import as Currency rather than doubles? 

    1.I agree with the following opinions.

       The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as    doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR).

    2.The data type will be currency only the data is "Symbol+Number" in excel. For example,$1,000.

       We can change the datatype of data in worksheet from numeric to currency in excel by clicking Format Cells.

       Please refer to the following pictures :

    Fees2 1,000 Numeric - double-precision float (DT_R8)

    Fees2 $1,000 Currency - currency (DT_CY)

    We can also use Data Conversion or Derived Column  in SSIS to change the datatypes.

    Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 12, 2019 3:20 AM
  • I don't know how I would get the users to convert their data to XML because they all use Excel.
    Thursday, December 12, 2019 1:48 PM
  • Thanks for the reply, Mona!

    I was looking for the exact definition of the currency type and this page says this:

    DT_CY A currency value. This data type is an eight-byte signed integer with a scale of 4 and a maximum precision of 19 digits.

    But integers always have zero scale.  Surely they meant eight-byte signed float?

    Edit:

    Looking into this further, I think they meant that Currency values are stored as integers internally, according to this article and Currency shouldn't be used!

    https://www.red-gate.com/hub/product-learning/sql-prompt/avoid-use-money-smallmoney-datatypes

    Thursday, December 12, 2019 1:56 PM
  • I don't know how I would get the users to convert their data to XML because they all use Excel.

    Hi M.S. Gregory,

    Here is a good step-by-step walk-through on how to it.

    How to Convert an Excel Spreadsheet to XML

    Thursday, December 12, 2019 3:27 PM
  • I don't see how that's different.  The XML that he created in that article doesn't contain data types either!
    Thursday, December 12, 2019 9:00 PM
  • Hi M.S. Gregory,

    You only made it half  way. The explanation is below.

    1. Again, MS Excel doesn't have a notion of any data types. It was a wrong expectation.
    2. SSIS has XML Source Adapter. It will generate for you an XSD Schema file, covering overall structure, data elements, and their data types.
      Check it out here: Importing XML documents using SQL Server Integration Services
    3. There is an additional benefit that you can query XML file as a virtual DB table on the file system.

    P.S. You can find me on LinkedIn and Skype.

    Thursday, December 12, 2019 9:31 PM
  • Really interesting, Yitzhak!  This looks like a great solution if we can reuse the XSD's and don't have to build them out all the time.  I might be able to convince the users that it's worth it to export the XML from Excel before importing into SQL Server, or maybe build that step into web app somehow so it's more guided.
    Friday, December 13, 2019 2:29 PM
  • Really interesting, Yitzhak!  This looks like a great solution if we can reuse the XSD's and don't have to build them out all the time.  I might be able to convince the users that it's worth it to export the XML from Excel before importing into SQL Server, or maybe build that step into web app somehow so it's more guided.

    Hi M.S. Gregory,

    You are right on target. There is no need to rebuild XSDs all the time.

    You just started getting benefits of the XML approach. There are many more...

    Additionally, SSIS Excel Source Adapter, even when it is working, has an artificial limitation of 255 characters of length for any string data coming from an Excel column.

    As I mentioned earlier: "There is an additional benefit that you can query XML file as a virtual DB table on the file system."

    For example, XML file:

    <root>
       <row id="1">
          <city>Miami</city>
          <population>470914</population>
          <submitted>2019-08-12</submitted>
       </row>
       <row id="2">
          <city>Orlando</city>
          <population>285713</population>
          <submitted>2019-08-10</submitted>
       </row>
       <row id="3">
          <city>Tampa</city>
          <population>392890</population>
          <submitted>2019-07-25</submitted>
       </row>
    </root>

    SQL:

    DECLARE @tbl TABLE (
       ID INT IDENTITY(1,1) PRIMARY KEY
       , [city] VARCHAR(30) NOT NULL
       , [population] INT NOT NULL
       , [submitted] DATE NOT NULL
    );
    
    WITH XmlFile (xmlData) AS
    (
       SELECT CAST(BulkColumn AS XML) 
       FROM OPENROWSET(BULK 'e:\Temp\CityPopulation.xml', SINGLE_BLOB) AS x
    )
    INSERT INTO @tbl(city, population, submitted)
    SELECT c.value('(city/text())[1]', 'VARCHAR(30)') AS [city]
       , c.value('(population/text())[1]', 'INT') AS [population]
       , c.value('(submitted/text())[1]', 'DATE') AS [submitted]
    FROM XmlFile CROSS APPLY xmlData.nodes('(/root/row)') AS t(c);
    
    -- test
    SELECT * FROM @tbl;


    Friday, December 13, 2019 3:24 PM