Importing data from Excel file to Tables in database

• vaibhav.a

  SSChasing Mays

  Points: 639

  More actions

  April 20, 2009 at 2:16 am

  #199595

  Hello,

  Please help me,

  I am having having excel file and i want to transfer data from different excel files into multiple tables..........

  First I want to know is this possible to import data from Excel to SQL server 2005.

  And if possible then how can I do this.

  If it is not possible than what is the alternate for this.......

  Please help me out, its urgent....

  Thank you in advance

• hayzer

  Say Hey Kid

  Points: 713

  More actions

  April 20, 2009 at 2:25 am

  #980551

  vaibhav.a (4/20/2009)

  ---

  Hello,

  Please help me,

  I am having having excel file and i want to transfer data from different excel files into multiple tables..........

  First I want to know is this possible to import data from Excel to SQL server 2005.

  And if possible then how can I do this.

  If it is not possible than what is the alternate for this.......

  Please help me out, its urgent....

  Thank you in advance

  Right click the database you will use, go to Task, then Import Data.

  SQL Server Import and Export Wizard will appear..

  Just follow the instructions...

  BR

  Hayzer

• vaibhav.a

  SSChasing Mays

  Points: 639

  More actions

  April 20, 2009 at 3:28 am

  #980572

  i want to perform this task using stored procedure, as I have to insert data in multiple table from multiple excel files at a time...

  plz help me

  thank you in advance

• hayzer

  Say Hey Kid

  Points: 713

  More actions

  April 20, 2009 at 3:33 am

  #980576

  vaibhav.a (4/20/2009)

  ---

  i want to perform this task using stored procedure, as I have to insert data in multiple table from multiple excel files at a time...

  plz help me

  thank you in advance

  you need SSIS on that..

  try this link http://www.accelebrate.com/sql_training/ssis_tutorial.htm

• Vijaya Kadiyala

  SSCrazy Eights

  Points: 9119

  More actions

  April 20, 2009 at 8:46 am

  #980771

  vaibhav.a (4/20/2009)

  ---

  i want to perform this task using stored procedure, as I have to insert data in multiple table from multiple excel files at a time...

  plz help me

  thank you in advance

  There are three steps:

  1) Please store the excel file as .csv.

  2) Getting the excel data into Staging tables using BULK INSERT.

  3) Create the stored procedure to read these tables and insert the data into main tables.

  Please let us know, If you still have any questions.

  Thanks -- Vijaya Kadiyala
  www.dotnetvj.com
  SQL Server Articles For Beginers

  
  

• vaibhav.a

  SSChasing Mays

  Points: 639

  More actions

  April 20, 2009 at 11:09 pm

  #981196

  sorry, but its my pleasure if u help me to write sp for fetching data from an external file .........

  thanks in advance

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  April 20, 2009 at 11:22 pm

  #981204

  You can use the OpenRecordSet or the OpenRowset method to import data from excel.

  "Keep Trying"

• nbhatnagar

  SSC Veteran

  Points: 225

  More actions

  April 20, 2009 at 11:23 pm

  #981206

  Hi Vaibhav..

  You can make a linked server to the excel sheet and then may import data in your tables. See the below sample to create linked server:

  create a virtual linked server and map it to excel

  EXEC sp_addlinkedserver 'VirtualServer',

  @srvproduct = '',

  @provider = 'Microsoft.Jet.OLEDB.4.0',

  @datasrc = 'C:\TestExcelFile.xls',

  @provstr = 'Excel 8.0;'

  GO

  -- map logins to linked server from local server

  EXEC sp_addlinkedsrvlogin 'VirtualServer', 'false'

  GO

  -- Get the spreadsheet data via OpenQuery where spreadsheet name is TestExcelFile

  SELECT * FROM OPENQUERY

  (VirtualServer, 'select * from [TestExcelFile]')