Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Dec 2003
    Location
    ga
    Posts
    7

    Unanswered: DTS problem with AS400 flat file

    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
    Last edited by dsweatman; 12-30-03 at 15:47.

  2. #2
    Join Date
    Dec 2003
    Location
    Houston, TX
    Posts
    6
    Do 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.

  3. #3
    Join Date
    Dec 2003
    Location
    ga
    Posts
    7
    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?

  4. #4
    Join Date
    Dec 2003
    Location
    Houston, TX
    Posts
    6
    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.

  5. #5
    Join Date
    Dec 2003
    Location
    ga
    Posts
    7
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You haven't mentioned how your doing the "import"

    Is this AS400 file from DB2?


    Also, what's the lrecl and how many rows?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2003
    Posts
    39
    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.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Dec 2003
    Location
    ga
    Posts
    7
    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

  11. #11
    Join Date
    Aug 2003
    Posts
    39
    So it looks like it is pipe dilimited. Have you tried that delimimter yet?

  12. #12
    Join Date
    Dec 2003
    Location
    ga
    Posts
    7
    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

  13. #13
    Join Date
    Aug 2003
    Posts
    39
    In the Row Delimiter drop down you can add delimiters. Just click in the drop down and put in a pipe.

  14. #14
    Join Date
    Aug 2003
    Posts
    39
    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.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •