problems importing multiline data from excel

[Milothicus]

when i import a spreadsheet into a table, there's one column that contains multiline data. for some reason, in the imported data, hard returns (alt-enter) are converted into squares, and the line breaks are in new places.

any idea how to avoid this? or how to go through programmatically and look for these squares? they're not a standard ascii character, so i don't know how to write a program to look for them and change them back into hard returns.

any ideas?

 

[fasternu]

there is a product called Monarch that you may need to scrub this data before moving to access or you may need to write some vb code to clean the data before you get the data to access.

 

[Milothicus]

My plan was to write some VB code to go through each one of these fields and put the line breaks back in, but i can't find any way to find the squares. it's not an ascii character, and actually, i don't know how to search through the data one character at a time.

actually, now that i think about it. i have no idea what to do. there ARE line breaks, but for some reason, they show up as these squares. i want to keep the line breaks, but i need access to show them as line breaks...

i can't find data scrubbing software that looks like it will do what i need.

 

[KeithG]

To step through each chatacter you could use the Mid function. To test the character to see if they are the squares you could use the Chr function which returns a string containing the character associate with the character code. Look in VBA help at charcater code 0- 127.

 

[Milothicus]

i've been playing with the mid function in excel, and it works, sort of...

i can search through, and it looks like the one that's creating problems is code 10: NL Line Feed, New Line

ok, so i can take them out... but i still want to have the line breaks in there. what character should i be inserting instead? i'm back where i started, replacing line feeds with line feeds...

i don't understand why data out of Excel doesn't import properly into access. wasn't that the entire point of the 'office' suite?