Text Conversion Problem
-
I am creating a table from a file directory contents - 1 of the columns is the filename = 00001-001.TXT or 00001-049.TXT, Unfortunately the filename comes accross as 00001-~1.TXT.
I have tried char(20), and varchar(20).
What do I need to do to get the correct format to be stored in my temp table.
Also, should I do this conversion on the create table(serverFiles) or in the Select statement - I in the creation of the table then please give me the correct syntax.
On the date fields I would like to display the date format as mm/dd/yyyy - how do I change the datetime format to display in this format?
Same question for the time fields what is the proper field type for time field and correct format syntax?
Here is my current code
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'Tmptable'
AND type = 'U')
DROP TABLE Tmptable
GO
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'Serverfiles'
AND type = 'U')
DROP TABLE Serverfiles
GO
Set Nocount On
Create Table Tmptable (Fname Varchar(255))
Insert Tmptable
Exec Master..Xp_Cmdshell 'Dir/S/B \\Seasc-Shared\Spln\Shared\Forecast\'
Create Table Serverfiles(
Alternate_Name varchar(20),
File_Size Char(20),
Creation_Date Char(20),
Creation_Time Char(20),
Last_Written_Date Char(20),
Last_Written_Time Char(20),
Last_Accessed_Date Char(20),
Last_Accessed_Time Char(20),
Attributes Char(20)
)
Declare @sql Varchar(255)
Declare @Fname Varchar(255)
Declare T_Cursor Cursor For Select Fname From Tmptable
Open T_Cursor
Fetch Next From T_Cursor Into @Fname
While @@Fetch_Status = 0
Begin
Set @sql = 'Exec Master.Dbo.Xp_Getfiledetails "' + @Fname + '"'
Insert Serverfiles
Exec (@Sql)
Fetch Next From T_Cursor Into @Fname
End
Close T_Cursor
Deallocate T_Cursor
SELECT Cast(Alternate_Name as varchar(20)) as AlternateName,
CAST(CAST(Creation_Date as varchar(20)) as datetime) as CreationDate,
--CAST(CAST(Creation_Time as varchar(20)) as datetime) as CreationTime,
CAST(CAST(Last_Written_Date as varchar(20)) as datetime) as LastWrittenDate,
--CAST(CAST(Last_Written_Time as varchar(20)) as datetime) as LastWrittenTime,
CAST(CAST(Last_Accessed_Date as varchar(20)) as datetime) as LastAccessedDate,
--CAST(CAST(Last_Accessed_Time as varchar(20)) as datetime) as LastAccessTime,
Attributes
From Serverfiles
Thanks,
Karen
-
Karen,
Look at this article on sqlservercentarl.com:
http://www.sqlservercentral.com/columnists/CCathers/searchingthefilesystem.asp
It has ALL the code you need, all you have to do is make some simple changes to meet your needs and it works.
Butch -
Here's a quick modification that also includes the long filename with the path stripped off. Your problem is that ALTERNATE_NAME is the 8.3 filename, but your desired files are 9 characters plus the extension. You need to use the value in the @Fname variable.
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Tmptable' AND type = 'U')
DROP TABLE Tmptable
GO
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Serverfiles' AND type = 'U')
DROP TABLE Serverfiles
GO
SET NOCOUNT ON
Create Table Tmptable (Fname Varchar(255))
Insert Tmptable Exec Master..Xp_Cmdshell 'Dir/S/B \\Seasc-Shared\Spln\Shared\Forecast\'
CREATE TABLE #tmpDetails
(
Alternate_Name varchar(20),
File_Size Char(20),
Creation_Date Char(20),
Creation_Time Char(20),
Last_Written_Date Char(20),
Last_Written_Time Char(20),
Last_Accessed_Date Char(20),
Last_Accessed_Time Char(20),
Attributes Char(20)
)
Create Table Serverfiles
(
Filename varchar(255),
Alternate_Name varchar(20),
File_Size Char(20),
Creation_Date Char(20),
Creation_Time Char(20),
Last_Written_Date Char(20),
Last_Written_Time Char(20),
Last_Accessed_Date Char(20),
Last_Accessed_Time Char(20),
Attributes Char(20)
)
Declare @sql Varchar(255)
Declare @Fname Varchar(255)
Declare T_Cursor Cursor For Select Fname From Tmptable
DECLARE @pos int
Open T_Cursor
Fetch Next From T_Cursor Into @Fname
While @@Fetch_Status = 0
Begin
-- Don't need Dynamic SQL here
-- Set @sql = 'Exec Master.Dbo.Xp_Getfiledetails "' + @Fname + '"'
-- Insert Serverfiles Exec (@Sql)
DELETE #tmpDetails
Insert #tmpDetails EXEC master.dbo.xp_Getfiledetails @Fname
-- strip path, leave filename only
SET @pos = CharIndex('\', @Fname)
WHILE @pos > 0 AND Len(@Fname) > 0
BEGIN
-- 255 is max defined length here, keeping it simple
SET @Fname = Substring(@Fname, @pos + 1, 255)
SET @pos = CharIndex('\', @Fname)
END
INSERT ServerFiles SELECT @Fname, d.* FROM #tmpDetails d
Fetch Next From T_Cursor Into @Fname
End
Close T_Cursor
Deallocate T_Cursor
DROP TABLE #tmpDetails
SELECT Cast(filename as varchar(60)),
Cast(Alternate_Name as varchar(20)) as AlternateName,
CAST(CAST(Creation_Date as varchar(20)) as datetime) as CreationDate,
CAST(CAST(Last_Written_Date as varchar(20)) as datetime) as LastWrittenDate,
CAST(CAST(Last_Accessed_Date as varchar(20)) as datetime) as LastAccessedDate,
Attributes
FROM Serverfiles
-
Thank you for all you hard work and helping me get my issue resolved - You are the BEST!!!!!
1 more issue - how to I modified the code to strip out those records that the filename is shorter than 9 characters and strip out the .txt from the filename.
Also How to I format time, I need to have the date and time field together in the same field to compare against existing data.
ie. 11/2/2004 6:31:44 AM
Right now my date format using Cast = 11/01/2004 and the time is not displayed. I am retrieving this date from the file directory.
Thanks so much - You Rule
Karen
-
Here's a bit more code. To simplify the main code, I've used two UDF's, dbo.fRStrBefore() and dbo.fRStrAfter(). You'll have to create those first to use the rest of the code.
I've modified the ServerFiles table to include the fully qualified pathname parsed into the path (fpath), the filename (fname), and the extension (fext). That way, if you need those parts in the future, they will be available.
The sizes used with cast at the end are just for display purposes. You should adjust those as necessary (or not cast to a smaller length at all).
-- PRINT dbo.fRStrBefore('XYZ.TXT', '.') returns "XYZ"
DROP FUNCTION dbo.fRStrBefore
GO
CREATE FUNCTION dbo.fRStrBefore
(
@target varchar(8000),
@search varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
-- ----------------------------------------------------------------------------
--
-- Function: fRStrBefore( @target varchar(8000), @search varchar(8000) )
--
-- Returns: varchar(8000)
-- The string preceding the rightmost occurrence of the string @search
-- within @target. If @search is not found within @target, an empty
-- string is returned ( '' ).
--
-- Example: fRStrBefore('XYZ.TXT', '.') returns "XYZ"
--
-- ----------------------------------------------------------------------------
DECLARE @pos int, @nextPos int
DECLARE @substr varchar(8000)
--
-- Step 1: Find rightmost occurrence of the search string
--
SET @substr = ''
SET @pos = 0
SET @nextPos = 1
WHILE @nextPos > 0
BEGIN
SET @nextPos = CharIndex(@search, @target, @nextPos)
IF @nextPos > 0
BEGIN
SET @pos = @nextPos
SET @nextPos = @nextPos + 1
END
END -- WHILE
--
-- Step 2: Return the substring preceding position @pos
--
IF @pos > 0
SET @substr = Left(@target, @pos - 1)
RETURN @substr
END
-- ======================================================================================
DROP FUNCTION dbo.fRStrAfter
GO
CREATE FUNCTION dbo.fRStrAfter
(
@target varchar(8000),
@search varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
-- ----------------------------------------------------------------------------
--
-- Function: fRStrAfter( @target varchar(8000), @search varchar(8000) )
--
-- Returns: varchar(8000)
-- The string following the rightmost occurrence of the string @search
-- within @target. If @search is not found within @target, an empty
-- string is returned ( '' ).
--
-- Example: fRStrAfter('C:\TEMP\XYZ.TXT', '.') returns "TXT"
--
-- ----------------------------------------------------------------------------
DECLARE @pos int, @nextPos int
DECLARE @substr varchar(8000)
--
-- Step 1: Find rightmost occurrence of the search string
--
SET @substr = ''
SET @pos = 0
SET @nextPos = 1
WHILE @nextPos > 0
BEGIN
SET @nextPos = CharIndex(@search, @target, @nextPos)
IF @nextPos > 0
BEGIN
SET @pos = @nextPos
SET @nextPos = @nextPos + 1
END
END -- WHILE
--
-- Step 2: Return the substring following position @pos
--
IF @pos > 0 AND @pos < Len(@target)
SET @substr = Substring(@target, @pos + 1, 8000)
RETURN @substr
END
-- ======================================================================================
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Tmptable' AND type = 'U')
DROP TABLE Tmptable
GO
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Serverfiles' AND type = 'U')
DROP TABLE Serverfiles
GO
SET NOCOUNT ON
Create Table Tmptable (Fname Varchar(255))
Insert Tmptable Exec Master..Xp_Cmdshell 'Dir/S/B \\Seasc-Shared\Spln\Shared\Forecast\'
CREATE TABLE #tmpDetails
(
Alternate_Name varchar(20),
File_Size varchar(20),
Creation_Date varchar(20),
Creation_Time varchar(20),
Last_Written_Date varchar(20),
Last_Written_Time varchar(20),
Last_Accessed_Date varchar(20),
Last_Accessed_Time varchar(20),
Attributes varchar(20)
)
Create Table Serverfiles
(
filename varchar(255),
fPath varchar(255),
fName varchar(255),
fExt varchar(255),
short_filename varchar(20),
File_Size varchar(20),
Creation_Date datetime,
Last_Written_Date datetime,
Last_Accessed_Date datetime,
Attributes varchar(20)
)
Declare @sql Varchar(255)
Declare @Fname Varchar(255)
Declare T_Cursor Cursor For Select Fname From Tmptable
DECLARE @pos int, @fpath varchar(255)
Open T_Cursor
Fetch Next From T_Cursor Into @Fname
While @@Fetch_Status = 0
Begin
-- Don't need Dynamic SQL here
-- Set @sql = 'Exec Master.Dbo.Xp_Getfiledetails "' + @Fname + '"'
-- Insert Serverfiles Exec (@Sql)
DELETE #tmpDetails
Insert #tmpDetails EXEC master.dbo.xp_Getfiledetails @Fname
-- strip path, leave filename only
/*
SET @pos = CharIndex('\', @Fname)
WHILE @pos > 0 AND Len(@Fname) > 0
BEGIN
-- 255 is max defined length here, keeping it simple
SET @Fname = Substring(@Fname, @pos + 1, 255)
SET @pos = CharIndex('\', @Fname)
END
*/
SET @fpath = dbo.fRStrBefore(@Fname, '\')
SET @fname = dbo.fRStrAfter(@Fname, '\')
-- strip file extension
INSERT ServerFiles
SELECT @Fname,
@fpath,
dbo.fRStrBefore(@Fname, '.'),
dbo.fRStrAfter(@Fname, '.'),
d.Alternate_Name,
File_Size,
Convert(datetime, Creation_Date + ' ' + Stuff(Stuff(Right(Replicate('0',6) + Creation_Time,6), 5, 0, ':'), 3, 0, ':')),
Convert(datetime, Last_Written_Date + ' ' + Stuff(Stuff(Right(Replicate('0',6) + Last_Written_Time,6), 5, 0, ':'), 3, 0, ':')),
Convert(datetime, Last_Accessed_Date + ' ' + Stuff(Stuff(Right(Replicate('0',6) + Last_Accessed_Time,6), 5, 0, ':'), 3, 0, ':')),
Attributes
FROM #tmpDetails d
Fetch Next From T_Cursor Into @Fname
End
Close T_Cursor
Deallocate T_Cursor
DROP TABLE #tmpDetails
SELECT Cast(filename as varchar(60)) AS filename,
Cast(fName as varchar(20)) AS fName,
Cast(fExt as varchar(3)) AS fExt,
Cast(fPath as varchar(60)) AS fPath,
Cast(short_filename as varchar(12)) as short_filename,
Creation_Date,
Last_Written_Date,
Last_Accessed_Date,
Attributes
FROM Serverfiles
WHERE Len(fName) >= 9 -- if you want filenames greater than 8 chars long.
-
Thanks for your great suggestion.
Karen
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply