DTS problem with AS400 flat file download

I'm having trouble importing a text file that was ftped down from an AS400 fixed length flat file. The text file appears to be formated correctly when I open it (1 record per line fixed format) but when I try to import it ASCII delimited it doesn't appear to be in that format and I'm not familiar at all with the AS/400 file formatting. This seems to only be a problem when the last field in the file is variable length. If the last field is a char 1 field it imports OK. Any idea on what I'm doing wrong with the import? I'm I going about this all wrong? Is there an easier way to get data into SQL Server from this AS400 flat file?
Any help would be greatly appreciated!!
Thanks,
DeidreDo you have IBM Client Access installed on your SQL Server? If so, you can create an ODBC connection to the AS/400. I do this for several AS/400 files at work and it couldn't be easier.|||No I don't have IBM Client Access installed on our SQL Server that's why we were ftping to a text file then trying to import it. I was trying to find a download for the IBM Client Access on IBM's website but I guess that's something you have to buy extra?|||I was under the impression that Client Access is something provided when you purchase an AS400. What kind of terminal emulator do your users use now to access the 400? The ODBC connection is what you really need more than all of the emulation that Client Access provides.

I hope I'm not beating around the issue here, but if you can get an ODBC connection, it would be much easier than the text file download.|||No thanks for the help! I think that would be alot easier too - if I can get the ODBC connection.
I know nothing about the AS400 side and I think that is my main problem. There is another group that handles the AS400 system and I just need a file they created to import into my system. I've been trying to get information from the other group with not alot of luck that is why I was trying the text file download. I will try to see if they can get me Client Access.|||You haven't mentioned how your doing the "import"

Is this AS400 file from DB2?

Also, what's the lrecl and how many rows?|||Do you have a file layout for the AS400 file? If the file is fixed width you will have to set the width of each column manually based on the file layout.|||You mention ascii delimited when referencing dts, but you also mention that the file is fixed length file - which is it. You have the option to import a fixed length field file as well in dts.|||Even easier...

If the file is less than 8000 bytes...dump it in to a temp table with 1 column..you can then parse it out, do validity checks, ect...

CREATE TABLE myTable99(Col1 varchar(8000))
GO

BULK INSERT myTable99 FROM 'd:\data\filename.txt'
GO

SELECT COUNT(*) FROM myTable99
GO|||I am importing using the dts wizard. I did try the fixed length ANSI option and it doesn't work either. It seems that all the fields are fixed length except the last field but none of the row delimiter options work for me. When I choose {CR}{LF} it catches the end of the first row then wraps the other rows that are longer than the first row. If I try any of the other row delimiter options I get this message "Could not find the selected row delimiter within the first 8 kb of data. Is the selected row delimiter valid?" but I see a double vertical line separating each row.
I am having this problem on all the files except 1 that I am trying to import from the AS400. The one that I am not having problems with last field is a 1 char field not variable length.
The AS400 guy said the file is not DB2 its just a AS400 fixed length flat file and I have the file format for all the files. The simples one is one with 2 fields- 1st field 2 chars - 2nd field 30 chars - variable row count.
Any other suggestion?
Thanks again for all the suggestions,
Deidre|||So it looks like it is pipe dilimited. Have you tried that delimimter yet?|||I don't see pipe as a row delimiter option - I've tried all the options I see - {CR}{LF}, {CR},{LF},Semicolon, Comma, Tab, Vertical Bar and none of them work.
Deidre|||In the Row Delimiter drop down you can add delimiters. Just click in the drop down and put in a pipe.|||Although vertical bar may be the same as pipe. Just to see if you can get it in a database, try importing it into access. The wizard is a little easier.|||It would be a lot easier if you could post a sample...

You say

but I see a double vertical line separating each row.

Do you mean row? Or Column? Quite different you know...

How much data are we talking about?

Did you try my example...should be pretty easy|||SHICKS -I tried entering the pipe delimiter in the row delimiter but it still didn't work. It imported in to Access great just used the wizard fixed width options and set the column breaks after the 2 and 32 chars.

--
Brett -- This works but I still don't see why I can't do it will a dts import:
CREATE TABLE #tblTemp(Col1 varchar(32))
GO

BULK INSERT #tblTemp FROM 'C:\test.txt'
GO

SELECT * FROM #tblTemp

insert into tblStageMajor
(sMajorCode,
vsMajorDescription,
dtImportDate)
select substring(Col1,1,2), substring(Col1,3,30),getdate()
from #tblTemp

drop table #tblTemp

I attached the file that goes along with this as an example of what I'm trying to import. I have several different files but this is the smallest. 2 columns 32 char width total - 27 rows - 1st column 2 chars -2nd column 30 chars.
The largest files is still only 147 char total row width, variable rows count(around 10,000 is normal), 12 fields.

Thanks,
Deidre|||I don't see no stinkin pipes (|)|||but I see a double vertical line separating each row
I agree with Brett - no vertical lines in that file. It is a little early in my timezone to see double anything yet - you haven't been sipping from the champagne bottle a little early, have you ? :-)|||Originally posted by rnealejr
I agree with Brett - no vertical lines in that file. It is a little early in my timezone to see double anything yet - you haven't been sipping from the champagne bottle a little early, have you ? :-)

And what time zone is that?

Also I tried to import that file...if you play around with the end of line parameter, you'll see that there's some kind of garbage at the end...|||OK I loaded it using my method, and everything looks fine

bizzare...try this

select LEN(Col1) from #tblTemp
SELECT '123456789012345678901234567890'
UNION ALL
select Col1 from #tblTemp

I thought I'd see extra bytes at the end...but I don't..

Hey ANOTHER reason not to use DTS...they just keep stacking up...|||That garbage is 0D0A - typical carriage return/linefeed ending|||Well DTS doesn't like it...did you try to import the test.txt file?|||Yep. Just add the control characters to the delimiter area.

dsweatman - Try the following in your dts script as a transformation:

Function Main()
strSource=DTSSource("Col001")
DTSDestination("Col001") = left(strSource,2)
DTSDestination("Col002") = right(strSource,len(strSource) - 2)
Main = DTSTransformStat_OK
End Function|||Check this out...I added 1 line...it looks like it's taking the line length definition from the first row...

bizzaro...

EDIT: And I just imported it Excel and Access...NO PROBLEMS...

DTS, you quirky little thing...

I even cut and pasted all the rows in to another file..same problem...|||I got it to work if I set the text file options to comma delimited.
and did the following in the transformation.

Originally posted by rnealejr
Function Main()
strSource=DTSSource("Col001")
DTSDestination("Col001") = left(strSource,2)
DTSDestination("Col002") = right(strSource,len(strSource) - 2)
Main = DTSTransformStat_OK
End Function

Thanks everyone!!|||Happy to help and Happy New Year !

Actually, in your example, any of the delimiters work - as long as your row delimter is set for cr/lf.|||Originally posted by dsweatman
I'm having trouble importing a text file that was ftped down from an AS400 fixed length flat file. The text file appears to be formated correctly when I open it (1 record per line fixed format) but when I try to import it ASCII delimited it doesn't appear to be in that format and I'm not familiar at all with the AS/400 file formatting. This seems to only be a problem when the last field in the file is variable length. If the last field is a char 1 field it imports OK. Any idea on what I'm doing wrong with the import? I'm I going about this all wrong? Is there an easier way to get data into SQL Server from this AS400 flat file?
Any help would be greatly appreciated!!
Thanks,
Deidre

hi,

first you confirm have specified carrage return while creating text file
in AS/400, If you are using client access system will take care
regarding carriage return
if you are using CPYFRMIMPF than you should specify carriage return

regards
NANAIAH
MUMBAI