September 16, 2005 at 11:24 am
Hi all
Please helpl. I have a flat file and the data as below
"Smith" "John" "09-30-2004"
"Green" "Woe" "08-25-2003"
I need to transfer into a table and I create a DTS to do this job. How do I eliminate the "" around by using vbscript? And convert "09-30-2004" to datetime data type in table. Does anyone have any sample please help?
Thanks in advance!
September 16, 2005 at 12:06 pm
Have you tried this? If you import a text file using DTS there is a field called 'Text Qualifier' - set this to be " (the default) and DTS will just treat everything between the quotes as the field and will not import the " themselves.
As for the date - you need to write a bit of script to get it into the form yyy-mm-dd 00:00 before punching it into a SQL Server datetime field. Or just import it as text and then write an update query to fix it all in SQL Server (a bit more work maybe, but once things are wholly in SQL Server, everything tends to end up being simpler.
Finally - are the fields separated by commas? Looks like spaces from your example. Anyway, remember to set the correct column delimiter before performing the import. Quite what DTS will do with any spaces between quotes (ie spaces in your text fields) if space is set as your column delimiter, I am not sure. Good luck.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2005 at 12:36 pm
I got it with the replace function to replace " to white space then use trim function to get rid of white space.
Thanks for your advices.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply