Import Issue w/ Non-Whitespace Characters

[JayKusch]

Importing a large record collection from a web server log file. approx. 25-30 million recs. No biggie there.

Problem is that we encounter non-whitespace characters that blows up the import.

Is there any feature of SQL2K that can override these characters when it encounters them ... or must this cleaning of non-white space characters be done before the import?

Does DTS have any built in functions to handle this?

Thanks

 

[tlbroadbent]

I'm unclear what you mean by Non-Whitespace characters. Precisely what is causing the process to blowup? Do you get a meaningful error message?

DTS can be used to transform data. You can include tests in VB or JavaScript to determine if data is valid or not. The script can be used to drop records you don't want to import or to change the data from invalid to valid.

Check BOL or Internet resources to learn the details of setting up a transformation script. Also, see faq183-692. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[JayKusch]

Morning ...

Terry, here is a description of the Non-whitespace characters i have been trying to work with.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q292588

May be Unicode. Looking at the Apache Server manual right now on log file descriptions. I have found records that have Non-Ascii characters in the like an 'e' with an accent mark above it as well as what looks like a fat double pipe character. like '||" but with no space between the '|'.

I know i can have someone write a script to clean up the file as it is imported, but i was wondering if SQL2K had a built in process to clean it up on the fly?

Thnaks for all the great help here and keep it coming.

 

[tlbroadbent]

Have you attempted to import using BCP rather than DTS? The article says that importing with BCP should work even when DTS fails. I would do that next rather than mess around with transformations or tweaking the input file.

FYI:

When I suggested a script I was talking about the DTS script that is created when you import a text file. The simplest script simply assigns the text file columns to the appropriate columns in the table. However, the transformation script can be modified to handle the transformations that I mentioned plus many others. This transformation (which can clean the data) occurs "on the fly" during the import process. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm