KlimBits

This script can be used to import a CSV file containing a column of 10 digit ISBNs into a table. It can handle having additional columns which will be ignored and does checks on the data and provides useful error messages. The user or SQL server may need the proper permissions in order to access the file. The SUSER_NAME() is returned if it fails in order to help with permissions issues.

ToDo: Trim the data being imported to remove any whitespace.

IF NOT EXISTS ( SELECT 1 FROM sys.tables WHERE Name = 'importFiles' )
	CREATE TABLE importFiles
	(	  idImportFile	BIGINT PRIMARY KEY IDENTITY (1,1)
		, creationDate	DATETIME
		, fileName		VARCHAR(100)
		, lastUpdated	INT
		, employeeID	INT
		, nickName		VARCHAR(100)
	)
	
IF NOT EXISTS ( SELECT 1 FROM sys.tables WHERE Name = 'importISBNs' )
	CREATE TABLE importISBNs
	(	  id_PK			BIGINT PRIMARY KEY IDENTITY (1,1)
		, genDate		DATETIME
		, idImportFile	INT
		, importedISBN	VARCHAR(10)
	)

DECLARE @fileName VARCHAR(100) = 'Testing File2.csv'
DECLARE @employeeID INT = 12345
DECLARE @nickname VARCHAR(50) = 'ISBN_List_' + CAST(CAST(GETDATE() AS DATE) AS VARCHAR(10))
DECLARE @bypassWarnings BIT = 0
DECLARE @importId INT

DECLARE @forecastDirectory VARCHAR(200) = '\\computer-1\ISBN_List'
IF @@SERVERNAME IN ('Test_Server') SET @forecastDirectory = '\\computer-1\ISBN_List_Testing';

DECLARE @fileExists BIT = 
(	SELECT TOP(1) 1
	FROM importFiles
	WHERE fileName = @fileName	
)		

BEGIN TRY

	IF @fileName NOT LIKE '%.csv'
	BEGIN
		SELECT 'Adding .csv to end of file name.' AS [Filename Altered]
		SET @fileName = @fileName + '.csv'
	END

	DECLARE @importFilePath VARCHAR(200) = @forecastDirectory + '\' + @fileName	

	DROP TABLE IF EXISTS #importStaging
	CREATE TABLE #importStaging (ISBN VARCHAR(100)  )

	BEGIN TRY		-- when there only one column

		EXEC
		(	'BULK INSERT #importStaging 
			FROM ''' + @importFilePath + '''
			WITH (FIRSTROW = 1,				
				FIELDTERMINATOR = '','',  
				ROWTERMINATOR = ''\n'')'
		)
		
	END TRY
	BEGIN CATCH			-- when there are multiple columns

		ALTER TABLE #importStaging ADD otherColumns VARCHAR(256)

		EXEC
		(	'BULK INSERT #importStaging
			FROM ''' + @importFilePath + '''
			WITH (FIRSTROW = 1,				
				FIELDTERMINATOR = '','',  
				ROWTERMINATOR = ''\n'')'
		)

	END CATCH 

	IF (SELECT COUNT(*) FROM #importStaging) = 0
	BEGIN
		SELECT 'Something is wrong with the import file. It either cannot be accessed or it has zero rows.';
		RETURN;
	END
	ELSE IF (SELECT MAX(LEN(ISBN)) FROM #importStaging) > 10
	BEGIN
		SELECT 'Something is wrong with the import file. It has values in the ISBN column over 10 digits. Example ISBN: ' + CAST( (SELECT TOP(1) ISBN FROM #importStaging ORDER BY LEN(ISBN) DESC) AS VARCHAR(20));
		RETURN;
	END

	-- double check import column names are what we expect them to be
	DECLARE @correctColumnNames INT = 
	(	SELECT TOP(1) CASE WHEN ISBN = 'ISBN' THEN 1 ELSE 0 END
		FROM #importStaging
		WHERE ISBN = 'ISBN'
	)

	IF @correctColumnNames = 0
	BEGIN
		SELECT 'Something is wrong with the import file. There does not appear to be a column labeled ISBN.';
		RETURN;
	END


	IF @fileExists = 1
	BEGIN
    
		IF @bypassWarnings = 0
		BEGIN
			SELECT 'This file has already been imported. In order to re-import it, you will need to set @bypassWarnings to 1.' AS [File Already Imported]
			RETURN 
		END

		IF @bypassWarnings = 1
		BEGIN
			
			SELECT 'File already imported. @bypassWarnings set to 1. Re-importing the file.' AS [File Already Imported]

			UPDATE importFiles
			SET lastUpdated = GETDATE(), employeeID = @employeeID, nickname = @nickname, @importId = idImportFile
			WHERE fileName = @fileName	

			DELETE FROM importISBNs
			WHERE idImportFile = @importId

		END
	END
	ELSE 
	BEGIN
        
		IF @nickname = '' SET @nickname = NULL

		INSERT INTO importFiles (fileName, creationDate, lastUpdated, employeeID, nickName)
		VALUES (@fileName, GETDATE(), GETDATE(), @employeeID, @nickname)			 

		SET @importId = @@IDENTITY
	END

	INSERT INTO importISBNs (idimportFile, importedISBN, gendate)
	SELECT 
			@importId
		, ISBN	
		, GETDATE()
	FROM #importStaging
	WHERE ISBN <> 'ISBN'

	SELECT @@ROWCOUNT AS rowsInserted

END TRY
BEGIN CATCH
	SELECT 'User: ' + SUSER_NAME() + '  Error: ' + ERROR_MESSAGE()
END CATCH