CREATE PROCEDURE [dbo].[sp_00_CSVtoTable](
@csv NVARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF
SELECT @csv = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@csv
, CHAR(239), '')
, CHAR(187), '')
, CHAR(191), '')
, CHAR(13), CHAR(10))
, CHAR(10) + CHAR(10), CHAR(10))
DECLARE @csvReplaceTextInCell TABLE(
row INT IDENTITY(1,1)
, m_text NVARCHAR(MAX)
)
SELECT @csv = REPLACE(@csv, '""', CHAR(130))
INSERT INTO @csvReplaceTextInCell SELECT value FROM STRING_SPLIT(@csv, '"')
UPDATE @csvReplaceTextInCell SET m_text = REPLACE(REPLACE(m_text
, CHAR(10), CHAR(128))
, ',', CHAR(129))
WHERE row % 2 = 0
DECLARE @thisrow INT, @totalrows INT
SELECT @thisrow = 1, @totalrows = COUNT(*), @csv = ''
FROM @csvReplaceTextInCell
WHILE @thisrow <= @totalrows
BEGIN
SELECT @csv = @csv + m_text
FROM @csvReplaceTextInCell
WHERE row = @thisrow
SELECT @thisrow = @thisrow + 1
END
CREATE TABLE #csvRows (
attdata NVARCHAR(MAX)
)
INSERT INTO #csvRows SELECT value FROM STRING_SPLIT(@csv, CHAR(10))
DELETE FROM #csvRows WHERE attdata = '' OR attdata = ','
ALTER TABLE #csvRows ADD row INT IDENTITY(1,1)
CREATE TABLE #csvTable(
row INT
)
DECLARE @sql NVARCHAR(MAX)= 'ALTER TABLE #csvTable ADD '
SELECT @sql = @sql + 'c' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY value)) + ' NVARCHAR(MAX),'
FROM STRING_SPLIT((SELECT attdata FROM #csvRows WHERE row = 1), ',')
SELECT @sql = LEFT(@sql, LEN(@sql) - 1)
EXEC (@sql)
ALTER TABLE #csvTable DROP COLUMN row
SELECT @totalrows = COUNT(*), @thisrow = 1 FROM #csvRows
WHILE @thisrow <= @totalrows
BEGIN
SELECT @sql = 'INSERT INTO #csvTable SELECT '
SELECT @sql = @sql + ''''
+ REPLACE(REPLACE(REPLACE(REPLACE(value
, '''', '''''')
, CHAR(128), CHAR(10))
, CHAR(129), ',')
, CHAR(130), '"')
+ ''','
FROM STRING_SPLIT((SELECT attdata FROM #csvRows WHERE row = @thisrow), ',')
SELECT @sql = LEFT(@sql, LEN(@sql) - 1)
BEGIN TRY
EXEC (@sql)
END TRY
BEGIN CATCH
PRINT 'Error on row ' + CONVERT(VARCHAR, @thisrow) + ': ' + ERROR_MESSAGE()
PRINT @SQL
SELECT * FROM #csvRows
RETURN
END CATCH
SET @thisrow = @thisrow + 1
END
SELECT * FROM #csvTable
DROP TABLE #csvTable
END