SqlBulkCopy Tutorial - Knowledge Base (KB)
32 results in tag: excel
How does SqlBulkCopy retain row order?
I'm exporting data programatically from Excel to SQL Server 2005 using SqlBulkCopy. It works great, the only problem I have is that it doesn't preserve the row sequence i have in Excel file. I don't have a column to order by, I just want the records to be...
asked by roman m
Strings longer than 255 characters are not copied by SqlBulkCopy.
I am trying to copy a large Excel spreadsheet into the SQL Server database. I am opening an OldDbConnection to the Excel spreadsheet and reading everything from the [Sheet1$]. Then, I am using the OleDbCommand to get a IDataReader with the spreadsheet dat...
asked by hpatel298
Using the WHERE clause to read an Excel file with blank rows causes an unknown error.
So clients upload Excel files to us and we have a windows service that periodically grabs new files and bulk-loads the contents to our SQL server like so:...string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Ex...
asked by Alex Polkhovsky
SQL Bulk Copy to SQL Server with Data Modifications in Excel
I am reading up on ...SQLBulkCopy... and would like to use it to import thousands of rows from an Excel document to SQL Server. I keep reading articles that do it directly without modifying the data. I need to do some modifications and validations on the...
asked by Mike Flynn
The type or namespace name 'SqlBulkCopy' could not be found
can someone help me please to fix that error. this is my code : ...using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data.SqlClient; using System.Data.OleDb; using System.Da...
asked by Nejthe
sqlbulkcopy from Excel via ACE.OLEDB truncates text to 255 chars
Pretty straight-forward import using SqlBulkCopy:...string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\""; using (OleDbConnection excelConnection = new OleDb...
asked by Alex Polkhovsky
SQL bulk insert does not insert values properly into table
I have an Excel file(.xlsx) and I am trying to upload the content of the file into a Sql server table. Iam using the SQL bulk copy to bulk insert the data. The data gets inserted into the table but I find that the data is not inserted properly. ...Here is...
asked by Cdeez
how to search for multiple values in one excel sheet and copy it to another sheet
Good day...I have a huge problem...I have an online store and need to update products and prices I have an excel sheet with all of the current products online and recieved a new pricelist Now the problem is that Im dealing with over 5000 products and I ca...
asked by Francois Vosloo
Using SQL Bulk Copy to fill a database table
I'm working on an web app using ASP.NET MVC4. I have an Excel file with several columns (some of them are useless) and I'd like to export the data to a related table by uploading the file....One of my superiors told me to use SQL Bulk Copy to do that. I s...
asked by Traffy
SQL Bulk Copy "The given value of type String from the data source cannot be converted to type datetime of the specified target column" using ASP.NET
I'm working on an ASP.NET MVC4 projet and I'm trying to export data from an xlsx file (Excel 2010 file) to my database by using SQL Bulk Copy. My Excel file contains only 2 columns : the first contains numbers (from 1 to 25) and the second contains charac...
asked by Traffy
Excel SqlBulkCopy works differently on local pc, and on application server?
My application truncates a database table and fills it with the rows from an excel....Using an excel file (2000 rows), below upload code inserts all the rows to database completely in my local pc (my development environment) and it did in the server too, ...
asked by HOY
SqlBulkCopy dbnull.value error
I am trying to insert rows from an excel file into sql server 2000 using bulkcopy. In the table there is a 'rowguid' field and its default value is set to (newid()) and cannot except null values. Also RowGUID is set to "Yes". ...In my code i remove the co...
asked by Lucky Luke2
SqlBulkCopy inserting null instead of Double
I'm trying to import excel files to my website then save its content in the sql server my problem with sqlbulkcopy when it fill the database some column filled with wrong values which is NULL ! while the column have a not null double values ! ...here is ...
asked by seetah
set a constant(same) time stamp for multiple data base rows
I'm creating an asp.net mvc4 application, that can write data into database tables from excel sheets(using sqlbulkcopy column mapping). when we select excel sheet and submit, it is writing data into database....Now I want to set file uploaded time as time...
asked by Chathz
Excel to Sql adding certain rows to sql server
cmd.CommandText = (Convert.ToString("SELECT * From [") & excelSheet) & "] Order By" & columnCompany & "OFFSET 0 ROWS FETCH NEXT 100000 ROWS ONLY " ...I am trying to map data form excel to an sql table but the excel table has a lot of records in it, so I ...
asked by Richard
C# - NullReferenceException while moving data from Excel to SQL Server
I am developing a web application in which i have to import the data in SQL Server from given Excel files using C# and ASP.NET MVC. For this purpose I followed ...this... article. So I used ...ExcelDataReader... to read the Excel files. Furthermore I have...
asked by Mustafa Sattar
string filed is truncated on SqlBulkCopy insert
I use SqlBulkCopy to insert excel data to table....One of the fields is ntext on the destination table but when the data is inserted the data is truncated after 255 chars....using (OleDbConnection conExcel = new OleDbConnection(connstring)) ...
asked by eyalb
Importing user-friendly Excel sheet into a SQL Server table that has many foreign keys
I have an Excel sheet that is populated by HR employee with thousands of client records it looks like this one:...User Friendly Excel Sheet Example Screenshot...My client's SQL Server table schema looks like this...CREATE TABLE [dbo].[Clients] ( [ID] ...
asked by Ahmed TalǮat
Update previous record when i import from excel to sql database
I need help with this code. I am trying to import into SQL database from excel. A column is set to unique so that if it discovers duplicate data it ignores it. In this case instead of ignoring it I want it to update the data if there is an update. Here is...
asked by Umar E. Shaibu
script task bulkcopy from Excel to Sql Server 2008. Missing row
I am currently loading data from an Excel worksheet which has a header to a table in SQL server. I am using a script task in SSIS. Everything is working fine except that it doesn't load the first row after the header. If I move the row to the bottom of th...
asked by Bluffer
Page 1 of 2