Brickwall
asked on
SSIS to import CSV Files from Folder
I have a Folder full OF CSV Files, that have File Names LIke this PropertName-4-18.csv with the last art of the file name as the date. I am needing to import all this into SQL Database with SSIS. The Data in the files is all numeric ad formated like this.
$100.00,52,23000
$200.00,79,34125
... And So on.
I am trying to figure al this out, and know i am going to need to do a for each loop. but I am needing the Data when it is Imported to look like this:
PropertName,4-18-2012,$100 .00,52,230 00
PropertName,4-18-2012,$200 .00,79,341 25
So i need touse the date from either the file name or date the actual file was created. Please advise. There are literally hundreds of file so i don't want to have to process each one individually.
$100.00,52,23000
$200.00,79,34125
... And So on.
I am trying to figure al this out, and know i am going to need to do a for each loop. but I am needing the Data when it is Imported to look like this:
PropertName,4-18-2012,$100
PropertName,4-18-2012,$200
So i need touse the date from either the file name or date the actual file was created. Please advise. There are literally hundreds of file so i don't want to have to process each one individually.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As a side-note: if you have any influence on the naming of those files, I suggest to change the convention to something like:
20120418-PropertName.csv
Advantages:
20120418-PropertName.csv
Advantages:
year is in the file name as well, it will prevent you from throwing your PC out the window next year
files can be sorted chronologically on name - not to be underestimated: the SSIS ForEach loops over files sorted on name
Does it have to be an SSIS package ?
Is this a once-off or a recurring job ?
Can we get a sample to test (are column headers in there) ?