September 28, 2017 at 4:13 am
Hi,
I have a file directory path were I get a csv file per day, with current date. I have set up Flat File Connection manager that uses expression to access that location. When I execute I get an error that it cannot open datafile.
The problem is, my path doesn't include the filename itself, and I don't understand how should I include file name because the file name changes daily, since it comes with different dates. e.g today's file Generic20170928100320.csv, tomorrow's file Generic20170929110523.csv
PS: I can also receive files with previous dates and time, e.g Generic20170914110720.csv
How do I fix this.
September 28, 2017 at 4:32 am
hoseam - Thursday, September 28, 2017 4:13 AMHi,I have a file directory path were I get a csv file per day, with current date. I have set up Flat File Connection manager that uses expression to access that location. When I execute I get an error that it cannot open datafile.
The problem is, my path doesn't include the filename itself, and I don't understand how should I include file name because the file name changes daily, since it comes with different dates. e.g today's file Generic20170928, tomorrow's file Generic20170929.
How do I fix this.
It's straight forward using a script task.
😎
September 28, 2017 at 4:42 am
Without a full filepath, this is an example, however, the following expression would return: \\YourFileShare\SharedFiles\Folder\20172809\Generic20170928"\\\\YourFileShare\\SharedFiles\\Folder\\" +
(DT_WSTR,4) DATEPART( "YYYY", GETDATE()) +
RIGHT("0" + (DT_WSTR,2) DATEPART( "M", GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2) DATEPART( "D", GETDATE()),2) +
"\\Generic" + (DT_WSTR,4) DATEPART( "YYYY", GETDATE()) +
RIGHT("0" + (DT_WSTR,2) DATEPART( "M", GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2) DATEPART( "D", GETDATE()),2)
So, much like you used an expression to determine your filepath, you just extend it to have the filename in as well.
This assume that your files don't have an extension, as your examples show.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 28, 2017 at 5:34 am
Thom A - Thursday, September 28, 2017 4:42 AMWithout a full filepath, this is an example, however, the following expression would return: \\YourFileShare\SharedFiles\Folder\20172809\Generic20170928"\\\\YourFileShare\\SharedFiles\\Folder\\" +
(DT_WSTR,4) DATEPART( "YYYY", GETDATE()) +
RIGHT("0" + (DT_WSTR,2) DATEPART( "M", GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2) DATEPART( "D", GETDATE()),2) +
"\\Generic" + (DT_WSTR,4) DATEPART( "YYYY", GETDATE()) +
RIGHT("0" + (DT_WSTR,2) DATEPART( "M", GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2) DATEPART( "D", GETDATE()),2)
So, much like you used an expression to determine your filepath, you just extend it to have the filename in as well.This assume that your files don't have an extension, as your examples show.
In my case is
\\YourFileShare\SharedFiles\Folder\Generic20170928.csv
The following day they replace the file then I will have
\\YourFileShare\SharedFiles\Folder\Generic20170929.csv
September 28, 2017 at 6:00 am
hoseam - Thursday, September 28, 2017 5:34 AMIn my case is\\YourFileShare\SharedFiles\Folder\Generic20170928.csv
The following day they replace the file then I will have\\YourFileShare\SharedFiles\Folder\Generic20170929.csv
Ok, so you'll to add the file extension onto the expression.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 28, 2017 at 6:12 am
Thom A - Thursday, September 28, 2017 6:00 AMhoseam - Thursday, September 28, 2017 5:34 AMIn my case is\\YourFileShare\SharedFiles\Folder\Generic20170928.csv
The following day they replace the file then I will have\\YourFileShare\SharedFiles\Folder\Generic20170929.csv
Ok, so you'll to add the file extension onto the expression.
I am doing that right now, I'm also trying to test other senerios, when I get this file
Generic20170913100320.csv
It has previous date and time, so using that expression with getdate() won't cut it
September 28, 2017 at 6:20 am
hoseam - Thursday, September 28, 2017 6:12 AMI am doing that right now, I'm also trying to test other senerios, when I get this fileGeneric20170913100320.csv
It has previous date and time, so using that expression with getdate() won't cut it
Ok, so the file name might or might not have the time in it?
Sounds like, at this stage, the name of the file is indeterminable prior to receipt. Therefore, I'd instead suggest using a For Each Loop Container on the folder, and looping around each file in the folder instead.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 28, 2017 at 6:23 am
Thom A - Thursday, September 28, 2017 6:20 AMhoseam - Thursday, September 28, 2017 6:12 AMI am doing that right now, I'm also trying to test other senerios, when I get this fileGeneric20170913100320.csv
It has previous date and time, so using that expression with getdate() won't cut it
Ok, so the file name might or might not have the time in it?
It will always have time, datetime in that format.
September 28, 2017 at 6:30 am
hoseam - Thursday, September 28, 2017 6:23 AMIt will always have time, datetime in that format.
/facepalm then why did your initial examples not include it? I can see you editted your original post 8 minutes ago with them in now, but Eirikur's quoted post shows you excluded these initially. If the filenames always include the time, then providing examples that don't have a time in them aren't examples.
You have the logic for how to get the year, month and date into file name. Have a go at doing this yourself for hours, minutes and seconds; the logic is exactly the same. Post back, with what you tried if you don't get it working.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 28, 2017 at 6:47 am
Rather than using a complex expression, why not use a foreach container with a filespec of Generic*.csv (on the assumption that files are moved or renamed after they have been processed)?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 28, 2017 at 6:50 am
Thom A - Thursday, September 28, 2017 6:30 AMhoseam - Thursday, September 28, 2017 6:23 AMIt will always have time, datetime in that format./facepalm then why did your initial examples not include it? I can see you editted your original post 8 minutes ago with them in now, but Eirikur's quoted post shows you excluded these initially. If the filenames always include the time, then providing examples that don't have a time in them aren't examples.
You have the logic for how to get the year, month and date into file name. Have a go at doing this yourself for hours, minutes and seconds; the logic is exactly the same. Post back, with what you tried if you don't get it working.
Your logic uses current dates, I'm struggling to pick up the times on the files.
September 28, 2017 at 6:55 am
Phil Parkin - Thursday, September 28, 2017 6:47 AMRather than using a complex expression, why not use a foreach container with a filespec of Generic*.csv (on the assumption that files are moved or renamed after they have been processed)?
This is the other idea I put forward earlier. Using an expression was fine at first (sometimes I prefer this method as if a file is missed you may not want to load them), when things were simply the date, but seems the goal posts have moved since. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 28, 2017 at 6:55 am
Phil Parkin - Thursday, September 28, 2017 6:47 AMRather than using a complex expression, why not use a foreach container with a filespec of Generic*.csv (on the assumption that files are moved or renamed after they have been processed)?
Thanks, Foreach worked.
September 28, 2017 at 6:59 am
Thom A - Thursday, September 28, 2017 6:55 AMThis is the other idea I put forward earlier. Using an expression was fine at first (sometimes I prefer this method as if a file is missed you may not want to load them), when things were simply the date, but seems the goal posts have moved since. 🙂
So you did ... I only skimmed the thread and missed that. I always use this method, on the assumption that if a file is resident in my 'ToBeProcessed' folder, then it needs to be processed 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 28, 2017 at 7:14 am
Phil Parkin - Thursday, September 28, 2017 6:59 AMThom A - Thursday, September 28, 2017 6:55 AMThis is the other idea I put forward earlier. Using an expression was fine at first (sometimes I prefer this method as if a file is missed you may not want to load them), when things were simply the date, but seems the goal posts have moved since. 🙂So you did ... I only skimmed the thread and missed that. I always use this method, on the assumption that if a file is resident in my 'ToBeProcessed' folder, then it needs to be processed 🙂
Generally I agree. I do, however, have a daily file from an Insurer that is cumulative. If, for whatever reason, the server doesn't run the file on that day, the last thing i want to do is run it the following day, as I might then overwrite the previous. I learnt that lesson the hard way, so I'm little bit of a skeptical now on completely relying on simply looping. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply