April 11, 2017 at 5:42 pm
Hello,
I've searched, and while there is some info, I've been unsuccessful as of yet. I'm able to do this in Access, but was hoping there's a nifty way to do this in SQL Server. Let's say I have a folder that a file is automatically dropped in each day. This file has a standard naming convention each day its dropped. let's say Sales_04112017. Unfortunately, this text file doesn't have a SalesDate field. What I'd like to do is this:
1) Grab the file from the folder, import it into my CurrentSales table
2) Take the last 8 characters of the file name and put them into the SalesDate field
3) Take the existing file and place it into my ArchivedSales folder
I don't know if that's enough info to go on, but a shove in the right direction would be appreciated
thank you!
April 12, 2017 at 1:34 am
John524 - Tuesday, April 11, 2017 5:42 PMHello,
I've searched, and while there is some info, I've been unsuccessful as of yet. I'm able to do this in Access, but was hoping there's a nifty way to do this in SQL Server. Let's say I have a folder that a file is automatically dropped in each day. This file has a standard naming convention each day its dropped. let's say Sales_04112017. Unfortunately, this text file doesn't have a SalesDate field. What I'd like to do is this:
1) Grab the file from the folder, import it into my CurrentSales table
2) Take the last 8 characters of the file name and put them into the SalesDate field
3) Take the existing file and place it into my ArchivedSales folderI don't know if that's enough info to go on, but a shove in the right direction would be appreciated
thank you!
This is the type of work that SSIS is for. Do you have experience of that, or work with somebody that does?
April 12, 2017 at 5:12 am
In this case it is better to work with someone intelligent
April 12, 2017 at 5:29 am
maudzedunjp - Wednesday, April 12, 2017 5:12 AMIn this case it is better to work with someone intelligent
I will assume that English is not your first language, so you need to know that could be taken as quite insulting.
A better way to say it would have been "experienced" instead of "intelligent".
You should also say what they need to be experienced in, as the reply you gave does not help at all.
April 12, 2017 at 6:55 am
lol yes...that was a fine way to start the day. Anyway, thanks Steve, I do have some experience with SSIS, but I don't have it set up on my new pc yet. I have used BULK LOAD in the past, so I was hoping I'd be able to do it with a few additional lines, but probably high time I dusted off Integration Services anyway
thank you
April 12, 2017 at 7:29 am
BULK INSERT would work ok, if the file name was consistent. With the file changing then it probably wouldn't be ideal, and I'm not sure you're be able to archive the files easily.
With SSIS you could scan the directory the files will be saved in for files (this means if you miss a day you can play catch up, or not have to rely on a specific file name being supplied on a specific day). You'll be able to manipulate the file name to get your date value, insert your data, and also perform file system tasks to achieve the file after so that you can archive.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 12, 2017 at 8:56 am
maudzedunjp - Wednesday, April 12, 2017 5:12 AMIn this case it is better to work with someone intelligent
You should take your own advise. 😉
https://www.sqlservercentral.com/Forums/FindPost1870404.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply