Someone else ask this question, but there were no responses so i'm
trying again.

"I am trying to import a fixed length field text file using the
transform data task. When I run the step not all of the records get
imported. I get no error messages or warnings. I can import the same
file into both Access and Excel and get all records."

I've looked at the file with a hex editor and do not see any unusual
characters.

Any ideas as to why this would be happening?

The problem could be with the data. If you defined the file as fixed-width,
this means that every record in that file should have the same number of
characters. Without looking at your data that would be my best guess.

"gtm808" wrote:

Hello AK,

Thanks for the response!
I define the file as fixed when importing into Excel and Access and it
imports all the records.
Would you know why the same text file is interpreted differently by
SQL?

gtm808

I'm not an expert in this area; however I believe that the import process in
Excel and Access behaves a little differently than of SQL Server. What SQL
Server is doing is combine your data until the fixed number of character are
reached (which I believe is determined by the first row in the file -
excluding the header row). As an example I created the following data:

===== Begin Sample Data ==========
Row1Field1Field2Field3Field4
Row2Field1Field2Field3
Row3Field1Field2Field3Field4
Row4Field1Field2Field3
Row5Field1Field2Field3Field4
Row6Field1Field2Field3
Row7Field1Field2Field3Field4Field5
======= End Sample Data =========

The data above will import fine in excel and access because both those
program assume (even though you specified that the data is a fixed number of
character long) that you mean each field is a fix number of character, and
not each row is a fix number of characters. SQL Server assumes the opposite
if true. Based on the above data, SQL Server will import the data as:

Row1Field1Field2Field3Field4
Row2Field1Field2Field3Row3Fi
eld1Field2Field3Field4Row4Fi
eld1Field2Field3Row5Field1Fi
eld2Field3Field4Row6Field1Fi
eld2Field3Row7Field1Field2Fi
eld3Field4Field5

The work around that comes to my mind is:

1. Load the data into a stage table then move your records using T-SQL.
- OR -
2. Create a preprocessor step that “cleans” the data before loading, for
example:

============ BEGIN VBSCRIPT CODE ============

Dim objFileSystem
Dim objInputSteam
Dim objOutputSteam
Dim strCleanedDataFileName
Dim strInputFileName
Dim strInputString
Dim strStringLength

Const FOR_READING = 1
Const MAX_RECORD_LENGTH = 86

strCleanedDataFileName = "C:\cleaned_File.txt"
strInputFileName = "C:\InputFile.txt"

Set objFileSystem = CreateObject("Scripting.FileSystemObject")

Set objOutputSteam = objFileSystem.CreateTextFile(strCleanedDataFileNam e,
TRUE)
Set objInputSteam = objFileSystem.OpenTextFile (strInputFileName
ForReading)

Do Until objInputSteam.AtEndOfStream

strInputString = objInputSteam.ReadLine
strStringLength = Len(strInputString)

' If the length of the input string is less than MAX_RECORD_LENGTH then
fill
' the remaining characters with spaces. This will insure that each record
is
' the same number of characters.

objOutputSteam.WriteLine strInputString & space(MAX_RECORD_LENGTH -
strStringLength)

Loop

Set objOutputSteam = Nothing
Set objInputSteam = Nothing
Set objFileSystem = Nothing

============= End VBSCRIPT CODE =============

I hope this helps, let me know if it does not.


"gtm808" wrote:

AK,

Thank you very much. I think the script will help.
Any suggestions on what type of file I should create rather than a
fixed field that SQL will have an easier time importing?

thanks again,
gtm808