March 7, 2005 at 2:36 pm
Hello,
I receive a comma delimited textfile on a daily basis with anywhere between 120,000 and 2,000,000 records in it. Now the file will never contain more than 70 columns, but some days it might contain as few as 5 columns.
I am wondering if there is a way I can create a DTS package that will be able to import this file into a standard 70 column table without any problems, and how. What I have to do at the moment is I have to open the file each day (in DTS Designer) and re-map all the columns again. I would love to have a sort of generic package that will recognise the number of columns and then just map it itself.
I did have an Active-X script before but that too 2 hours to complete compared to 5 mins now.
Any help would be much appreciated.
March 8, 2005 at 5:01 am
I can't see how yopu hope to achieve this. How will dts know which columns to map?
You are going to have to use activex to provide the logic behind the import.
If you have a set of rules for import, post them back and here and we'll take a look.
March 8, 2005 at 8:38 am
hai ,,
you can use BCP to import the file and using the format file u can do this..
regards
ilayaraja
March 8, 2005 at 1:10 pm
BCP with a dynamic file format is pretty hard to set up. You need to open the file and figure out the number of columns, then set up the file format, then run the import. Not that easy, really.
But, there is no easy way to do this. I've gotten by this by turning columns into rows. If extra columns are needed for an entity, then it's just an extra row for that entity instead of a column (with a key column that joins them together). You can then use dynamic SQL to build a table (or tables) and turn these columns into rows.
If you can't get the file in a different format, you can do this using vb script; but again it's not that easy.
cl
Signature is NULL
March 12, 2005 at 7:45 am
I think there are a couple of different things to consider here.
First, can you change the file format so that although you may only have one file format that has a variable number of columns filled in? This would be probably the easiest solution because it will eliminate the need for complicated programming. It would also allow for variable columns to be filled in.
Second, the question that has not been asked here is that when you recieve the 5 column sheet is it the same 5 columns everytime? The task is nearly impossible if you are not getting some semblance of uniformity.
Third, is the file named differently for each incarnation of it? If so then you can spend the time to develop the different packages for each file and then execute the correct package based on what file is there.
Conan
March 12, 2005 at 2:26 pm
"Second, the question that has not been asked here is that when you recieve the 5 column sheet is it the same 5 columns everytime? " --YES always the same
"Third, is the file named differently for each incarnation of it?" -- NOPE filename is always the same.
If there is a better way let me know. What I have done up to now is to have a textfile with 70 commas in it, and everytime we get a file from the company I open it and paste the 70 commas in the first row and then I just run my DTS package. This works fine, but if there is a better way let me know.
Cheers
Rykie
March 15, 2005 at 5:47 am
Ryk,
Let's look at this from a weasel's perspective. Someone is going to have to do some work here. Why should it be you? Why not push back to the folks that are giving you the data and ask that they ALWAYs send you 70 columns? Seems that would be simpler than what you'll have to go through in order to handle the unknown. Have them pad it out with empty string, nulls or whatever.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply