Importing TXT from non-Access export....can't read Layout

[mykonosman]

Would anyone be able to help me read and convert the attached file layout so that I can import the file into Access 2003. The file was exported from a non-Access database system. I have attached a very small extract of the TXT file if that helps.

Thanks.

[ssanfu]

I have done something like this in the past. I won't be able to look for the code until I get home.

Is the is a one time thing?
Do you have a table set up for this data? If you do, please ensure it is in A2000 format and post it, please.

[ssanfu]

More info.

You can use a table or code it into an array. You would store the field starting column and the length for each field in the "extract file layout". For example the first field is skipped (unused). So the first entry would be 2 for the column, 2 for the length. Then 4 for the column, 9 for the length.... then the code would be something like:

DO UNTIL EOF
   Read a line from the text file into a variable

   For i = 1 to 18 ' there are 4 unused fields
      'read the starting column and length into variables. 
      'use the MID() function to get the sub-string from the input string
      'update the table
   Next

LOOP

After looking at the layout, why does the Key, Block, Block-N all start at column 4? Hmmmmm....Very strange.....


So you can see that it is fairly simple. Make sure to add error checking.

[David04845]

open it in note pad then go to edit and replace

replace " " one space bar press with , then hit replace all.. wait for it to go through the entire document.

then import the file into excel using , as the delim and mark treat consecutive delim's as one.

you will have some minor cleanup left because of some of the long addresses but you can sort view the cells and clean it up pretty quick for import.