I've created a new database and restored the database from a backup. I'm wanting to import raw data from an excel spreadsheet into the tables of the new database.

When I right click on the database, and hover over tasks the import/ export data menu options are not in the menu.The menu items ends at 'Generate Scripts'.

I'm connected on a local instance to a registered server.

Can anyone advise?

hi,

Lee Rivers wrote:

I've created a new database and restored the database from a backup. I'm wanting to import raw data from an excel spreadsheet into the tables of the new database. When I right click on the database, and hover over tasks the import/ export data menu options are not in the menu.The menu items ends at 'Generate Scripts'. I'm connected on a local instance to a registered server. Can anyone advise?

SQLExpress does not provide the Integration Services features you require, nor does SSMSE include them as management features...

try having a look at http://www.databasejournal.com/features/mssql/article.php/10894_3085211_3 where a linked server is defined to an Excel datasource you can later use to INSERT .. SELECT FROM ExcelDataSource to import data into a SQL Server database..

regards

Thanks for the reply.

The link regarding Four-Part Naming was helpful in showing how to use direct reference.

There were a couple factors that I discovered.

1. Import/Export is not available in the SQL Express version.

2. After exporting the .txt file from the excel spreadsheet via File.. Save As ...(Tab Delimited) and into a file to import into a database table, I discovered the bcp utility. Bulk copy utility copies data between an instance of Microsoft SQL Server from a data file in a user-specified format. In my case, I imported from .txt format or Tab Delimited format.

I used a command prompt and the proper syntax using bcp to import the text file into the database table.

bcp [DataBaseName].[fileType].[TableName] in [FileLocation] -T -c -S ServerName

so the command looked like this:

bcp DatabaseName.dbo.TableName in C:\import\TextFileName.txt -T -c -S ServerName\SQLEXPRESS

In order to use bcp you must configure SQL Server to allow remote connection because bcp uses a remote connection to import data to the database table. Please see the following reference to configure SQL Server Express:

http://support.microsoft.com/kb/914277

This worked for me. I hope this will be helpful to others discovering both bcp utility and SQLServer2005 Express.

Thank you so much the problem has been solved.

 -Regards

-Lee

There is a program provided with SQL 2005 Express.

Here is the shortcut   "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"

It is an import utility that includes Access, Excel, and many more.  Just create a shortcut and the program runs alone.