Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wembleybear
Yak Posting Veteran
93 Posts |
 Posted - 2014-11-04 : 16:52:55
|
| I have a script which imports the contents of a csv file from our CRM system and updates a table in my database. This works OK but the problems I have are that a) sometimes there is more than one file in the folder, and b) that I wish to move any csv files that have been imported into an archive folder. The csv files arrive with a time/datestamp and I currently rename them manually to FREXPORT before importing (the name is in the format FREXPORT_20141101_1217.csv).How do I: 1) get it to process the file without me having to manually rename the file(s) each time, 2) if there is more than 1 file in the folder process all the files and 3)move the correctly processed files to an archive folder which is: \import\archive? Ultimately, I would like the script to be run as a scheduled job, so it also has to deal with the fact that sometimes there will be no files to import too.[code]create table #import(worknumber nvarchar(12), date_done smalldatetime)BULKInsert #importfrom '\\fork04-hq-dc01\data\import\FREXPORT.csv'with(fieldterminator = ',',rowterminator = '\n')goselect * from #importUPDATE worksorderhdr set worksorderhdr.est_complete_date = #import.date_donefrom worksorderhdrinner join #import on #import.worknumber = worksorderhdr.worknumbergodrop table #import [\code]Many thanks for your helpMartyn |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-04 : 17:39:33
|
| Sounds like a job for ssis for each container |
 |
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2014-11-04 : 17:54:37
|
| Thanks, I was wondering whether SSIS was more appropriate. Will check in with the SSIS forum on here as haven't done anything in SSIS before. |
|
|
|
|
|
|
Import from multiple files