how can we trasfer data from an MS Excel File whose size is around 800KB and transfer it to SQL Server200.?

Question

i have been trying to do it through SQL Import Exprt Wizard,but when i try to transfer it it gives an error as"TOO many fileds defined"

Answer 1

The single biggest problem when converting an Excel file to any database is the field data integrity. First you should review the data in the Excel file and groom the data - such that date fields only contain valid dates and number fields only contain valid numbers. People putting information into an Excel file will typically put "extra information" in a cell - which then becomes a problem when converting. Also, clean up the row headers - which will become the field names in the database. Make sure they're unique - and all extraneous spaces should be removed from the field names (e.g., "First Name" should be changed to "FirstName" - no space). Once the data is cleaned up - the data conversion should be successful. I would also use an intermediate step of converting to an Access database first - then you can use the up-sizing feature in Access and easily convert the data into a SQL Server database. Good luck!

Answer 2

In EXCEL, export (SAVE AS) the data to a CSV (Comma Separated Value) File, which is a text file that is formatted by a comma usually, or other delimeters such as a quote " or sometimes a space or tab.



Then IMPORT the data into your MySQL or *SQL database via phpMyadmin or similar software.

Answer 3

in protecting with the question on importing the Excel 2007 records to sq. Server 2005, i prefer to provide you the techniques as follows: a million. Connection string project: Please examine if you're efficient to connect both Excel 2007 and sq. Server 2005 because the options source effectively. The excelConnection seems to locate as you presented. Then that you will be able to also upload "HDR=particular" to point that the usual row includes columnnames, now not information. "HDR=No;" exhibits the different. this can prevent the first row at a loss for words even as importing to the sq. tables. 2. Please try your ODBC sq. Server connection string only so the relationship to sq. Server is nice. except the ODBC, that you are able to also use SqlConnection and execute the SqlCommand merchandise with the statement that is composed of the excelConnection on your VB.internet code, yet now not only combine the finished in a unmarried sq. statement. the following article exhibits you the acceptable way: Import Excel sheet into table 3. except the above procedures, that you'll do not ignore to apply SqlBulkCopy classification to import. the following article shows you the representation: Import Excel Spreadsheet options into sq. Server Database table utilising SqlBulkCopy 4. there's a KB article in MSDN on the region. in spite of the truth that the object isn't utilized for sq. Server 2005, that it is also conceivable to be educated on sq. Server records Transformation amenities (DTS), Microsoft sq. Server 2005 Integration amenities (SSIS), sq. Server linked servers and sq. Server dispensed queries. they're major on your paintings. counsel on the thanks to import options from Excel to sq. Server desire that ought to help you.