Import from Fixed Width Column Text file Problem

Question

Post reply

Import from Fixed Width Column Text file Problem

sthornton

Ten Centuries

Points: 1333
More actions
March 2, 2005 at 6:30 pm

#362095

Hi,
I need to import from a Fixed Width column file. The problem is that the last column of the file is optional.
Eg the last 5 characters of a line may look like DR C, but the next line ends at the DR characters.
SQL server hence thinks that the end of the row has been reached.
Is it possible to make SQL server know that the file is say 80 characters in width, and use the WIDTH as the row delimiter, rather than the carriage return-line feed it is using now?
The other options in the Row Delimiter field option do not help.
I am stuck with the file structure, an external company provider it.
Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

wz700

SSCertifiable

Points: 7133

March 2, 2005 at 7:21 pm

#545085

in DTS, set Row delimiter as <none>

philcart SSC-Forever Points: 47796 More actions · Answer 2

When you initially setup the file connection in DTS make sure the first row has the last column populated. This is the important part.

Then select "Fixed Width" instead of "Delimited" in the file format properties. This will allow you to set the width for each column in the file.

Once the columns have been defined, it doesn't matter if the last column is populated or not. You only need it populated so that DTS picks up the correct length.

--------------------
Colt 45 - the original point and click interface

sthornton Ten Centuries Points: 1333 More actions · Answer 3

Hi,

I tried what you suggested, but it seems that the carriage return and line feed is being counted as part of the ling length if you nkow what I mean

Say the line is 230 characters long, the crlf characters now appear at the start of the next line.

Do you have any ideas to get around that?

wz700 SSCertifiable Points: 7133 More actions · Answer 4

wz700

SSCertifiable

Points: 7133

March 2, 2005 at 7:57 pm

#545091

Than add 2 into line length.

philcart SSC-Forever Points: 47796 More actions · Answer 5

Did you select CR/LF as the row delimiter? Selecting "Fixed Field" is only for the columns. You still have to supply the appropriate row delimiter.

--------------------
Colt 45 - the original point and click interface

sthornton Ten Centuries Points: 1333 More actions · Answer 6

Hi,

I had it working earlier, but for the life of me, I can't get it to work again. I lost the package when my computer crashed.

I set the type to Fixed Width, I set the row width to 233 (the last two chars are crlf), but on the next page what do I select? Is the column supposed to be the same width as the row eg 233?

I used 233, but the carriage returns still effect what is imported.

sthornton Ten Centuries Points: 1333 More actions · Answer 7

thanks guys,

figured it out: row delemiter = crlf

thanks for all your help.

sthornton Ten Centuries Points: 1333 More actions · Answer 8

Hi,

Actually, the answer is Delimited, with row delimiter crlf, and a column delimeter of (I started typing "{" .. the rows populated) so I left the delimiter as "{"