August 12, 2009 at 9:32 pm
I am trying to create an query to import data into table from text file and my text file name is a variable.
I have to read the file names in a directory first and use that name in a Bulk insert sql. I try to read the file name into varable @file and write following sql and it did not work
BULK
INSERT #temptable
FROM @file
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR ='''
)
Please Help me to workaround this problem.
August 13, 2009 at 12:14 am
Hi Try this
Declare @file varchar(1000)
set @file = 'c:\file.txt'
BULK INSERT T_Name
FROM @file
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR ='''
)
August 13, 2009 at 3:05 am
'Did not work'. Did it run without errors but have nothing in the temporary table, or did it show an error? Try removing the variable and hard-coding the filename first of all, just to make sure the 'mechanics' of the import will work OK.
I must confess I've never seen a Bulk Insert use a variable for the filename, but I have seen examples of Dynamic SQL building a Bulk Insert with a variable for a filename, so it may be that you have to do it that way.
BrainDonor
August 13, 2009 at 10:11 pm
It works when you hard code file name. I don't know how to do this in Dynamic sql I will investigate. Thanks for the response.
August 14, 2009 at 6:21 am
here's how I doing: bulk insert with dynamic SQL:
note the forum strips out the slash -n, so i put a placeholder {slash-n}
--bulk insert won't take a variable name, so make a sql and execute it instead:
set @sql = 'BULK INSERT BULKACT FROM ''' + @path + @filename + ''' '
+ ' WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''{slash-n}'',
FIRSTROW = 2
) '
print @sql
exec (@sql)
Lowell
August 14, 2009 at 7:02 am
You can help make a change for the SQL Server by voting for this connect issue here
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=295626
N 56°04'39.16"
E 12°55'05.25"
August 17, 2009 at 4:08 am
Thanks Lowell
Your code worked for me. Next step is I have to workout how to pickup one file at a time from the directory.
It is great if you can give me some direction.
I am thinking of using it using Xp_cmshell and dir and update file names to a table.
Is there a smart way of doing it.
August 17, 2009 at 5:47 am
ajith.ranjan (8/17/2009)
Thanks LowellYour code worked for me. Next step is I have to workout how to pickup one file at a time from the directory.
It is great if you can give me some direction.
I am thinking of using it using Xp_cmshell and dir and update file names to a table.
Is there a smart way of doing it.
the snippet I gave was from the complete example below:
the original issue was the poster knew he needed to process an large but unknown number of files, but that they existed in four folders. :
--MULTIPLE FILE BULK INSERT
-a table to loop thru filenames drop table ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
--the source table: yours already exists, but needed for this example.
CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))
--some variables
declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@cmd varchar(1000)
--get the list of files to process:
--^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#
SET @path = 'C:\DB\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
SET @path = 'C:\DB2\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
SET @path = 'C:\DB3\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
SET @path = 'C:\DB4\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
--^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#
--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.txt%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status -1
begin
--bulk insert won't take a variable name, so make a sql and execute it instead:
set @sql = 'BULK INSERT BULKACT FROM ''' + @path + @filename + ''' '
+ ' WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''{slash-n}'',
FIRSTROW = 2
) '
print @sql
exec (@sql)
fetch next from c1 into @path,@filename
end
close c1
deallocate c1
Lowell
August 19, 2009 at 2:39 pm
Thanks again,
That's am exactly looking for
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply