|
|
Posted by Plamen Ratchev on 01/24/07 02:43
In the past I had to perform similar tasks very often to import text files
generated from mainframe systems (or other third party sources that could
not directly import) to SQL Server. Normally I have done it two ways based
on the tools available:
1) Use a pre-processing tool to clean up the data and then import to SQL
Server via BCP or DTS. Sometimes I had to write my own little utility
program or script to clean up the data, sometimes I was able to find tools
that are available out there. In your case a simple search and replace tool
should do the job (probably better to have command line interface so you can
automate the process). There are many available and if you do not feel
comfortable writing your own tool you just need to Google for "search and
replace" and you will find a few. I did a quick search and here are two
links, but please test and evaluate yourself:
http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb05/hey0208.mspx
http://www.thefreecountry.com/programming/searchandreplace.shtml
2) The second approach is to use BCP or DTS to import the text file to a
staging table with a single varchar (or nvarchar if you have UNICODE
characters in the data) column. This works well if you can fit one row of
the text file into the size of varchar(8000) or nvarchar(4000). Otherwise
you can still split it into multiple columns but processing afterwards
becomes more complex. Then you can use the various string functions in SQL
Server to perform the data clean up, split into columns and insert the final
results into your production table. In your case the REPLACE and SUBSTRING
functions could do it.
Hope this helps.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
Navigation:
[Reply to this message]
|