How To Transfer a Table From SQL Server To Excel

5 Dec by dbtut

How To Transfer a Table From SQL Server To Excel

 

I explained how to transfer data from SQL Server database to SQL Server database.

In this article I will describe how to transfer from SQL Server database to Excel.

I think it would be more useful to explain it directly through the example.

In our example, we’ll export the “Person.Adress” table in the AdventureWorks2014 table to Excel.

Right click on the AdventureWorks2014 database and click Task-> ExportData. We select Microsoft OLE DB Provider for SQL Server from the screen as follows.

 

After making our selection, a screen appears as follows. This screen is our Data Source. That is, the database that contains the table that needs to be transferred.

Because of we clicked Export on AdventureWorks2014t, it brought AdventureWorks2014 directly to the Database section as follows. Click Next to continue.

 

We click Browse in the Excel file path section and we select the path to save excel. We click on open by giving a name to our excel.

I chose Microsoft Excel 2007 from the Excel Version section. You can decide which option to choose. Click Next to proceed.

On the next screen, we proceed by selecting “Copy data from one or more tables or views”.

You can also transfer with your own query by selecting “write a query to specify the data transfer”.

 

We select the Person.Adress table on the incoming screen, and we see that ‘Adress’ occurs automatically in the column next to it.

This is the name of the worksheet of Excel. You can determine yourself. I’m moving on without making any changes.

Click Next and Next and you will see the following error.

The Microsoft Jet database engine could not find the object ‘objectname’. Make sure the object exists and that you spell its name and the path name correctly.

 

The reason for this error is due to the LongBinary type. If you ignore the LongBinary type column from the Edit Mappings as follows, the transfer will be completed.

Leave a Reply

Your email address will not be published. Required fields are marked *