Link to home
Start Free TrialLog in
Avatar of ToString1
ToString1

asked on

Import data from MS Excel into Sql express

I have an ms excel spreadsheet with two columns


Col1                 Col 2
House Title          Grove street, 10478

I want to import the data from excel into a table in Microsoft Sql Express.

My table has four columns

ID                    int              (auto increment)
Title                varhcar(255)
Street            varchar(255)
ZIP                 Varchar(50)

I want to import from the excel spreadsheet into the table so the above excel line would be imported as

Title       "House Title"
Street    "Grove Sreet"
Zip        "10478"





ASKER CERTIFIED SOLUTION
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ToString1
ToString1

ASKER

Thanks guys I will try your solutions later today and award points.   In meetings for hours!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
HI Guys

Sorry for delay but I am getting the error below if I try suggestion from NerdsOfTech

".................The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object......."
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am still getting the error but I do this

SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book1.xls','SELECT * FROM [Sheet1$]')

It works fine
Maybe I could just import everything into one table and then use t-sql just on a temporary database table?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
post your reflected SQL column substitutions