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.
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.
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
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.Originally Posted by OTWarrior
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
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
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
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.
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"?
Yes, i need all the fields.
May i know how to create "Import Specifications"?
tks!
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.
ok, will try. tks OBP...
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.
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.
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.
No, not Excel.
Import the whole file in to one Access Memo field.
Hi OBP,
I've imported the 2 text files into one Access Memo field. Tks.
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.
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.
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.
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!