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 |
|
taracad
Starting Member
1 Post |
 Posted - 2013-03-04 : 11:24:12
|
| Hello,This is my first post. Wanted to provide the problem scenario.Our marketing department gets a ftp drop of 6 CSV comma delimited files every week. They want to import this file to a new SQL database.The files are the same files from the previous week but with updated entries so it is like a running update.Ideally what I would like is to have a new database created each week once the csv files are in and create tables in that 1 database for each file. So the database named for the Date of the csv files and tables name with the name of the csv file.Is there any way to automate the import into the tables? I was able to do manual import to the tables, but I wanted to know if there was a automated way to do this. Understand that the csv files have over 30 header columns so creating a query to bulk import them would be difficult as I would have to create a schema with a lot of headers.KrisKris Taracad |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-04 : 13:55:54
|
| I would recommend that you NOT create a new database each week. Create a single database, and perhaps even a single table. Whether you need one table or two or three or more tables depends on your processing requirements. But definitely do not create a table for each week. Assuming you create a single table, that table should have all the columns that the CSV file has. In addition, it should have another column to indicate the date on which you imported the data into the table.Once you have done that, you can use SSIS or any of the other methods to import the data into that table. If you are able to successfully import the data into the table once manually, you then can automate it by scheduling the import process as a SQL Agent task (or using any scheduler including Windows scheduler or 3rd party schedulers). |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 00:08:08
|
quote:
Originally posted by taracad
Hello,This is my first post. Wanted to provide the problem scenario.Our marketing department gets a ftp drop of 6 CSV comma delimited files every week. They want to import this file to a new SQL database.The files are the same files from the previous week but with updated entries so it is like a running update.Ideally what I would like is to have a new database created each week once the csv files are in and create tables in that 1 database for each file. So the database named for the Date of the csv files and tables name with the name of the csv file.Is there any way to automate the import into the tables? I was able to do manual import to the tables, but I wanted to know if there was a automated way to do this. Understand that the csv files have over 30 header columns so creating a query to bulk import them would be difficult as I would have to create a schema with a lot of headers.KrisKris Taracad
so far as the csvs are all having no changes in structure(metadata) you can use logic like below for upload. http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.htmlAdd this package execution as a step inside sql server agent and it will execute it automatically based on predefined schedule------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Automating Import of CSV