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 |
|
speterson
Starting Member
11 Posts |
 Posted - 2007-01-23 : 11:55:44
|
| I have seen a number of examples regarding the design and implementation of a DTS package that loops through and imports multiple text files of the same format into a SQL table. I have hundreds of text files in a directory that need to be imported. In the past I have just done this all manually one at a time, however I feel that this is a waste of my time. So, I am looking for other solutions as each time I need to go through this process the number of files increases (right now I am at almost 800)!Here is my scenario. Please advise if there is anyway to automate this process via looping through steps in a dts package.1) All Files are text files, each with different names of course.2) All Files are TAB delimited ANSI files with a row delimiter of {LF} and a text qualifier of <none>.3) Each file has a different number of lines and could possibly have a different number of columns due to the items listed in #2.4) Each file needs to be imported into its own SQL table with the name corresponding to the file name.Due the sheer volume and size of these files I need to come up with a solution to get the files into SQL without spending days manually importing these.Thanks... |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-01-23 : 12:50:49
|
| There's plenty of ways of doing this for files of the same format. However, unless there's a consistent format or formats to your files, you're going to have a hard time of it. It's point 3) of your list that is the issue. Are you saying that every file could potentially be of a different format?Mark |
 |
|
|
speterson
Starting Member
11 Posts |
Posted - 2007-01-23 : 13:03:42
|
| Each file is formatted the same but may have a different number of records(rows) and a different number of columns. The files would get a different number of columns due to the placement of TABs, LF, etc. listed in #2.Does that make sense? |
|
|
|
speterson
Starting Member
11 Posts |
Posted - 2007-01-24 : 09:56:04
|
| Would there be a way to do this if I only used the first 4 columns of each file? Can you "ignore" all other columns? Then would the automated import process work??I really want to believe there is an efficient way of doing this type of mass import. I cannot imagine that I am the first person to need such functionality based on different file layouts, etc. |
|
|
|
|
|
|
Import multiple Text files to multiple SQL Tables