Problem Importing Fixed Width Text File using DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Problem Importing Fixed Width Text File using DTS

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-02-22 : 18:37:58

Im trying to import a fixed with text file into SQL using DTS.

The text file looks fine when I open it in Notepad. Each row is one record. Ive even successfully imported the file in Access with no issues, I just have to define the width of each field.

However, when I view the file in the DTS panel, it does not accurately portray where the line ends, therefore messing it all up.

Is there another way to import fixed width text files?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-22 : 19:21:52

I have only seen this problem occur when the length of each row is not consistent. Access ignores the error, but SQL DTS will not, so it appears on the surface (using Access) that there are no issues.

In our case, I had to write script to essentially pad the row length to the max length (going row by row).

I assume you are getting either less records than expected, or an invalid row delimeter error in DTS?

The problem is indeed with the flat/fixed width file however.

Poor planning on your part does not constitute an emergency on my part.

Aqua
Starting Member

1 Post

Posted - 2008-03-12 : 10:19:36

Hi, I have problem inporting Fixed length text file into SQLServer(2005) using DTS. Googled on it, and ended up here.
This is what works for me.
In SQL Server Management Studio--> select the database --> right click --> tasks --> import data -->
In the Wizard
Data source : Flat file source
Browse: the file you want to import
Format: Fixed Length
Step2:
Select columns on the left side
Row Width: 97 ( in my file) -- this is the key part
Source data coulns section(where you can priview the data)
click at each column lenght
Ex: I clicked on position 32 to get the black divider

continue like that for each column(ex: Position 35,43,55,66 etc)
Tip: if you want to remove the devider,right click and delete.
Once you do that

click next in the wizard
you can name your columns
Click nect until the wizard finishes download the flat file in to a table.

Hope this helps!

zylo47
Starting Member

1 Post

Posted - 2008-04-03 : 11:08:27

Just an FYI, when using the SQL 2005 import / export wizard to import fixed width data, I had to create an extra column (I called it EOL - End Of Line) to store the CRLF data. I don't know why the wizard wasn't ignoring it like it was supposed to, but if I didn't do this all of my columns were being shifted over.

PepaSnipp
Starting Member

1 Post

Posted - 2008-05-13 : 12:18:50

I had same problem. When I used file format "ragged right" file had been imported without problems.

KarthikThangaraj
Starting Member

2 Posts

Posted - 2008-05-13 : 13:31:41

I have the same problem and im using SQL 200. Does SQL 2000 has "ragged right" option?

KarthikThangaraj
Starting Member

2 Posts

Posted - 2008-05-13 : 13:58:27

How to solve this problem. reading row by row is not feasible since the table has huge data. Please help.