none
Alternatives to Microsoft.ACE.OLEDB.15.0 RRS feed

  • Question

  • I upload on a monthly basis a number of spreadsheets from Excel 2013 into SQL 2014. My excel data has several named ranges and I utilize the DoCMD.TransferSpreadsheet to perform the transfer utilizing those named ranges.

    Recently I was asked to expand the name range from 120 to 480 cells. The named range runs across columns in the spreadsheet for ease of use of the users. The process doesn't work because a limitation of 255 columns. Is there an engine that doesn't have the 255 limitation or alternatively is there a way to force the transfer, through a parameter, to treat the excel columns as rows.

    I attempted a complete re-write in SSIS, Visual Studio 2015, but the underlying engine is the same so same limitations.

    Any help is greatly appreciated.

    Tuesday, June 27, 2017 6:35 PM

All replies

  • Hi SKG23,

    you had mentioned that you are using DoCMD.TransferSpreadsheet method.

    this method is MS Access method.

    DoCmd.TransferSpreadsheet Method (Access)

    are you working with MS Access?

    so at present it looks like first you are transferring the data from Excel to Access and then transferring the data to SQL.

    I try to make a test on my side.

    I try to create a named range (A1:A500).

    then I try to use code below.

    Sub demo()
    DoCmd.TransferSpreadsheet acImport, 10, _
     "Table8", "C:\Users\v-padee\Desktop\demorange.xlsm", True, "demorange"
    End Sub
    

    I find that it import data from all 500 cells.

    can you post your code and also try to clear your approach how exactly you are transferring your data from Excel to SQL using DoCMD.TransferSpreadsheet method.

    so that we can try to understand your approach and try to provide you suggestion.

    Regards

    Deepak


    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.

    Wednesday, June 28, 2017 6:17 AM
    Moderator
  • Hi SKG23,

    >> My excel data has several named ranges and I utilize the DoCMD.TransferSpreadsheet to perform the transfer utilizing those named ranges.

    Could you share us how you process this by code? In my memory, DoCMD.TransferSpreadsheet is a command in Access, how did you transfer the Excel data to SQL, do you mean you export the data from Access to Excel, and then import data into SQL from Excel?

    As you know, this is the limit of SSIS. I would suggest you try whether it is possible to export data to csv file, and then import it to SQL.

    Here is a reference:

    # Workaround for exporting data to Excel with more than 255 columns

    https://stackoverflow.com/questions/42322816/workaround-for-exporting-data-to-excel-with-more-than-255-columns

    Best Regards,

    Edward


    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.

    Friday, June 30, 2017 7:27 AM