SQL Server - Import excel data failure

Hi All,

I have an excel file having 10 columns, when I tried to import the data into one table I am getting truncation error. This is due to one column which is having comments(contains special characters also).

When I remove that column and importing it is working fine, but tried to import with comments column getting error.
Please advise how can I import the excel data into database.

Thanks in Advance.

Error Details:

Error 0xc020901c: Data Flow Task 1: There was an error with output column "Comments" (30) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task 1: The "output column "Comments" (30)" failed because truncation occurred, and the truncation row disposition on "output column "Comments" (30)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)

HI

follow these steps.....it might help you

1. Copy your Excel data into text file - it will come as tab-delimited file.

2. Create target table in SQL Server.

3. Run BCP IN in DOS prompt, its format should be something like this (without line breaks):

bcp db_name.schema_name.table_name in c:\your_path\your_text_file_name.txt -c -S server_name -T -F 2 -e c:\your_output_error_file_name.txt

-F 2 means to skip column headers

-T means trusted connection

refer the link
http://www.sqlservercentral.com/Forums/Topic326846-149-1.aspx#bm327013
http://stackoverflow.com/questions/375991/import-excel-spreadsheet-columns-into-sql-server-database

1. The error is probably due to a strange character in your data and redirecting the output will allow you to find it.    Have you checked the length of your source data in Excel? Are you inserting into char or varchar column? If char are you trimming the data to insure you do not have trailing spaces making it too long?

2. also check- If you import the excel file do you have the correct column names?

try this alternative code -

Using SqlBulkCopy calss object you can transfer excel data to DataBase-

protected void btnSend_Click(object sender, EventArgs e)
{
 String strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";

//file upload path
string path = "file path";

//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);

excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

//Give your Destination table name

sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
}
 

try this code and let me know.