How can I Parse this from a TXT file

[Ste4en]

Can anyone help me sort this data out please. I attached a file of the typical data. It consists of three fields: ID Number, Name, DataString. The (cr) signifies the next record. Two records are below. The data is justy one long text string.

....(cr)"429088930","Robert E Watkins ","692026636666",(cr)"206844084","James Thomas ","642036636466",

What i want out is a table of

ID Number Name DataString


Any guidance appreciated.

thanks
Steve

 

[Tiro Cupidus]

One way would be to open it up in Microsoft Word and Find and Replace all '(cr)' with '^p'. Save it again as .txt and you have a comma separated values (csv) file.

 

[The_Doc_Man]

The text import wizard should be able to import the file because it appears to be properly comma-delimited, with text fields enclosed in quotes. This is a File >> Get External Data function.

Be warned that you might have to tweak the field sizes because there are defaults on imports of this type and they are INVARIABLY bigger than you thought they were!

 

[Ste4en]

Thanks for the responses:
I have been trying File/Get external data and browsing to my text file, this opens the wizard, there is no option in get a .csv file. The wizard shows hundreds of columns - one per piece of data, the (cr) or ^p part of every third heading.

I don't see where I tell it to start the next record ot where the text wizard recognizes the next record.

What am I doing wrong.

thanks

 

[RayH]

Is this what you mean?
I'm not sure but I suspect that the (cr) code is in the wrong place in your sample.txt file.
What data is supposed to be in what field?

 

[The_Doc_Man]

Well, there is always the swindle of opening the file with something like Wordpad and immediately doing a Save As, then selecting "Text file" and a new file name.

If Wordpad will read the file in the first place, it can perhaps change the file mode.

If that doesn't work, try the same trick in Word.

Please note that if you have a minimum install of Word, this might not work because you need the Office text import wizards in order to import various file types.

 

[The_Doc_Man]

Wait, I just had a horrible thought...

when you say that the record contains ....

(cr), ".....", "....", ".....", (cr), ".....


Is that the actual Carriage Return character or a representation thereof by the four-character sequence "(", "c", "r", ")" ?

 

[Ste4en]

Ray H - that is what I want.

I think the sample data which I took a cut from a larger file did not start and end on the right field - records 2 to the next to last are what i need.

So how did u do it, please...

Thanks

 

[Ste4en]

The Doc Man...

Yes it is a just 4 characters.

 

[The_Doc_Man]

OUCH! I'm not going to ask how it got that way. I don't want to know.

OK, use WORD.

at the top of the file, prepare to do a FIND AND REPLACE.

In the FIND box type "(cr)" (as four characters)

The dialog box has this button, "MORE," that you need to click to get to the extended FIND/REPLACE options. You need to click the Special button that is one of those extended buttons.

Now click in the REPLACE box, then use the Special button to define a manual line break or a manual paragraph break.

OK, now do the REPLACE ALL.

Now SAVE AS to file type "Text Files"

 

[RayH]

Did it just like the Doc_Man said.
Glad to help.

 

[Ste4en]

The Doc Man - that worked fine I see Ray H did it the same way -

seems like I need something in the data I get i.e not (CR) but l^

So if I get the data with the right carriage return symbol can I get this to work in Access??


thanks for your help...

 

[Tiro Cupidus]

Isn't that Word thing what I said? =) Well, I used a paragraph mark, but I think that works too. '^l' would be the line break.

 

[The_Doc_Man]

In WORD, the trick is knowing what gets translated to what. I guess if you want to export to a .TXT file, use the hard paragraph break because I think that is the closest thing to a real carriage return character when you translate it.

Once you have a file with hard carriage returns and three fields that are comma-delimited, it should import just fine.