Post reply

Is there any way to import data from EXCEL to SQL Server via BCP?

  • March 31, 2011 at 4:37 am

    #237738

    Hi everyone?

    Is there any way to import data from EXCEL to SQL Server via BCP?

    Excel Version: office 2010

    SQL Server Version: Sql Server 2008 R2.

    I was thinking that we can use following way to get data from EXCEL, but it throw various messages on different machine. I also tried many way to fingure out it. but in vain. Seems that BCP can do it. is there any body can share some sample for it.

    SELECT *

    FROM

    OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\FiscalCutoverDates.xlsx;

    Extended Properties=Excel 12.0')...[Sheet1$]

    BTW, I can use DTS wizard to import data into SQL Server, is there any method or SP can implement this import action by using it? Really appreciated if anyone can share something with me. Thanks a lot.

    Thanks

    Lindsay

  • March 31, 2011 at 7:46 am

    #1305974

    Is SSIS out of question here? You could create a SSIS package and deploy it as an SQL Agent job.

    Check out this link: http://support.microsoft.com/kb/321686

  • March 31, 2011 at 8:08 am

    #1305991

    Thanks for your quick response runaldo^_^

    Basically, I know how to create package or import excel data via import export wizard. I'm seeking a way to import data via command line. it can be BCP, or DTSWizard if it is possible. Not sure whether we can find this way...

    I know we can use following bcp command line to import data from notepad, is there similar anyway to import data from Excel?

    EXEC master..xp_cmdshell 'bcp Test.dbo.Sheet1 in D:\share\DataIm.txt -c -S ADMIN-PC -T -F 2 -e'

    Thanks

    Lindsay

  • March 31, 2011 at 5:59 pm

    #1306263

    BCP is not the tool for importing EXCEL spreadsheets. It works on common text files. However you could save your spreadsheet as a CSV and import it using BCP. SSIS is much better choice for importing native EXCEL formats.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 31, 2011 at 7:22 pm

    #1306273

    Thanks for your response The Dixie Flatline.

    I know SSIS will be good tool to do it, but seems that we have to create a package and then run it. I hope I can use one command line to import excel data. this command like can include all the info it will use. like provider, excel path, worksheet name, and target table in DB... Does SSIS have this script?

    Thanks

    Lindsay

  • April 1, 2011 at 3:29 am

    #1306359

    Lindsay - did you check out the link I posted. This link shows different ways of impoting excel data into databases.

  • April 1, 2011 at 6:58 am

    #1306449

    runaldo (4/1/2011)

    Lindsay - did you check out the link I posted. This link shows different ways of impoting excel data into databases.

    Yes, Seems that SSIS and ADO can work on my machine, I have some existing ADO code, I only want to see whether the SSIS has any script to figure out it.

    Thanks

    Lindsay

  • April 1, 2011 at 7:46 am

    #1306481

    OK, I found that we can use DTEXEC with SSIS package command line to import data. Thanks every body:)

    Thanks

    Lindsay

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply