Thanks
@David Gerard. My company is looking at getting ADF setup. As for the compression, I fully understand that and wasn't surprised by the 3 minutes to read the excel. It was then the 20 minutes to only do 3000 rows to SQL that was surprisingly slow, since the data was already "in memory" as an array within the flow. But sounds like that's do to the lack of bulk operations. I'm stuck in this case with using an excel file, because I need people to be able to actively update the file, then at night I sync the days work back to SQL.
In this case the reading excel into an array and creating a csv table, and uploading the CSV file into SQL works very well. There is a 1000 limit on the number of insert into values within SQL, you could by-pass this with:
INSERT INTO TableName( Colum1) VALUES (1)
INSERT INTO TableName( Colum1) VALUES (2)
INSERT INTO TableName( Colum1) VALUES (3)
This is the column splitter code i'm using, combined with azure's string_split for converting line breaks (char(10)) into new rows
declare @data as nvarchar(max)
set @data = (select top 1 column1 from table)
select [dbo].[UFN_SEPARATES_COLUMNS](value,1,',') Column1, dbo].[UFN_SEPARATES_COLUMNS](value,2,',') from sting_split(@data,char(10))
function code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[UFN_SEPARATES_COLUMNS](
@TEXT varchar(8000)
,@COLUMN tinyint
,@SEPARATOR char(1)
)RETURNS varchar(8000)
AS
BEGIN
DECLARE @POS_START int = 1
DECLARE @POS_END int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
WHILE (@COLUMN >1 AND @POS_END> 0)
BEGIN
SET @POS_START = @POS_END + 1
SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
SET @COLUMN = @COLUMN - 1
END
IF @COLUMN > 1 SET @POS_START = LEN(@TEXT) + 1
IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1
RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
END
------------------------------
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
------------------------------
Original Message:
Sent: Aug 14, 2020 06:49 AM
From: David Gerard
Subject: Insert Row (V2) To SQL Table
@Sam Duval, just to add up on what others have said, Excel workbooks store data in a compressed format. When pulling data from an Excel workbook, the workbook is first uncompressed and that is certainly what takes the most time, and usually a lot of memory. Passing by a CSV is better as the format is uncompressed and the data is readily available. Also, by definition, a CSV file will be bigger than an Excel workbook.
@Will Page's suggestion of using Azure Data Factory is certainly the best way to go. ADF has some templates available to get you started with a simple Copy Pipeline and easy connection with your Azure SQL Database (and always store your connection credential using Azure Key Vault ;) ).
All the best
------------------------------
David Gerard
Business Intelligence Consultant
Kampala
Original Message:
Sent: Aug 12, 2020 01:13 PM
From: Sam Duval
Subject: Insert Row (V2) To SQL Table
Is there a faster way to get rows out of excel and into a SQL table (or viceversa) I have 8,000 rows by 9 columns and it took 20 minutes to "import" 3,000 rows.
My flow steps are
Excel:
List Rows in a table (takes about 3 minutes)
Apply to each value from the list rows object above
Insert row for SQL
my SQLtable is in an azure sql database
------------------------------
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
------------------------------