August 12, 2011 at 8:27 am
Hi,
I was just contemplating importing data into MSSQL and I wondered if there was a way to read through the contents of a directory and add say a csv file from each sub directory into the MSSQL database using T-SQL?
Any suggestions welcome.
Thanks
August 12, 2011 at 8:34 am
would the table already exist that you want to import into?
would all the csv files be the same format?
if the above two things are true, you could use xp_cmdshell to get the file name, and a little dynamic SQL with BULK INSERT to bring in the data.
after that, i'd consider either the import data task in SQL Server Management Studio, or maybe SSIS , since both of them can create the ables if they don't exist.
Lowell
August 12, 2011 at 8:40 am
Then I appear to be on the right tracks as the tables exist and the csv files are constant.
I just need to find the best or correct article to aid me in my problem solving.
Thanks for the suggestions
Regards
August 12, 2011 at 8:48 am
ok here's a full emaaple then, all in TSQL:
this is getting every *.txt file in four different folders, and inserting them all into the same table.
my table "BULKACT" would be the equivilent of your target table.
you can certainly use this as a model then
:
--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 = ''\n'',
FIRSTROW = 2 --my files had irst row column names
) '
print @sql
exec (@sql)
fetch next from c1 into @path,@filename
end
close c1
deallocate c1
Lowell
August 12, 2011 at 9:00 am
Great!
I am almost there with that but I need to loop through the folders in a directory and then get each file from there, unless I remove the need for a folder.
I will see what I can make out of your code though.
Thanks!
August 12, 2011 at 9:09 am
this may help a little;
this gets all the directories in a given directory.
from there, you can cursor thru to build what i had hard coded to 4 specific directories:
--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\'
--/AD = only directories /B=Vrief format /OG order by directories first
SET @cmd = 'dir ' + @path + ' /AD /B /OG'
EXEC Master..xp_cmdShell @cmd
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply