Hi Community,
I need a solution for a problem. I have excel files which needs to be imported into a SQL Database. I tried the below method using SSMS.
When SSMS is connected to the chosen instance of SQL Server, right-click on the desired database and navigate to Tasks > Import data option from the Tasks submenu:
The problem is the date columns will change as the month progresses. It is like an incremental load where new columns should be populated each month & new rows should be added if the dimensions change. The columns will always be the same except the new dates will come in each month. Attached 2 batches sample file attached for reference. Can someone please help me with this? I am unable to get my head around.
I have SSMS 2019 installed and also have access to MS Azure (Synapse) if this can be done there? Thanks a ton in advance,
For Reference:
CREATE TABLE [dbo].[WeeklyData](
[AGG_TYPE] [nvarchar](255) NULL,
[PRODUCT_GROUP] [nvarchar](255) NULL,
[PRODUCT_NAME] [nvarchar](255) NULL,
[PRODUCT_CODE] [nvarchar](255) NULL,
[FORMAT_NAME] [nvarchar](255) NULL,
[STORE_REGION] [nvarchar](255) NULL,
[STORE_NAME] [nvarchar](255) NULL,
[STORE_CODE] [nvarchar](255) NULL,
[03/06/2019_SALES] [float] NULL,
[10/06/2019_SALES] [float] NULL,
[17/06/2019_SALES] [float] NULL,
[24/06/2019_SALES] [float] NULL
) ON [PRIMARY]
GO
July 8, 2021 at 4:12 am
A big part of your problem is that you data is pivoted, and you need to unpivot it to store it. Unpivoting something like this using PowerQuery is child's play. Basically, you'd select all the columns up to [StoreCode], and then you'd UNPIVOT OTHER columns, and that would create a separate record for each date. Then you could just import into your final table.
I haven't used M inside SSIS before, so I'm not 100% sure on how that part would work. You could maybe do all the transforms inside Excel and just output one large file, and then import that into SQL Server, but that's messy.
July 8, 2021 at 5:27 am
Hi @Pietliden,
Thanks. Much appreciated.
I will leave this open for a bit to see if someone has a better solution. I was hoping that there would be some functionality in Azure to easily do this? Not sure!
Hi sqlGuru1day
nice username 🙂
OK. I had the same challenge.
The data was delivered almost similarly in the Excel sheet. But that couldn't be used for an automatic import (ETL).
The data must be rotated from the horizontal to the vertical position. Something like an unpivot.
You can certainly do that with Excel, but unfortunately I don't know that. I did it in the SQL code in SQL Server 2017.
So here is my description:
I asked the supplier to make changes to the Excel so that it can be used for an automatic import (ETL).
The following adjustments would have to be made in the ExcelSheet:
For example, rename the columns with the date [03/06/2019_SALES] to Week1_Sales.
Rename the column [10/06/2019_SALES] to Week2_Sales.
The same for the last 2 weeks.
It should then look like this:
Week1_SALES
Week2_SALES
Week3_SALES
Week4_SALES
Next, a new column "Due date" must be added, which then contains the start of the week. Example 03/06/2019
And that was about it.
The table for the row data import (horizontal):
CREATE TABLE [dbo].[WeeklyData] (
[Due date] [DATETIME] NULL
, [AGG_TYPE] [NVARCHAR](255) NULL
, [PRODUCT_GROUP] [NVARCHAR](255) NULL
, [PRODUCT_NAME] [NVARCHAR](255) NULL
, [PRODUCT_CODE] [NVARCHAR](255) NULL
, [FORMAT_NAME] [NVARCHAR](255) NULL
, [STORE_REGION] [NVARCHAR](255) NULL
, [STORE_NAME] [NVARCHAR](255) NULL
, [STORE_CODE] [FLOAT] NULL
, [Week1_SALES] [FLOAT] NULL
, [Week2_SALES] [FLOAT] NULL
, [Week3_SALES] [FLOAT] NULL
, [Week4_SALES] [FLOAT] NULL
)
The table for the row data import (vertical):
CREATE TABLE [dbo].[WeeklyData_Vertical] (
[AGG_TYPE] [NVARCHAR](255) NULL
, [PRODUCT_GROUP] [NVARCHAR](255) NULL
, [PRODUCT_NAME] [NVARCHAR](255) NULL
, [PRODUCT_CODE] [NVARCHAR](255) NULL
, [FORMAT_NAME] [NVARCHAR](255) NULL
, [STORE_REGION] [NVARCHAR](255) NULL
, [STORE_NAME] [NVARCHAR](255) NULL
, [STORE_CODE] [NUMERIC](15, 3) NULL
, [Week_Date] [DATE] NULL
, [Week_Value] [NUMERIC](15, 3) NULL
)
With the enclosed SQL code you can then import the data into your table.
DECLARE @SQLSTRING AS VARCHAR (MAX) = '';
DECLARE @SourceTable VARCHAR (255) = '[dbo].[WeeklyData]';
DECLARE @TargetSystem VARCHAR (255) = CAST(DB_NAME () AS VARCHAR (255));
DECLARE @TargetTable VARCHAR (255) = '[dbo].[WeeklyData_Vertical]';
SELECT @SQLSTRING = STRING_AGG ( CONVERT(VARCHAR (MAX),'
SELECT [Due date]
, AGG_TYPE
, PRODUCT_GROUP
, PRODUCT_NAME
, PRODUCT_CODE
, FORMAT_NAME
, STORE_REGION
, STORE_NAME
, STORE_CODE
, Week_Date= DATEADD(ww,CONVERT(INT,RIGHT( REPLACE('''+ [name] +''',''_SALES'',''''),1))-1,[Due date])
, Week_Value= [' + name + ']
FROM ' + @SourceTable ),
' UNION ALL ' )
FROM sys.[columns]
WHERE object_id = OBJECT_ID ( @SourceTable ) --'[dbo].[WeeklyData]'
AND [name] LIKE 'Week%_SALES';
SET @SQLSTRING = 'INSERT INTO ' + @TargetTable + ' (
AGG_TYPE
, PRODUCT_GROUP
, PRODUCT_NAME
, PRODUCT_CODE
, FORMAT_NAME
, STORE_REGION
, STORE_NAME
, STORE_CODE
, Week_Date
, Week_Value
) SELECT AGG_TYPE
, PRODUCT_GROUP
, PRODUCT_NAME
, PRODUCT_CODE
, FORMAT_NAME
, STORE_REGION
, STORE_NAME
, STORE_CODE
, Week_Date
, Week_Value
FROM (' + @SQLSTRING + ') AS SUBQUERY';
PRINT @SQLSTRING
EXEC ( @SQLSTRING );
Since I am using the SQL system table, this will probably not work in the AZURE.
For the import / export I would create an ssis package that imports the data from Excel into the SQL server and then exports it to AZURE.
I hope Google English isn't too bad. But better than my english.
Have fun with the code. If you have any questions, please call.
July 12, 2021 at 5:20 pm
I agree with Frank, that the column names for dates should be generic, not including hardcoded dates.
Then load into a temporary table, and use UNPIVOT to load into your permanent table.
August 8, 2021 at 12:02 am
Thanks Frank!
That helped heaps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply