locked
Quickly: Importing Data from a file? RRS feed

  • Question

  • Hello,

    Thanks for reviewing my question.  I would like to know if its possible in SQL to programmatically import a file (excel or .csv) into its own table to an existing table?

    We have a website and I would like to offer the ability to upload a file to the website which will then get imported into the SQL server.

    Or should I write an Excel add that imports?

    Many Thanks

    Peter

    Monday, April 11, 2011 10:50 PM

Answers

  • Hi Peter If I understand correctly you would want to import a file to its own table or an existing table, What version of SQL Server are you running? I have not written Excel add that imports

    But You can use SSIS  to import a file to its own table or an existing table, Does the columns contained in the flat file change periodically? You can also import data into a staging table scrub the data and load into the main table.

    Thanks

    Jay

    Tuesday, April 12, 2011 12:20 AM
  • You can use SSIS packages , BULK INSERT , SQL Server linked servers , SQL Server distributed queries and more. More info here http://support.microsoft.com/kb/321686

    Thanks, Leks
    Tuesday, April 12, 2011 2:55 AM
    Answerer

All replies

  • Hi Peter If I understand correctly you would want to import a file to its own table or an existing table, What version of SQL Server are you running? I have not written Excel add that imports

    But You can use SSIS  to import a file to its own table or an existing table, Does the columns contained in the flat file change periodically? You can also import data into a staging table scrub the data and load into the main table.

    Thanks

    Jay

    Tuesday, April 12, 2011 12:20 AM
  • You can use SSIS packages , BULK INSERT , SQL Server linked servers , SQL Server distributed queries and more. More info here http://support.microsoft.com/kb/321686

    Thanks, Leks
    Tuesday, April 12, 2011 2:55 AM
    Answerer
  • Thanks for the help.  I am using SQL 2008.  The columns names would not change.

     

    Quick question: What is SSIS?

    Thanks

    Peter

    Tuesday, April 12, 2011 1:21 PM
  • Hi Peter, SSIS is SQL Server Integration Services. Its a full blown ETL(Extract,Transform, Load) which can be used for many things.

    Here is more information

    http://msdn.microsoft.com/en-us/library/ms141026.aspx

    Thanks

    Tuesday, April 12, 2011 1:29 PM
  • Thanks for the info.

    What about using OPENROWSET to import data?  Is one method preferred than the other?

    Peter

    Tuesday, April 12, 2011 5:32 PM