[Solved] PYTHON – EXCEL (matrix)

Question

Here are a few ways I might go about this:

Option 1

Use Excel to get your data into the correct format – you’ll be able to do this with macros/VBA or even just formulas. Once this is in place there are many ways to import directly into SQL Server, e.g., right-click the database > ‘Tasks’ > ‘Import Data…’.

Option 2

If it’s going to be a regular thing, and you’ll always be getting the data in this format, or if the dataset is too large for Option 1, I’d look into using SSIS (SQL Server Integration Services). Within SSIS you could set up either a Data Flow Task with a Script Component Source or a Script Task to transform the data and pull it into SQL Server (look into using Microsoft.Office.Interop.Excel to interact with Excel in C#/VB.net).

Accepted Answer

Here are a few ways I might go about this:

Option 1

Use Excel to get your data into the correct format – you’ll be able to do this with macros/VBA or even just formulas. Once this is in place there are many ways to import directly into SQL Server, e.g., right-click the database > ‘Tasks’ > ‘Import Data…’.

Option 2

If it’s going to be a regular thing, and you’ll always be getting the data in this format, or if the dataset is too large for Option 1, I’d look into using SSIS (SQL Server Integration Services). Within SSIS you could set up either a Data Flow Task with a Script Component Source or a Script Task to transform the data and pull it into SQL Server (look into using Microsoft.Office.Interop.Excel to interact with Excel in C#/VB.net).