Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trying to import specific rows from excel to sql table. But: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Posted on 2013-11-25
4
Medium Priority
?
1,405 Views
Last Modified: 2013-11-26
Hi,
I have workable code to import excel data into sql table, but only as [Sheet1$]. But, I'm trying to choose specific rows to be imported like [Sheet1$A3:C3]. And, of course not working, and getting an error: System.Data.OleDb.OleDbException: No value given for one or more required parameters.
So, I would like someone to check if this is actual correct procedure, or something else...Or, maybe my approach is totally wrong.

Private strConnection As String = "<MyServerConnection>"
    Protected Sub btnSend_Click(ByVal sender As Object, ByVal e As EventArgs)
       Dim path As String = fileuploadExcel.PostedFile.FileName
        Dim excelConnectionString As String = (Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=") & path) + ";Extended Properties=Excel 12.0;Persist Security Info=False"
        Dim excelConnection As New OleDbConnection(excelConnectionString)
        Dim cmd As New OleDbCommand("Select [ID],[FirstName],[SecondName] from [Sheet1$A3:C3]", excelConnection)
        excelConnection.Open()
        Dim dReader As OleDbDataReader
        dReader = cmd.ExecuteReader()
        Dim sqlBulk As New SqlBulkCopy(strConnection)
       sqlBulk.DestinationTableName = "test"
        sqlBulk.WriteToServer(dReader)
        excelConnection.Close()
    End Sub

Thank you in advance for help.
0
Comment
Question by:dejandejanovic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 600 total points
ID: 39676447
Does it take excessive time? Or is there some other reason for wanting to change?

My suggestion would be to import the data to a staging table and then move the rows you want to the final table. You don't state what the DB system you are using in your question. But if it is SQL and you use TRUNCATE TABLE it resets the table each time and shouldn't cause excessive growth.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1400 total points
ID: 39676509
For me, this works via SQL Server  :

Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\ee\TEST.xlsx;HDR=NO;IMEX=1', 'SELECT * FROM [Sheet1$A1:C10]') as a         

Open in new window

So, it shouldn't be an oledb problem per se. But you might want to triple check your command line syntax and make sure of the single quotes etc.

But lets also think outside the box...

well, as part of the select statement, you can use the "WHERE" clause.  

the A:C is taken care of by the column names (might want to add in ";HDR=NO;IMEX=1" to make very sure, then columns are F1, F2...etc ) so, it becomes a matter of identifying row=3, and you could select top 3 to make sure it doesn't go further and then only exclude the first two rows in the "where".

Couple of strategies... One is to use the "where" to identify the contents in that row number, next would be to loop and count the rows and only post the three field values (ie don't make SQL read the excel sheet just yet), add in a datatable, load into that and then foreach datarow count rows.

Where and how are you using this ?

There is an example similar to yours in : http://www.mssqltips.com/sqlservertip/2176/dynamically-find-where-table-data-starts-in-excel-using-ssis/
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1400 total points
ID: 39676532
Ummm, just on reflection, if you are excluding the first row, then how will it be getting its field names ? Try HDR=NO and then use F1,F2,F3 or in the select use F1 as ID, F2 as Firstname etc...
0
 

Author Closing Comment

by:dejandejanovic
ID: 39678833
@Mark_Wills,
OPENROWSET works perfect for my solutions. Thanks again, to all posts.
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question