Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267

    Unanswered: File Import & Codepage

    To All--

    I'm trying to import a file into Access. I've tried using an Import Specification but I can't get that to work. The issue is that the file doesn't seem to have any carriage return or line feeds or whatever in the data. This is causing Access to think that the file contains just one long record. Unfortunately, I don't have the ability to have the file format changed so I have to make do with what is given me.

    I have written code to import the file through VBA but the resulting data is just garbage. Does anyone know how to implement their own codepage functionalility so that it will "decode" the input? The codepage is IBM EBCDIC (US-Canada).

    Hope that makes sense. Thanks.

    C

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    http://www.dbforums.com/showpost.php...6&postcount=24 (the 2nd example with NEW at the end of the name (recently added) will let you do the actual importing.)

    Try out the link above if you haven't (especially the 2nd example with NEW). I'm guessing you already have though. You don't need an import specification for importing if the file has a separater value (ie. , (comma) - csv as in the first example below. You can also manually add in the carriage returns into the file but I don't think you want that solution as I'm guessing you need to do this importing often. Normally though, you don't need to worry about the carriage return as long as the data falls into the next line.

    ie...(example of a csv file)
    George, Kohn, 111 Eastwood, Madison, WI, 53527
    Paul, Kohn, 222 Elmwood Rd, Madison, WI, 53577
    Bill Haddock, 44 Somewhere Ln, Somewhere, WI, 53522
    versus
    George, Kohn, 111 Eastwood, Madison, WI, 53527, Paul, Kohn, 222 Elmwood Rd, Madison, WI, 53577, Bill, Haddock, 44 Somewhere Ln, Somewhere, WI, 53522
    (which I'm guessing is what you're dealing with.)

    Once you create a file specification, you can use it for one of the parameters for the docmd.transfertext found in the example above. Usually though there is some character (ie. chr(13) or another) which determines if a new line is made. (I've written some code which searches for these type of characters.) If not and it's like the 2nd example above, you'll probably need to write an import specification or manually press enter at the end of each line to drop the next line down. If it's like the 1st example, the sample importing routine from the code bank should work fine. You could also try simply adding a header row (separated by commas) for the first line in 2nd example and see if the routine will work (then marking true that it has a header file on the Import form example.)
    ex:
    FirstName, LastName, Address, City, State, Zip
    George, Kohn, 111 Eastwood, Madison, WI, 53527, Paul, Kohn, 222 Elmwood Rd, Madison, WI, 53577, Bill, Haddock, 44 Somewhere Ln, Somewhere, WI, 53522
    (this usually works - again, check the box that it has a header row on the import form example.)

    Can you upload the file you're trying to import and post it? I could probably find a simple way to modify the importing example above to import this file. "The codepage is IBM EBCDIC (US-Canada)." - I didn't understand this unless you're trying to break these apart to import into fields. If so, there is a way to do this (but it's not the easiest.)
    Last edited by pkstormy; 06-19-08 at 02:11.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    pkstormy--

    Thanks for the suggestions but unfortunately it's not a comma separated file. I can't post the file itself but this is what some of the data looks like if you try and view it in notepad: "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@". It's seems to be basically the raw byte data from a customer's mainframe. I can't enter my own carriage return - line feeds because I wouldn't have a clue where to insert them. The file contains anywhere from 3000 - 4000 records and it's generated every half hour.

    This is what I ended up doing. A brute force technique but it seems to be working. First I read in 1 byte of data from the file at a time. Then I convert that byte into a human readable character. I've attached the table that I base the conversion on (it's one huge select statement in a function - hey it works!). Then I output one character at a time back into another text file. From that text file I'm able to import into an Access table.

    If you know of any easier way to convert the file into a human readable format please let me know. Thanks again.

    C
    Attached Files Attached Files

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    "The file contains anywhere from 3000 - 4000 records"

    These words indicate to me that you know how many characters each record is allowed to have. If that's so, you can import the file into MS Word - or some other word processor that permits macros - and write a simple macro to insert a Chr(13) every so many characters. Then select the whole text and create a table from it right in Word. Save it as a .csv file. (This can all be done right in the same macro.) Then you can import it into Access and convert it into human readable text.

    Hope this helps,

    Sam

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The critical component is translating the different code pages, not hacking the gobbledegook so that a record-break can be put in.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Sam and StarTrekker--

    You are both right. I do know how many bytes per record which means that I should be able to insert Chr(13). I hadn't thought of that.

    But once that is done then I should be able to use an Import Specification that has the Codepage setup as IBM EBCDIC and import the file directly into Access and Access would then handle the conversion for me.

    Thanks for pointing me in a new direction!!!!

    C

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Good luck.

    Please post back and tell us of your success.

    Sam

  8. #8
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Sam--

    Thanks again...it's working great. I just read in from the raw file the number of characters per record and then I add chr(13). I output that into a "cleaned" file. Then I have a table that is linked to my "cleaned" file using an Import Specification that converts from IBM EBCDIC into ASCII. Tremendously improves the time it takes to import a file!!!

    Thanks again for the idea. You guys rock.

    C

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hey great to see you got it working
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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