Delimiting Records

[Onenamer]

I am trying to import several hundred records into Access from a txt file but I can't find a way to separate the file into individual records. A portion of the file is given below and I would welcome any assistance:

'C Olive Teece', 'Census, 1940', 'birth: 1919 New York', 'residence: 1940 Ward 4, Canandaigua, Canandaigua City, Ontario, New York', 'spouse: John H Teece', 'parents:', 'children:'\n 'Edith Teece', 'Census, 1940', 'birth: 1888 England', 'residence: 1940 Smithtown Town, Suffolk, New York', 'spouse:', 'parents:', 'children:'\n 'St Clair Teece', 'Census, 1940', 'birth: 1867 Pennsylvania', 'residence: 1940 Ward 3, New Castle, New Castle City, Lawrence, Pennsylvania', 'spouse:', 'parents:', 'child: Edward C Teece'\n

 

[pr2-eugin]

Hello Onenamer, Welcome to AWF..

Is \n not the New Line character? How are you performing the import?

 

[Onenamer]

Hello Paul, Thanks for the welcome.
Yes, I thought \n should work but it dosn't. I'm using the standard File Import option.
Brian

 

[Uncle Gizmo]

In the sample you show are there any reoccurring sets of data? For example I see that you have the words: census, birth, residence, spouse, parents, children.

Does every record in your data set have parents, children, in other words if there are no parents is there still an entry made for parent but with out any data and if there aren't any children is there an entry made, children but without any data?

 

[Onenamer]

In the original source (FamilySearch), there was a varying number of fields but I used a text editor to include a placeholder. So,there should be seven fields in each record. As a point of interest, if I type ENTER at the end of each record when the file is in the text editor, Access accepts it OK. But with several hundred records, it's not a practical answer!

 

[pr2-eugin]

Okay simple way to do would be, copy the content of the text file in MS Word.. Or open the txt in MS Word and use CTRL+H (Find & Replace) Search For \n and Replace with ^| that should place a new line.. Which Access should be happy to import..

 

[Onenamer]

I've just tried that and Word gives me:
^| is not a valid special character for the Replace With box.

 

[pr2-eugin]

There should be no space between the Power up symbol and line.. Best to type it rather than copy paste..

 

[Onenamer]

I've just tried substituting ^| for \n using Notepad - it was accepted, but when I tried importing the amended file into Access, I got the same result as for \n.

 

[pr2-eugin]

Notepad considers the symbol as just another character, that's the reason I asked you to use Word.. In MS Word the symbol is the shortcut for New Line similar to \n being the New line character in most programming languages.. do not add space between the two chars.. ^|

 

[Onenamer]

Thanks for explaining that - I tried Word again, making sure that there was no space between the characters, but I got the same error message.

 

[pr2-eugin]

OOPS, my bad.. It should be the letter 'l' not |.. so sorry..

 

[Uncle Gizmo]

I only saw 6 but on a second look I see you have Children & Child ... Please explain..

 

[jdraw]

onenamer,
What is your plan once you can process the text file? Are you planning a genealogy database? Do you have a database design? Being able to read the text file is one thing, but a genealogy database is something else.
Good luck.

 

[Onenamer]

Thanks for that. The incoming file now has errors so will have to do another.
Will let you know how I get on later.
Brian

 

[Onenamer]

Hi Uncle Gizmo,
Yes, the source uses Child for one, and Children for more than one.

 

[Onenamer]

Hi jdraw,
Thanks for your interest. The database is no problem - I've been doing a one-name study for some years.

 

[gemma-the-husky]

out of interest, where did the csv come from in the first place?

have you just typed it all in, or is it in a spreadsheet, say? eg - if it's in a spreadsheet, you can just import the spreadsheet

 

[Onenamer]

Hi Gemma,
Thanks for your interest. I copied the search results from the web page and then massaged them a bit - several hundred records, so typing ought to be avoided <grin>.