Converting data types

[Stevehewitt]

Hi guys,

Got an Access DB with a 'Memo' field. The end user has been inputting data like so:

Line1,
Line2 Line 3
Line4,
Line6

E.G. Commas and also line breaks.

I need to export the data in this table, including the memo field into a .CSV file (for data importing into a 3rd party app). Obviously this causes some issues, as the line breaks are represented in the export (so the records are broken) and also the random commas introduct additional, non existant fields into the CSV output file.

I'm after any advice anyone can give me. We do have SQL Server as resource, so if someone knows of a way I can get around this by cleaning the data if we migrate to SQL then it's not a problem.

Essentially I need to take the contents of this 'Memo' field, remove all commas (easy enough - search and replace I'm thinking) but also a method to remove the LINE BREAKS.

Any suggestions really appreciated!

Thanks,




Steve.

"They have the internet on computers now!" - Homer Simpson

 

[Remou]

Line breaks:

[tt]UPDATE tblTable SET ThisMemo = FindAndReplace([ThisMemo],(Chr(13)+Chr(10)),"|")[/tt]

Or whatever you fancy as a replacement character.

Generally, a text field should be enclosed in quotes to prevent commas causing a mess in a CSV file.

 

[Stevehewitt]

Thanks Remou,

Maybe i'm missing something, but Access is saying that 'FindAndReplace' is not a valid function.

Cheers,




Steve.

"They have the internet on computers now!" - Homer Simpson

 

[Remou]

Oops. I cut and pasted from an answer I made to a 97 person

[tt]UPDATE tblTable SET ThisMemo = Replace([ThisMemo],(Chr(13)+Chr(10)),"|")[/tt]