How use dynamic Folder and File in Stored procedure?

Question

Sir

I have to import flat file into mssql 2019
But I have created stored procedure to import .txt file using asp.net page
I have fixed path and file name. Its working fine.
I need advice how to do ,user select folder and file name dynamically

It is my Code

insert into [dbo].[ZZ_Flat_Data]
(CoCode, CoName, CoAddress1, CoAddress2, CoAddress, CoPost, SPeron, Transporter, DocDate, DocNo, 
Category, Amount, Type)
SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Text;Database=C:\textfiles; ',
'SELECT CoCode, CoName, CoAddress1, CoAddress2, CoAddress, CoPost, SPeron, Transporter, DocDate, DocNo, 
Category, Amount, Type FROM test.txt')

Pls advice me
thank you
Maideen

Answer 1

Hi @SILS ,
Maybe you can build the statement as a string first, and then use EXECUTE to run it.
You can refer to the following example:

declare @query varchar(5000)  
declare @FileName varchar(100)  
declare @FilePath varchar(100)  
set @FilePath = 'C:\textfiles';  
set @FileName = 'test.txt';  
set @query = 'insert into [dbo].[ZZ_Flat_Data]  
(CoCode, CoName, CoAddress1, CoAddress2, CoAddress, CoPost, SPeron, Transporter, DocDate, DocNo,  
Category, Amount, Type)  
SELECT * FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'',''Text;Database=' + @FilePath + ''',  
''SELECT CoCode, CoName, CoAddress1, CoAddress2, CoAddress, CoPost, SPeron, Transporter, DocDate, DocNo,  
Category, Amount, Type FROM ' + @FileName +''')';  
  
print @query  
execute(@query)  

---

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.

Best regards,
Lan Huang