March 4, 2008 at 12:17 pm
i have a file c:\\abc.txt
in which i have some data so i jus need to import data from that file to db so i jus used import export wizard but here the problem is that i need to get a new column naming "source file" with the c:\\abc.txt as data in that column for the whole data ....i have some other files c:\\abc1.txt which i should import to the same table now i should get c:\\abc1.txt for the data in the column 'source file" for eg
name id sourcefile
abc 1 abc.txt
abcs 2 abc.txt
agh 3 abc.txt---imported from file abc.txt
hdsj 4 abc.txt
ancb 5 abc1.txt
ahdhd 6 abc1.txt--imported from fil abc1.txt
adshd 7 abc1.txt
deg 8 abc2.txt--imported from fil abc2.txt
sah 9 abc2.txt
asaj 10 abc2.txt
so i shouls have all this data in a single table but i need to add a new column with the column data to be changed according from which file it has come from ...other than ssis how can i do that in ssms..
can i manually type the sourcefile name in the import at first..if so how can i do that please let me know its urgent thkz in adavance.....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 4, 2008 at 12:53 pm
I don't think this can be done in the Import/Export Wizard. SSIS can do it (of course), but you said you don't want to use that, so the only other options I can think of is use BCP, or Open Recordset, and add the column you want to the results that way, or just plain do the import through the Wizard, then update the SourceFile column manually through a script.
Personally, I'd use SSIS for this. It's what it's made for, and you can easily re-use a package to import multiple files. If they have the same internal layout, you can even create a "ForEachNext" loop that will go through all the files and process them for you automatically.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2008 at 12:57 pm
You can either do this via TSQL or SSIS. In SSIS, you need to create for each container. In the container you will set a variable to the file path. You then must use a data flow task to create a derived column. The column will be created by using string expression to parse out the file name from the current file being enumerated. Then you simple a sql destination for the data flow.
This article will help you out.
To do this in sql you would have to create a loop to process one file at a time. After the bulk import you would have to update the data to reflect the filename.
(Note: this is not the correct syntax but is basic pseduocode.)
e.g.
while file <= 1
bulk insert
update table with file name
end
March 4, 2008 at 12:57 pm
can i know how can i do that in ssis
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 4, 2008 at 1:06 pm
Follow the link I posted. All you have to do additionally is use a derived column to create the filename. Like I said before you will have to create an expression to parse the filename from current connection string.
March 4, 2008 at 2:35 pm
Hmmm.... when it comes to plain ol' delimited text files such as CSV files, I sometimes think people make things way too difficult. You can easily get a listing of all files in a directory and even read some files using SELECT... it's all in the wrist... it's mostly straight out of Books Online...
--===== Create a linked server to the drive and path you desire.
EXEC dbo.sp_AddLinkedServer TxtSvr,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\',
NULL,
'Text'
GO
--===== Set up login mappings.
EXEC dbo.sp_AddLinkedSrvLogin TxtSvr, FALSE, NULL, Admin, NULL
GO
--===== List the tables in the linked server which is really a list of
-- file names in the directory. Note that the "#" sign in the
-- Table_Name is where the period in the filename actually goes.
EXEC dbo.sp_Tables_Ex TxtSvr
GO
--===== Query one of the files by using a four-part name.
SELECT *
FROM TxtSvr...[abc1#txt]
--===== Drop the text server
EXEC dbo.sp_DropServer 'TxtSvr', 'DropLogins'
GO
Even if the file isn't delimited and you need to use BCP, you can still use the method to read the file names.
If you can't use a Linked Server because of privs or whatever, you can use the undocumented wonder-of-wonders known as xp_DirTree to get the file names for use with Bulk Insert...
--===== Create a directory table
CREATE TABLE #Directory
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name VARCHAR(128),
Depth TINYINT,
IsFile BIT
)
--===== Populate the table with directory info
INSERT INTO #Directory
(Name, Depth, IsFile)
EXEC Master.dbo.xp_DirTree 'C:\',1,1 --path, depth, list files
--===== Select just the file names as a demo...
SELECT *
FROM #Directory
WHERE IsFile = 1
ORDER BY Name
DROP TABLE #Directory
Please... no rhethoric about using undocumented features because they might change... even the documented features might change. Yeah, I know... unsupported... if it works, it doesn't matter and this particular command works in 2k, 2k5 and 2k8 (according to rumor control... I don't have access to 2k8).
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2008 at 3:04 pm
Jeff good solution. 🙂
We need to expand on this a little further to meet requirments.
You would need to supply data for the SourceFile column, which does not exist in the text files.
--===== Query one of the files by using a four-part name.
INSERT INTO MyTable
SELECT *, 'abc1.txt' AS [SourceFile]
FROM TxtSvr...[abc1#txt]
Also, now that Jeff put some ideas in my head. Using openrowset dynamically would not be a bad idea also. The same concepts can be applied.
March 4, 2008 at 4:13 pm
You can do something like this, using openrowset.
DECLARE @Path VARCHAR(25),
@FormatFilePath VARCHAR(25)
@i int,
@file varchar(25),
@FilePath varchar(25),
@sql NVARCHAR(500)
SET @i = 1
SET @Path = 'C:\Test'
SET @FormatFilePath = 'c:\Test\testfmt.xml'
--===== Create a directory table
DECLARE @Directory TABLE
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name VARCHAR(128),
Depth TINYINT,
IsFile BIT
)
--===== Populate the table with directory info
INSERT INTO @Directory
(Name, Depth, IsFile)
EXEC Master.dbo.xp_DirTree @PATH,1,1 --path, depth, list files
CREATE TABLE #Results(
Name varchar(25),
id int,
SourceFile varchar(15)
)
WHILE @i <= 4
BEGIN
SELECT @file = Name
FROM @Directory
WHERE RowNum = @i
SET @FilePath = @PATH + '\' + @file
SET @sql =
'INSERT INTO #Results
SELECT a.*,'''+@file+''' FROM OPENROWSET( BULK '''+@FilePath+''',
FORMATFILE = '''+@FormatFilePath+''') AS a;
'
EXECUTE sp_executesql @sql
SET @i = @i + 1
END
SELECT *
FROM #Results
DROP TABLE #Results
You will need a format file. I have attached all the files needed to get this to work.
March 4, 2008 at 4:17 pm
You may need to delete items from the @Directory table to make sure you only process the files you want. You can do a simple delete after the directory has been created to weed out unwanted files.
E.g.
DELETE
FROM @Directory
WHERE Name IS NOT LIKE '%Test%' AND
Name IS NOT LIKE '%.txt%'
March 4, 2008 at 5:26 pm
hey adam the link u provied is good it is used to import data from multiple files automatically but in that i got a problem .. In xpression when i searched fro connection string i couldnt find that ...where can i see connction string..n what i did was...
droppd the foreachloop into control flow n set the following as in the link gav the collections n set the fil retrieval n directory n all those ..in expresiions i couldnt set the conneciton string n in foreach loop enumerator what should i include data flow task or what should put inside th loop ...i tried putting dataflow task but in flatfile source in browse i should can browse to only one file so how can i do that i couldnt understand that but in the link it showed flatfile n destination so what should be browsed in the source plz let me know ...thkz adam for ur help ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 4, 2008 at 5:38 pm
I added some more flexibility to the script. I do not know if you want to process all files in a directory but this will process all files that you have not filtered out.
If you want to do this in SSIS, I will look at it tomorrow.
DECLARE @Path VARCHAR(25),
@FormatFilePath VARCHAR(25),
@i int,
@file varchar(25),
@FilePath varchar(25),
@sql NVARCHAR(500),
@max INT
SET @i = 1
SET @Path = 'C:\Test'
SET @FormatFilePath = 'c:\testfmt.xml'
--===== Create a directory table
DECLARE @Directory TABLE
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name VARCHAR(128),
Depth TINYINT,
IsFile BIT
)
--===== Populate the table with directory info
INSERT INTO @Directory
(Name, Depth, IsFile)
EXEC Master.dbo.xp_DirTree @PATH,1,1 --path, depth, list files
SET @max = @@rowcount
DELETE
FROM @Directory
WHERE Name NOT LIKE '%Test%' AND
Name NOT LIKE '%.txt%'
CREATE TABLE #Results(
Name varchar(25),
id int,
SourceFile varchar(15)
)
WHILE @i <= @max
BEGIN
if exists(select 1 from @Directory where RowNum = @i)
BEGIN
SELECT @file = Name
FROM @Directory
WHERE RowNum = @i
SET @FilePath = @PATH + '\' + @file
SET @sql =
'INSERT INTO #Results
SELECT a.*,'''+@file+''' FROM OPENROWSET( BULK '''+@FilePath+''',
FORMATFILE = '''+@FormatFilePath+''') AS a;
'
EXECUTE sp_executesql @sql
END
SET @i = @i + 1
END
SELECT *
FROM #Results
DROP TABLE #Results
March 4, 2008 at 5:44 pm
thkz adam but now i can use ssis so i dnt want to go for complexitu using scripts so when poss jus look over abt ssis thkz
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 4, 2008 at 6:50 pm
Heh... And the GUI rules... it may take longer and it may run slower, but the GUI rules.
Bill Gates was right 15 years ago... someday there won't be any programmers... just a bunch of people that point and click and drag...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2008 at 9:12 pm
thkz adam but now i can use ssis so i dnt want to go for complexitu using scripts so when poss jus look over abt ssis thkz
This task is very easily setup in SSIS. I actually had the entire process working in less than 10 mins. I gave you the solution in one of my very first posts. I am glad to see that you at least attempted SSIS by yourself. Since you at least attempted this by yourself, I am willing to post a complete dtsx file with the solution. All you wll need to change is the connection information.
-Adam
March 4, 2008 at 9:34 pm
kurraraghu,
People are helping you solve your problems on their own free time. I do not know if it is a language barrier, you are in a rush, or you do not care, but when you post things like
thkz adam but now i can use ssis so i dnt want to go for complexitu using scripts so when poss jus look over abt ssis thkz
it seems derogatory and unappreciative. It is like you did not even take the time to write complete or logical thoughts. The bottom line is people are more willing to help someone who is sincere about needing help. We do not want to do your work for you. We want to help you grow and understand your profession.
Anyway.. It is late and I am done rambling thoughts.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply