Power Platform Community Forum Thread Details

Using Azure SQL Database, older versions might be possible as well, you'll just have to look up the string_split function or steal an equivalent user defined function from the internet.

In a very round about way yes. There are several blogs if you search google on how to do it exclusively in power automate, but I found it easier to do it in SQL.

Using power automate, get the file contents and dump it into a staging table. From there run some SQL scripts over it to parse it out and clean up the data:

DECLARE @CSVBody VARCHAR(MAX)
SET @CSVBody=(SELECT TOP 1 NCOA_PBI_CSV_Holding.FileContents
FROM NCOA_PBI_CSV_Holding)

/*CREATE TABLE NCOA_PBI_CSV_Holding
(FileContents VARCHAR(MAX))*/

SET @CSVBody=REPLACE(@CSVBody,'\r\n','~')
SET @CSVBody=REPLACE(@CSVBody,CHAR(10),'~')

SELECT * INTO #Splits
FROM STRING_SPLIT(@CSVBody,'~')
WHERE [value] NOT LIKE '%ADDRLINE1,ADDRLINE2,ADDRLINE3,ANKLINK%'

UPDATE #Splits
SET value = REPLACE(value,CHAR(13),'')

TRUNCATE TABLE NCOA_PBI_CSV_Holding

SELECT dbo.UFN_SEPARATES_COLUMNS([value],1,',') ADDRLINE1
,dbo.UFN_SEPARATES_COLUMNS([value],2,',') ADDRLINE2
,dbo.UFN_SEPARATES_COLUMNS([value],3,',') ADDRLINE3
/*,dbo.UFN_SEPARATES_COLUMNS([value],4,',') ANKLINK
,dbo.UFN_SEPARATES_COLUMNS([value],5,',') ARFN*/
,dbo.UFN_SEPARATES_COLUMNS([value],6,',') City
/*,dbo.UFN_SEPARATES_COLUMNS([value],7,',') CRRT
,dbo.UFN_SEPARATES_COLUMNS([value],8,',') DPV
,dbo.UFN_SEPARATES_COLUMNS([value],9,',') Date_Generated
,dbo.UFN_SEPARATES_COLUMNS([value],10,',') DPV_No_Stat
,dbo.UFN_SEPARATES_COLUMNS([value],11,',') DPV_Vacant
,dbo.UFN_SEPARATES_COLUMNS([value],12,',') DPVCMRA
,dbo.UFN_SEPARATES_COLUMNS([value],13,',') DPVFN
,dbo.UFN_SEPARATES_COLUMNS([value],14,',') ELOT
,dbo.UFN_SEPARATES_COLUMNS([value],15,',') FN*/
,dbo.UFN_SEPARATES_COLUMNS([value],16,',') Custom
/*,dbo.UFN_SEPARATES_COLUMNS([value],17,',') LACS
,dbo.UFN_SEPARATES_COLUMNS([value],18,',') LACSLINK*/
,dbo.UFN_SEPARATES_COLUMNS([value],19,',') LASTFULLNAME
/*,dbo.UFN_SEPARATES_COLUMNS([value],20,',') MATCHFLAG
,dbo.UFN_SEPARATES_COLUMNS([value],21,',') MOVEDATE
,dbo.UFN_SEPARATES_COLUMNS([value],22,',') MOVETYPE
,dbo.UFN_SEPARATES_COLUMNS([value],23,',') NCOALINK*/
,CAST(dbo.UFN_SEPARATES_COLUMNS([value],24,',') AS DATE) PRCSSDT
/*,dbo.UFN_SEPARATES_COLUMNS([value],25,',') RT
,dbo.UFN_SEPARATES_COLUMNS([value],26,',') Scrub_Reason*/
,dbo.UFN_SEPARATES_COLUMNS([value],27,',') STATECD
/*,dbo.UFN_SEPARATES_COLUMNS([value],28,',') SUITELINK
,dbo.UFN_SEPARATES_COLUMNS([value],29,',') SUPPRESS
,dbo.UFN_SEPARATES_COLUMNS([value],30,',') WS*/
,dbo.UFN_SEPARATES_COLUMNS([value],31,',') ZIPCD
,dbo.UFN_SEPARATES_COLUMNS([value],32,',') Unique_ID
--,CAST(dbo.UFN_SEPARATES_COLUMNS([value],32,',') AS INT) Unique_ID
,CAST(NULL AS INT) Dedup_Priority
,CAST(NULL AS NVARCHAR(20)) CIF_Key
INTO #ParsedCSV
FROM #splits-- STRING_SPLIT(@CSVBody,'~')
--WHERE [value] NOT LIKE '%ADDRLINE1,ADDRLINE2,ADDRLINE3,ANKLINK%'

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