locked
Convert Interval of cells in the same line in Excel to different record in SQL Table RRS feed

  • Question

  • I have an Excel sheet in with a certain amount of rows. Each row has always the same amount of columns and the amount of columns is always a multiple of 12 (12, 24, 36, 48...).

    For each row I want to take, starting from the A column, group of 12 columns and put in a SQL database table record. Each subset represent data for a year, 12 months.

    Therefore if I have let's say 48 columns (4 years data), for each row I have to put 4 lines (48/12) in the SQL table.

    I am not asking for a solution but just for a hint on which tool/approach do you suggest for accomplish this task, especially because in Excel columns are indicated by letters and not numbers and I do not know how to specify "take the columns from 0 to 12, than from 12 to 24...and so on".

    I have been suggested to use SSIS or the Integration Wizard but before starting with using this tools I would like to know if I am taking the right path. Of course any advice on how to tackle this issue is welcome. Thanks

    Wednesday, February 26, 2014 5:02 PM

Answers

  • You can use SSIS to achieve this. 

    Load all data into a temporary table and then use a stored procedure/T-SQL to load data of 12 columns each into destination table.

    OR

    Use OpenRowset property of Excel Source to specify what column data you want to pick. Here you have to use 4 Excel Source for 4 year of data

    SSIS: Read and Export Excel data from nth Row




    Vikash Kumar Singh || www.singhvikash.in

    • Marked as answer by Trinakriae Wednesday, March 5, 2014 9:30 AM
    Wednesday, February 26, 2014 6:40 PM

All replies

  • A simple SQL query would do, or you can use SSIS to extract the data to an interim table.

    Then use SQL. Are you proficient it SQL?

    You can even experiment with the Data Export Import Wizard grabbing the data to SQL then manipulating on it. The result of the Wizard can be saved as a package for re-use/analysis or modifications in BIDS/SSDT-BI


    Arthur My Blog

    Wednesday, February 26, 2014 5:15 PM
  • Thansk for your reply! I have been using SQL for years so I can say I am familiar. In the past I used Interop library with C# and VB.NET to process an Excel sheet , get its data and write them in a SQL database but I've been asked to use this method as last resort.

    I have never used SQL to query an Excel file, I have used SQL queries from the SQL query editor in Excel to get data from SQL database and then write them in an Excel sheet  but not the other way around. Are you talking about this VB script approach as in this article? http://technet.microsoft.com/en-us/library/ee692882.aspx. Thanks

    Wednesday, February 26, 2014 6:34 PM
  • You can use SSIS to achieve this. 

    Load all data into a temporary table and then use a stored procedure/T-SQL to load data of 12 columns each into destination table.

    OR

    Use OpenRowset property of Excel Source to specify what column data you want to pick. Here you have to use 4 Excel Source for 4 year of data

    SSIS: Read and Export Excel data from nth Row




    Vikash Kumar Singh || www.singhvikash.in

    • Marked as answer by Trinakriae Wednesday, March 5, 2014 9:30 AM
    Wednesday, February 26, 2014 6:40 PM
  • Thansk for your reply! I have been using SQL for years so I can say I am familiar. In the past I used Interop library with C# and VB.NET to process an Excel sheet , get its data and write them in a SQL database but I've been asked to use this method as last resort.

    I have never used SQL to query an Excel file, I have used SQL queries from the SQL query editor in Excel to get data from SQL database and then write them in an Excel sheet  but not the other way around. Are you talking about this VB script approach as in this article? http://technet.microsoft.com/en-us/library/ee692882.aspx. Thanks

    You can use OPENROWSET method in SQL to access data from Excel

    see

    http://support.microsoft.com/kb/321686

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Mike Yin Wednesday, March 5, 2014 8:05 AM
    Wednesday, February 26, 2014 7:07 PM
  • Thanks, I had a look at the OpenRowSet property but at this point I guess the easiest approach is, as suggested, to load the whole Excel file in a temp table and then use SQL to create the definite table. Since I do not have access to the sever itself but just to the database (remotely by using SSMS), I cannto use SSIS therefore the best option to import datta from Excel to SQL  is described here http://support.microsoft.com/kb/321686/en-us
    • Edited by Trinakriae Thursday, February 27, 2014 9:40 AM
    • Proposed as answer by Mike Yin Wednesday, March 5, 2014 8:05 AM
    Thursday, February 27, 2014 9:39 AM