-1

DBMS - SQL server 2008 r2 with management studio (GUI) Excel file - Columns exactly like the columns in destination table

How do I load the data from the excel table into the sql table? Are there any potential problems in this way of doing things ? For example, a column does not allow null, but the excel has a null.

sequel.learner
  • 3,421
  • 7
  • 22
  • 24
  • Have you reviewed the [many similar questions](http://stackoverflow.com/search?q=sql+server+load+excel+data) that have already been asked? – Pondlife Mar 15 '13 at 16:21
  • A better answer with pictures - http://stackoverflow.com/questions/3474137/how-to-export-data-from-excel-spreadsheet-to-sql-server-2008-table – sequel.learner Mar 16 '13 at 01:14
  • Consider closing the Question. As it is **[Duplicate](http://stackoverflow.com/questions/3474137/how-to-export-data-from-excel-spreadsheet-to-sql-server-2008-table).** – Prahalad Gaggar Apr 10 '13 at 07:12

2 Answers2

2

The simpliest way to import XLS data directly into SQL is to use the Import Wizard.

You can do this either by having the import wizard create the table for you when the wizard runs, alternatively, you could create the table before hand and then use the wizard and use that table as your target table.

I prefer the latter method as I like to control my table creation with my desired datatypes.

To find the Import Wizard, simply right-click on the Database name, Tasks -> Import Data

SQLGuru
  • 1,099
  • 5
  • 14
0

You can also use an Excel Data Source in SQL Server Integration Services. Some caveats though:

  1. If you are running in a 64-bit OS, make sure your package properties are set to disable the 64-bit runtime, or the import from Excel 2003-2007 or will fail. You will get a connection error.
  2. Take the time to use the advanced editor to shrink your columns to the size you need, or every field will come out as nvarchar(255) by default, which is a waste of space. If you are mapping to an existing table, you may need to convert the data types as well.

The advantage of this approach is that you can re-use it more easily, though of course you can save the package the wizard creates as well.

Joey Morgan
Programmer/Analyst Principal I
WellPoint Medicaid Business Unit

Joseph Morgan
  • 163
  • 1
  • 9