Hi @Neo ,
Please try below script, it will work:
USE [TestDemo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE OR ALTER PROCEDURE [dbo].[ps_test_Import]
@PathFileName varchar(100),--='c:\TxtFile1.txt',
@FileType tinyint
AS
--Step 1: Build Valid BULK INSERT Statement
DECLARE @SQL varchar(2000)
IF @FileType = 1
BEGIN
-- Valid format: "John","Smith","john@smith.com"
SET @SQL = 'BULK INSERT TmpStList FROM '''+@PathFileName+''' WITH (FIELDTERMINATOR = '','',FIRSTROW=2) '
END
ELSE
BEGIN
-- Valid format: John,Smith,john@smith.com
SET @SQL = 'BULK INSERT TmpStList FROM '''+@PathFileName+''' WITH (FIELDTERMINATOR = '','',FIRSTROW=2) '
END
-- print @SQL
--Step 2: Execute BULK INSERT statement
EXEC (@SQL)
--select * from TmpStList
--declare @FileType tinyint=1
--Step 3: INSERT data into final table
INSERT TEST ([DATE],[CD],[MY-ACC],[YOUR-ACC],[BAL])
SELECT CASE WHEN @FileType = 1 THEN SUBSTRING([DATE],2,DATALENGTH([DATE])-2)
ELSE [DATE]
END,
SUBSTRING([CD],2,DATALENGTH([CD])-2),
SUBSTRING([MY-ACC],2,DATALENGTH([MY-ACC])-2),
SUBSTRING([YOUR-ACC],2,DATALENGTH([YOUR-ACC])-2),
CASE WHEN @FileType = 1 THEN SUBSTRING([BAL],2,DATALENGTH([BAL])-2)
ELSE [BAL]
END
FROM TmpStList
-- exec [dbo].[ps_test_Import] 'C:\1.Temp\Test.txt',1
Output:
In addition, there is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.
Best regards,
Carrin
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.