December 19, 2003 at 2:06 pm
Excel has a feature I often find useful - "Treat consecutive delimiters as one". Can this be done in DTS? Or do I have to write code such as replace(text_row," "," ") for space-delimited data, then save & re-import?
It seems odd to me if there isn't an easier way to do this. I'm new to using DTS for anything other than straight transfers. Any help would be very appreciated.
Data: Easy to spill, hard to clean up!
December 19, 2003 at 3:41 pm
You can use multiple delimiters in the DTS import/export utility.
When you import a text file you will reach a screen that asks you if the data is delimited or fixed width. Choosing delimited sends you to a screen where you can choose the delimiting character. If you choose other, you can enter your specific delimiter character(s). For instance, I have a file that has a comma+space as the delimiter and it works fine in DTS.
Michelle
Michelle
December 19, 2003 at 4:05 pm
I noticed that too. But my import data is space delimited & has varying amounts of spaces between data.
I did get import to work by DTS to another text file via multiple replace statements to eliminate spaces, then importing from the new text file into a sql table.
Must be a better way. Maybe by ignoring all nulls that occur due to consecutive delimiters?
The only normal people are those you don't know well - Oscar Wilde
Data: Easy to spill, hard to clean up!
December 22, 2003 at 8:16 am
Is it possible that your file has fixed width columns instead of delimiters? If the spaces are used to make a column uniform in width down through the table, you might try using the fixed width feature in DTS when importing the text file. DTS will give a screen where you set where the column divisions are. Take a look at it anyway and see if you can line up the data.
Michelle
December 23, 2003 at 9:43 am
No, it's not fixed width, at least not consistently enough.
Thanks for the advice.
The only normal people are those you don't know well - Oscar Wilde
Data: Easy to spill, hard to clean up!
December 30, 2003 at 10:32 am
OK - I guess there's no DTS version of Excel's option of treating consecutive delimiters as one.
I'm really not much on DTS, so I thought someone else would come up with a better solution.
My solution is to
1. transform the original text to csv via VB replace statements & save in a csv file.
Function Main()
Main = replace(replace(DTSTransformStat_OK," "," ")," ",",")
End Function
2. import the csv file to a SQL table. (Comma delimited text)
It's not fast, but it works. It's a lot faster than find/replace in notepad. Excel is not an option - the data files have too many rows.
Maybe I don't need the step of saving the csv file? I will be looking at automating this in the future & need the ability to change the source & destination names as appropriate.
Data: Easy to spill, hard to clean up!
December 31, 2003 at 6:54 am
To automate the file save/rename process try using the File System Object. It's quick and easy.
In combination with global variables and the Dynamic Transformation Task you can build file names, move/delete/rename files and set import and expr0t paths.
Matt,
Peace Out!
Regards,
Matt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply