Need help with Access/Excel

[rajncajn]

I'm currently working with an Access database & have been dumping the information to Excel in order to use some of it's filtering, sorting & editing capabilities. Now I am trying to dump the data back into Access & everything is working great with one exception. In each cell we have line breaks or returns as you might call them, well when you dump the data back from Excel to Access it turns those returns into a symbol that resembles a block rather than the return. I've tried doing a find & replace in Access & in Excel, but neither recognizes the symbol as something that can be replaced. Below is an example of a single cell entry & that is just how it should appear in Access

 

1-320-3

AIR TEST

1-320-1-L

[Kid Cid]

I think you're screwed. Access sees that block as two non-printable characters, a carriage return and a line feed (think of a typewriter). There's no way to tell Access to replace that block for viewing in tables. Ther eis a whole discussion around databases and how to organize the data in them so that a report will show the data formatted the way you want it, but I just can't find the energy to go into it right now. Maybe someone else can help.

[keggerz]

rajn and fyi, kid is still drunk (see the vegas thread)

[rajncajn]

I think you're screwed. Access sees that block as two non-printable characters, a carriage return and a line feed (think of a typewriter). There's no way to tell Access to replace that block for viewing in tables. Ther eis a whole discussion around databases and how to organize the data in them so that a report will show the data formatted the way you want it, but I just can't find the energy to go into it right now. Maybe someone else can help.

I was pretty sure that was what the answer would be. Our own tech group said I may be able to run some sort of script to replace them all. Maybe that will be my next step. Thanks for the help tho.

[jelap1]

This is actually pretty easy to do. Copy the text out of excel, and paste it in to textpad, or whatever text editor you have. Then use the 'find and replace' to replace that "block" character with a blank string.

 

Then copy from the text editor and paste the data in to Access.

 

If you want to send the info to me I could probably do it for you rather quickly. Just shoot me a PM.

[rajncajn]

This is actually pretty easy to do. Copy the text out of excel, and paste it in to textpad, or whatever text editor you have. Then use the 'find and replace' to replace that "block" character with a blank string.

 

Then copy from the text editor and paste the data in to Access.

 

If you want to send the info to me I could probably do it for you rather quickly. Just shoot me a PM.

Already tried & it's not feasible. I have almost 3000 lines of entries and it won't all just paste to Notepad and then back into Access and still keep all the information in it's individual cell. Notepad can find & replace the blocks, but it doesn't replace it with an Access recognized return character. So I'm still down to editing each individual cell using that only now I'm taking it a step further. When I put it into Wordpad it pastes it into cells and it automatically deletes the boxes, but then it doesn't replace them with anything so there is no space and no Access recognized return character, so once again I am back to editing each cell individually. It saves me one step in deleting the boxes, only now I don't have the boxes to tell me where the return was in the first place.