# Text to MSACCESS with Fixed Width



## immortali (May 4, 2006)

Hi,

I don't know if anyone can help me in this or if it is even possible.
I have a text file which is with Fixed Width. As far as that point I have managed to import the file and make into fields. However the problem is that at the beginning of each set of fields there is another field (on another line) that is common for all fields. Example:

Johannes Str.
_John Doe 23, Hoho 283034
_Jane Doe 26, Hihi 256054

Johanness Str. is a common field for both fields. As you may see at the lines below there is 1 space before the field starts.

How can I go about it and have a full databaes with the following:

John Doe 23, Hoho, Johannes Str. 283034


Thanks in advance for your help,


Immortali


----------



## Dragoen (Apr 10, 2009)

Hi immortali,

I'm going to need a better explanation of the problem. Perhaps a larger sample of the original text file. Just replace the name characters with xxxx yyyy for privacy purposes. I am confused by your term "beginning of each set of fields". If you imported the data correctly the record format, each row of data would be essentially the same, or have the same number of delimited fields. You wouldn't have two rows of x number of fields worth of data and then one row with one field of data, and two more rows of x number of fields of data and another row of one field of data. That doesn't make sense but that is what I interpreted from your question. If you can post a sample of the raw text file I'd have a better chance of telling you how to get through the Import Text Wizard and prepare the text file if necessary.


----------



## immortali (May 4, 2006)

I don't know if this might help.http://gianpaul.net/test.txt


----------



## Dragoen (Apr 10, 2009)

Sorry that example doesn't give much help. If I open it with wordpad it has one format, notepad yields a different and stranger result. However I will say it looks like:

First Lastname
address line 1
address line 2
address line 3

First Lastname
address line 1
address line 2
address line 3

repeating....

If this is the case you could write a word macro to normalize the data into comma delimited rows, one address per line. Something like: start at the end of the lastname in the first line and insert a comma, do a delete to bring the second line up after the comma, jump to the end of the line, insert a comma, do a delete to bring address line 2 up, jump to the end of the line, insert a comma, do a delete to delete the blank line, index down and to the end of the next line and start the macro over.

The exact sequence in the macro will need to be tested and adjusted because you could extraneous control characters on the lines, etc. and may need to do some additional conditioning to the file. Of course if there isn't a lot of data you might be able to do it manually.

Once you get to a comma delimited format it will import easily into Access using the Import wizard.


----------



## immortali (May 4, 2006)

To be exact it is

Street name
_Name, House_Name, ID
_Name2, House_Name2, ID2
_Name3, House_Name2 (because they live at the same house), ID3


Street name2
_Name4, House_Name4, ID4


Street name3
_Name5, House_Name5, ID5

The street name is identical to all items below it until another street name appears.
Whoever wrote the txt file (way before my time at the company) made sure that the names of the persons have a space at the beggining of the line.


----------



## Dragoen (Apr 10, 2009)

Because the data is not in a standard repeating format this would require a custom process to filter and format it into useable records. In this case it might actually be easier to dump it into excel. I think a routine to add the street name to each row below it until the street name changes would be easier to write. And don't worry about the leading spaces, they can be easily removed. Lets see if one of the Excel guru's can help.


----------

