Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

file rename with store procedure Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 6:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 5:51 AM
Points: 4, Visits: 32
Hi all,
I've files with an unix timestamp as name. For instance filename: 1081277409
I've written a function that based on the filename returns the readable dateformat: 2004-04-06 16:50:09.000

I've done this by reading the folder contents (xp_cmdshell) and insert the filenames into a table, wrote a convert function and based on this I get the readable dateformat.
The last thing I need to do is to rename the file: 1081277409 to 2004-04-06 16:50:09.000 automatically.

Is this possible with a stored procedure? I've tried SSIS file task but this won't work.

Any tips would be highly appreciated. Thanks in advance.
Post #1519191
Posted Tuesday, December 3, 2013 7:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 13,126, Visits: 11,964
Trestire (12/3/2013)
Hi all,
I've files with an unix timestamp as name. For instance filename: 1081277409
I've written a function that based on the filename returns the readable dateformat: 2004-04-06 16:50:09.000

I've done this by reading the folder contents (xp_cmdshell) and insert the filenames into a table, wrote a convert function and based on this I get the readable dateformat.
The last thing I need to do is to rename the file: 1081277409 to 2004-04-06 16:50:09.000 automatically.

Is this possible with a stored procedure? I've tried SSIS file task but this won't work.

Any tips would be highly appreciated. Thanks in advance.


When you say it didn't work with SSIS did you get an error message? Remember that you can't have colons : in a file name.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1519228
Posted Wednesday, December 4, 2013 6:11 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 3,978, Visits: 2,994
Sean Lange (12/3/2013)
Trestire (12/3/2013)
Hi all,
I've files with an unix timestamp as name. For instance filename: 1081277409
I've written a function that based on the filename returns the readable dateformat: 2004-04-06 16:50:09.000

I've done this by reading the folder contents (xp_cmdshell) and insert the filenames into a table, wrote a convert function and based on this I get the readable dateformat.
The last thing I need to do is to rename the file: 1081277409 to 2004-04-06 16:50:09.000 automatically.

Is this possible with a stored procedure? I've tried SSIS file task but this won't work.

Any tips would be highly appreciated. Thanks in advance.


When you say it didn't work with SSIS did you get an error message? Remember that you can't have colons : in a file name.

If you're already using xp_cmdshell to to get the filenames to convert, why not use another shell where you execute the rename commands in DOS? It's pretty straightforward, but Sean hit the nail on the head - you cannot have a colon in a filename no matter how you approach it.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1519590
Posted Wednesday, December 4, 2013 8:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,781, Visits: 31,238
Heh... and stop using spaces in file names. It complicates things further down the line. If you must have separation between "fields" in the file names, use underscores.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1519661
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse