CODEWITHSUNDEEP
vb.netvisual-studiosql-server-2005excel
Vikky
Vikky

Reputation: 764

Bulk insert from specific excel workbook sheet having more then 280 columns

can any one suggest me what to do as i used Openrowset but it didnot works well the sheet contain variable no of columns.

        Dim sConnectionString1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & exfname & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        Dim con As New OleDbConnection(sConnectionString1)
        objCmdSelect = New OleDbCommand("SELECT  * FROM [sheet 12$]", con)


        Dim dr_x As OleDbDataReader
        con.Open()
        dr_x = objCmdSelect.ExecuteReader()
        con.Close()

This works well when having about 240 columns, but if it exceed to 256 it gives error and the connection would not get open. So i decided to use bulk insert into a temp table , can any one advise how to bulk insert from specific sheet of workbook I have tried

select *   into test1 from (
select * from OpenRowset(
 'Microsoft.Jet.OLEDB.4.0',
 'Excel 8.0;Database=C:\Documents and Settings\VIKKY\My Documents\Downloads\alvin\xyz.xls',
 'select * from [sheet12$]')) a

But it didnot works when number of columns exceed but works well on sql when columns are less. Masters please guide me. Thanks for help in advance.

Upvotes: 1

Views: 1638

Answers (1)

Juliusz
Juliusz

Reputation: 2105

Is is a bit confusing - what is the file format you are accessing the data from? If it is the old Excel 97-2003 format, how do you manage to have more than 256 columns? If it is 2007 or later version, why are you using outdated OLE DB driver? Have you tried Microsoft.ACE.OLEDB.12.0? (connectionstrings.com)

Upvotes: 2

Related Questions