(urgent)Insert data from a excel Spread sheet to SQl server

Hi,

I have a spread sheet which has 4 columns called cusip, Chartheader, growthdates and NAV.. and i also have the same number of columns in the Sql server... and I want to add another column called Rownumber and set it as int indentity... and when i try to import the data to sql server i am getting this error called

Received an invalid column length from the bcp client for colid 1.

How should i fix it.

Regards

Karen

usual way is:

1). Import data from Excel sheet;

2). Change the column names, format data if necessary;

3). Add an identity column.

|||

Without the int indentity column the import works fine... The reason i want to add the row number to the table is becauase after i transfer it to a staging database i am scrubbing the data and then inserting it to another database. But in the process of transferring data from the staging database to the actual databse the dates get jumbled up and is not in the same order that it was in the staging database.. and because of that my data in a particular sub report is not right.. this is what i am doing... in my asp.net program

If (flGrowth10K.PostedFile.FileName.ToLower.EndsWith(".xls")Or flGrowth10K.PostedFile.FileName.ToLower.EndsWith(".csv"))Then flGrowth10K.PostedFile.SaveAs(location6)' Connection String to Excel WorkbookTry Dim excelConnectionStringAs String =String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location6)'"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=location;Extended Properties=Excel 8.0" ' Create Connection to Excel Workbook Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select Cusip,FundName,ChartHeader,GrowthDates,GrowthNAV FROM [Sheet1$]", connection) connection.Open()' Create DbDataReader to Data Worksheet Using drAs Data.Common.DbDataReader = command.ExecuteReader()' SQL Server Connection StringDim sqlConnectionStringAs String = System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION)'"Data Source=.; Initial Catalog=Test;Integrated Security=True"Dim myconnectionAs New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))Dim queryAs String ="Truncate Table Growth" myconnection.Open()Dim cmdAs New SqlCommand(query, myconnection) cmd.CommandType = CommandType.Text cmd.ExecuteScalar() myconnection.Close()' Bulk Copy to SQL Server Using bulkCopyAs SqlBulkCopy =New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName ="Growth" bulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End UsingCatch exAs ExceptionThrow exEnd Try End If Try oImportFunds =New ImportFunds(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))With oImportFunds .PeriodID = ddlPeriod.SelectedItem.Value .save()End With Catch exAs Exception lblErrorMsg.Text ="Error: Import Failed" + ex.Message lblErrorMsg.Visible =True End Try

I was wondering if i have to add Rownumber to the select clause in the oledbstatement too...

any help will be appreciated

Regards

Karen

|||

I am sorry that I didn't know you did it in your application. The error happen, I think, because your excel columns don't match your table's columns (Your table column 1 is an identity integer data now. Your application tried to insert Cusipinto this first column). If I was you, I will let the program work first, then mannually add the identity column after data imported.

|||

Thanks for your suggestion... But i cannot do that cause everytime the user updates the tables using the spread sheets i cannot go in add the row number. The reason is we have given full access to that co. to use our database. .. is there a way to specify in the oledb statement that the first column is an int identity... something in the select statement. right now i dont have the Row number in my database... so it works fine.. but on some imports things get imported as it is in the staging database and some some entries get jumbled up.

Regards

Karen

|||

Just curious(I doubt it will work) to see what happens if you change your SELECT to "Select 0, Cusip,FundName,ChartHeader,GrowthDates,GrowthNAV FROM [Sheet1$]"

|||

che3358,

I think so it worked the way u told me to do...any ways let me update my dbase and try running it from a different server...

and will let u know the results.//

Regards

Karen

|||

here's a quick and dirty way to do it, suitable for a one-time solution:

copy the table definition (less the identify and timestamp columns) and create a new table, temp_whatever

Make sure the column order in the table matches the column order in the spreadsheet.

Open the table in sql server manager.

Copy the data in the spreadsheet, minus column headings.

Paste it into the temp table.

use this statement, adjusting for table and column names, and ignoring all identify and timestamp columns:

insert into real_table (col1name, col2name, col3name,...) select col1name, col2name, col3name, ... from temp_whatever.

when done, drop temp_whatever.

:)