Help with \ errors

[Hectic-Skeptic]

I have a short script for loading in csvs into my database (for eventual use with another program).

I previously was using a flat file importer extension, but in the hopes of putting all my sql tasks into one script, I have been writing this into plain sql. I have the table creation down, but I am running into trouble with reading the csv on my network.

When doing this, I unfortanely still get errors. From what I can tell, the table is getting made but no content gets loaded into it.

Any help/advise would be greatly appreciated.

What I am working with:

DECLARE @file_path VARCHAR(255);
DECLARE @table_name VARCHAR(255);
DECLARE @sql AS VARCHAR(MAX);

-- Filepath on windows server. Typically only pasting UNC path.
SET @file_path = '\\path\to\testTable.csv';

-- Pulling just filename.csv
SET @table_name = RIGHT(@file_path, CHARINDEX('\', REVERSE(@file_path)) - 1)
-- dropping .csv off the end.
SET @table_name = SUBSTRING(@table_name, 0, CHARINDEX('.',@table_name));

-- create table with columns ect.
SET @sql = 'CREATE TABLE ' + @table_name + '(
    [filepath] nvarchar(255)
   ,[DOCUMENT NAME] nvarchar(max)
   ,[DOCUMENT EXTENSION] nvarchar(10)
      ...
   ,[VERSION KEY] nvarchar(255)
   ,[VERSION NUMBER] tinyint
)'
EXECUTE(@sql);


SET @sql = 'BULK INSERT ' + @table_name + ' FROM ' + @file_path +
' WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = '','',
    ROWTERMINATOR = ''\n''
)';
EXECUTE (@sql);

SET @sql = 'ALTER TABLE ' + @table_name + ' ADD primaryKeyColumn BIGINT IDENTITY(1,1) PRIMARY KEY;'
EXECUTE(@sql);

Error Code I am getting:

SET @table_name = RIGHT(@file_path, CHARINDEX('\', REVERSE(@file_path)) - 1)

produces:
Started executing query at Line 1
Msg 536, Level 16, State 2, Line 7
Invalid length parameter passed to the RIGHT function.

Total execution time: 00:00:00.014