Delimited Text to 10 seperate columns

[vbaInet]

And please next time don't give us "simple cases". We want to see the full picture.

 

[memaxt]

There's no simple process to follow when it comes to this sort of case. You will need to write a more sophisticated parsing function for it work.

You can look at using the Import Wizard and see if that helps.

When I use the import wizard it sends some of the data to another row. i'm not sure whats casuing the line break. when all the data is seperated by "#" what do you think?

 

[vbaInet]

In your other thread plog mentioned that you have some new line characters in the text and that would most likely be the case. If this is related to that thread I would advise that you only post once. You can imagine that it's a waste of our time if several people are working on the same issue on different threads, it's frowned upon.

How many parts do you expect from that one string? And why does it come in that format?

 

[memaxt]

In your other thread plog mentioned that you have some new line characters in the text and that would most likely be the case. If this is related to that thread I would advise that you only post once. You can imagine that it's a waste of our time if several people are working on the same issue on different threads, it's frowned upon.

How many parts do you expect from that one string? And why does it come in that format?

Hi this one is different to the other thread as there are no characters that cause linebreaks that i can see.

• Basically I have linked Outlook to Access where it automatically imports new email.
• Each email contains Delimited Text
• So i append the delimited text to a table
• Export the table to .csv as it contains delimted text
• Link the exported file i just created using the wizard where i hoped that it would split all the delimted text into fields upon (linking).

Delimted data (.csv) attached

 

[memaxt]

I really need to find away to remedy this issue as I need foward the data as and when it comes in to my colleagues....are we getting to the idea that it may not be possible?

 

[vbaInet]

When you say each email, you mean the body of the email itself is delimited? And is it the body of the email that you attached?

 

[vbaInet]

• Export the table to .csv as it contains delimted text
• Link the exported file i just created using the wizard where i hoped that it would split all the delimted text into fields upon (linking).

Also explain why you need to export to a table and link back again?

Basically, what is the whole point of this exercise? You want to email your colleagues some file?

 

[memaxt]

When you say each email, you mean the body of the email itself is delimited? And is it the body of the email that you attached?

Thats correct, I wanted the body of the email (delimited text) go to master table into relating columns.

So what i have down is export the email to .csv as soon as it comes in, then link the csv back to the database where the wizard creates all the columns for via the separator.

Then append to master table.

 

[vbaInet]

"1068#reman#(0161) 4957#Hospice##Gill Acreman#87I2567E#Stockport#St Ann's Hospice#St Ann's Road North#Heald Green#SK8 3SZ##ST/N D Foulkes#79i3237e#29-08-2014#No#No#N/A##No##Hospice #Administration error###Other please specify##Oxynorm#5mg / 5mls#Solution##25mg# breakthrough dose at 08h30. *Nurse did not administer regular dose at same time. *This was subsequently missed.#######################Green 1-4#Yes#given advice only#No harm##Nurse did not follow procedure of writing on board that medication was due
#As patient had received breakthrough dose, patient did not have any additional pain#Incident was not realised until 14h00. *Further breakthrough given.
#Nurse made aware of error and reminded of procedure##Yes - All staff advised###gac.uk#I agree#"

Break the above down into the fields/parts you would want them to go into.