Importing Text File To Sql Server

[Kruznn66]

I have a large text file with over 31000 lines of data; that I want to import into Sql table called SKU. When I use the DTS Wizard to import the text file into the SKU table I continuously get the following error.

TransFormCopy ‘ DirectCopyXForm’ Conversion Error: Conversion invalid for data types on column pair 32 (Source column ‘col032’ (DBTYPE_STR) destination column ‘Lstgibo’ (DBTYPE_I4))

However, I can import portion of the text file into the SKU table with no problem. Additionally, if I import the text file as a new table (SKU1) it works fine except I have no column headings except Col1, Col2 and so on. When I go to view the diagrams or create a new table it give me the following error:

An unexpected error happened during this process

How and what do I need to do to get this problem fixed. Also can you write a stored procedure (macro) in Sql that would import the data without using the DTS Wizard?

I am a new user using the SQL Server 7.0, and would appreciate any help in the matter.

Thanks,
Travis

 

[grahamm]

I suspect that somewhere in your 31,000 records column 32 has a character string that can not be parsed as an integer number. When you let DTS create the table I would guess that it has built the table with a type of char (or varchar) for col 32. Given that you can load a portion of the file it suggests the first n records are ok, but somewhere there is a malformed record.

This is the approach I've taken in the past:

Inspect the first few records by hand to ensure that column 32 looks like number. If it does then you're going to have to find the suspect record. I'd use PERL (I have a unix background) or a similar scripting language to search the 31,000 records for the "bad records".

 

[rac2]

Wouldn't it be great if MS SQL Server DTS had a feature to skip "bad records", or even save "bad records" to a table.

 

[tlbroadbent]

You can actually modify the script ceated by DTS and have the VB or Java script check for valid data. When creating the DTS package, at the "Select Source Tables and Views" dialog, click on the Transform button. Then select the Transformations tab. Check "Transform information as it is copied to the destination." You can select VBScript or JScript at the bottom of the screen.

At that point you can modify the script to add tests for the validity of data in any column. If the data isn't valid, you can substitute a valid value or skip the row of data. I usually choose to substitute valid values. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.