locked
Export data from excel into mssql RRS feed

  • Question

  • my objective is to export a rather large file, many rows with many items in each row, from an excel spreadsheet into mssql. The first row will contain labels for that content in each column.

    The one question I have other than that method to move information from excel into mssql is that will it be necessary to 'prep' the mssql to recieve the data? Some information will be just plain text, some data will be dates while other data will be numeric values.

    This is no doubt a two part inquiry. One being what 'prep work' is necessary for the sql database to recieve the data (ie., like naming the instance, defining the rows) or does the excel spreadsheet takes care of these details?

    The data file in the excel spreadsheet is 58,000+ lines with about 9 items in each row.

     

    Thursday, November 3, 2011 11:35 PM

Answers

  • Hi rbbenson,

    For your requirement, the simple way is to use the 'Export/import wizard' in SQL Server Management Studio to import the data from excel into table.

    Please refer to the samples from the online articles below:

    1. http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

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

     

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Naomi N Friday, November 4, 2011 6:00 PM
    • Marked as answer by Peja Tao Monday, November 14, 2011 3:24 AM
    Friday, November 4, 2011 6:15 AM
  • HI rbbenson !

    Here are few approaches you may use depending on your case;

    1) You may create a DTS Package , define the Source(Excel or csv) and Destination Connection(s) (MS SQL Server) and identify the transform from Source to Destination.
     
    2) You may create the SSIS Package and use Transform Table utility to copy data from Source to Destination DB (MS SSIS)

    3) Use Import/Export Wizard in MS SQL Server and Import Table to MS SQL Server.
     
    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham
     
     

    • Marked as answer by Peja Tao Monday, November 14, 2011 3:24 AM
    Friday, November 4, 2011 9:18 AM
    Answerer

All replies

  • Hi rbbenson,

    For your requirement, the simple way is to use the 'Export/import wizard' in SQL Server Management Studio to import the data from excel into table.

    Please refer to the samples from the online articles below:

    1. http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

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

     

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Naomi N Friday, November 4, 2011 6:00 PM
    • Marked as answer by Peja Tao Monday, November 14, 2011 3:24 AM
    Friday, November 4, 2011 6:15 AM
  • HI rbbenson !

    Here are few approaches you may use depending on your case;

    1) You may create a DTS Package , define the Source(Excel or csv) and Destination Connection(s) (MS SQL Server) and identify the transform from Source to Destination.
     
    2) You may create the SSIS Package and use Transform Table utility to copy data from Source to Destination DB (MS SSIS)

    3) Use Import/Export Wizard in MS SQL Server and Import Table to MS SQL Server.
     
    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham
     
     

    • Marked as answer by Peja Tao Monday, November 14, 2011 3:24 AM
    Friday, November 4, 2011 9:18 AM
    Answerer
  • Thanks much, will give it a try.

    One principal question still remains is the 'naming of the instance' as well as defining the contents of the database as in date define fields, numeric define fields, text define fields.

    Will all these 'things' get taken care of prior to moving/transferring date into the database as well as naming the instance of this database?

     


    • Edited by rbbenson Friday, November 4, 2011 5:40 PM
    Friday, November 4, 2011 5:39 PM