question

sils avatar image
0 Votes"
sils asked LanHuang-MSFT edited

How use dynamic Folder and File in Stored procedure?

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



dotnet-aspnet-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered LanHuang-MSFT edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.