Importing a txt file into excel or acces

You might try opening the file in Word and performing the editing neccesary to put the file into database format. If you could identify end/start of a record/series such as the client name always starts with ABC, or always follows XYZ, you could write a simple macro that would parse the data.

A B C

John Willams 12-1-02 John Willams
John Willams 45689
John Willams 4589
Bill Kansas 15-4-02 Bill Kansas
Bill Kansas 34
Bill Kansas 678
Bill Kansas 567
Mary Hall 1-5-01 Mary Hall
Mary Hall 45
It is not easy to understand exactly your lay-out. Suppose that yout .txt can be imported in Excel like column B and C above [originally in A and B]. Add a new row and a new column. Put the following formula in A2: =if(istext(C2);C2;A1) and propagate if with the fill handle. The first row should start with a date.

Excel can open .txt files, and this is a good way to check if the information is organized in a way that Access or Excel can use it. When you choose "Open" from the menu, and the "Open" window appears, click on the right triangle in the "Files of type" space and choose .txt. Excel will ask a few questions about the text and open the file. You can then see if the data is evenly distributed in the same number of columns for each record.

I also like to use Excel because I can use "drag and drop" to move data that is out-of-place, whether because of data entry errors or some other reason.

Once the data is in order in Excel, you can save it as an Excel file (extension .xls). Access very easily imports Excel files into tables.

I would do it in VBA. The following would work using DAO:

first import it in a table called for example tbltemp. Make sure it adds a primery key.
Next I would create a table with the right fields in it called tbldata.

Next I would use the following in VBA

dim rs as recordset
dim rs2 as recordset
set rs = currentdb.openrecordset(select * from tbltemp order by id) 'where id is primery key field
set rs2 = currentdb.openrecordset(select * from tbldata)
with rs
.movefirst
while .eof = false
if rs(id) mod 2 <> 0 then ' you know you are at the first line that belongs to the that record
rs2.add
rs2(fieldname)=rs(fieldname)
else ' then you know you are at the secont line of the previous record
rs2(fieldanme)=rs(fieldname)
rs.update
end if
.movenext
wend
end with
rs.close
rs2.close

The above will only work if every record has 2 lines.
If you need any more help just email me.

It looks as though you want some easy solution, but there isn't one. Each of us has given you the clear message that you need to bite the bullet and somewhat massage the data piece by piece, but you don't want that answer. I do quite a bit of dataconversion, and there are some times when we just have to buckle down and get to work to change the data a bit.

Hello nicholasP,

You can fully automate the process provided you can programmatically (logically) identify a unique word or a unique sequence of words, not repeated elsewhere in the record, by which a new record starts.

For example, every new record may start with the sequence 'Name:'.

I may be able to help build the necessary logic if you can post a few sample records.

With best compliments,

SanganakSakha,
Effective and Simple Solutions To Small Problems, No QuickFixes

I have posted an example file on:
http://www.psarros.com/data/

As you can see, the data for each client comes on a separate "page"

Any help will be greatly appreciated

Take a look at the example file on:

http://www.psarros.com/data/

thanks

Point value changed by question poster.

Point value changed by question poster.

This question was closed by the author