Consulting

Page 1 of 4 1 2 3 ... LastLast
Results 1 to 20 of 80

Thread: How to import a text file with more than 255 fields into Access

  1. #1

    How to import a text file with more than 255 fields into Access

    I have a text file with 267 coloumns. When trying to import via access, the coloumn will be auto truncated at from 256 coloumn onwards. Anyone can advise on how to import all coloumns from these text file into 2 tables in the Access? tks in advance.

  2. #2
    VBAX Mentor OTWarrior's Avatar
    Joined
    Aug 2007
    Location
    England
    Posts
    389
    Location
    Firstly, why would you have columns in a text file? Why not in Excel?

    Also, why do you have so many columns? How many rows are there in this text file?

    Have you tried copying the information into excel and importing it?
    -Once my PC stopped working, so I kicked it......Then it started working again

  3. #3
    Quote Originally Posted by OTWarrior
    Firstly, why would you have columns in a text file? Why not in Excel?
    Because this is an auto downloaded file from an application system.

    Also, why do you have so many columns? How many rows are there in this text file?
    Actually it is my mistake for not knowing that the columns requested is more than 255. It is too late to change the request to reduce the columns. As for rows is unpredictable, may be will hit up to 10000 rows.

    Have you tried copying the information into excel and importing it?
    Sorry, dont really understand your question. However i've tried using the import text wizard in excel to import the data. I need to import 2 times, ie manually exclude the fields that is more than 255 for the 1st import, then exclude the first 255 fields for the 2nd import.


  4. #4
    VBAX Tutor
    Joined
    Dec 2006
    Posts
    220
    Location
    Another way of doing this is to make two copies of your text file and link to the first text file retrieving the fields you want to the first table and then link to the second text file and retrieve the remainder for the second table. HTH
    "Intellectual passion occurs at the intersection of fact and implication."

    SGB

  5. #5
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,285
    Location
    Use two tables who are relative to each other (if possible). Let's say column 1,2,3 is customerid, customername, customer address and 4, 5, 6 is some other data. If the customerid is not present in first table, add 1,2 and 3 to first table and the second table add only the customerid, skip 2 and 3 and add 4, 5, 6.

    Charlize

  6. #6
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    If the text file has a header row and the fields are delimited why not use Jet 4.0 and write 2 SELECT INTO.... queries using the Text Driver... this permits selecting both the fields and order they appear in the output tables. .02 Stan

  7. #7
    Hi all, tks for all the suggestion. But pls excuse me for being asking stupid question, ie how can i automate this importing via access? Reason: this will be my daily process so it will be good if the process is automated. tks.

    Hi Stanl, may i know how to use Jet 4.0 that you've mentioned? Can you guide me because i am not very good in Access programming. tks a lot.

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    You can use VBA to automate the import of the text File.
    But you need to create 2 "Import Specifications" to handle which Fields you actually want. It sound like you do not actually want all of the data (Actually it is my mistake for not knowing that the columns requested is more than 255).
    Do you actually need all the Fields?
    Have you looked at "Import Specifications for Text Files"?

  9. #9
    Yes, i need all the fields.
    May i know how to create "Import Specifications"?

    tks!

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    Look at the Main Menu>File>Get External Data>Import and when you select File Type "Text" and then you select a File and it shows the data, there is an "Advanced" button on the bottom left that takes you to the Specification where you can "Skip" fields. You can also save the specification it this point for future use with VBA code or manual Import.

  11. #11
    ok, will try. tks OBP...

  12. #12
    Hi OBP,

    I hit problem.

    Thought of import the text file 2 times, ie 1st file import the max 255 fields & 2nd file import the remaining fields. However, i cannot view all fields when trying to import. Then how do i skip fields? I hv attached the sample text file & error screenshot. Hope to hear fm you soon! tks.

  13. #13
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    winxmun, I have looked at your data.
    Import it into one field and I will create you some VBA code to split out what you need from the Memo field that will be created.

  14. #14
    Hi OBP, sorry to reply so late because was very busy recently.
    Are you saying that to copy all the fields fm the text file into one cell in Excel?
    Currently I am manually importing the text file into 2 worksheet via Excel. However i would prefer to auto import the text file via Access because i have all my reporting/ programming is in the Access file. tks.

  15. #15
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    No, not Excel.
    Import the whole file in to one Access Memo field.

  16. #16
    Hi OBP,
    I've imported the 2 text files into one Access Memo field. Tks.

  17. #17
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    Ok, which parts do want to keep and which parts discarded?

    Are you sure that the data has been transferred, it only appears to have headings in those Fields?

    can you email me the fiel or files?
    Last edited by OBP; 09-01-2008 at 07:38 AM.

  18. #18
    Hi OBP,
    I need the whole files. Can you split into 2 tables during the automation.
    Currently, the file contains header only. No data has been transferred yet.
    Do you mean the db file or text file?. tks.

  19. #19
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    I have analyzed the Post_Approval_Accepted Headers and there are 264, you could split this in to 2 tables of 132 each. There are 267 field headers in the Post_Approval_Disbursed Table. They could be split 133 and 134.
    Do you want to create those tables manually, or have VBA create them?
    I now need the data to work with to create the VBA to put values in the Table's Fields.

  20. #20
    Hi OBP,

    I've re-attached a new db file with data. There are 267 fields for both tables. No problem to split into 2 tables of 133 and 134. I would prefer to have VBA code to create them.

    tks a lot!

Posting Permissions

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