Exporting data to excel via notepad

[CC801340]

I have an access query which I need to export to an excel spreadsheet. I am limited by access 255 character limit. I need 5 fields to combine into one excel cell (these fields combined are usually > 255 chars). The query therefore keeps the fields seperate but starts the 2nd, 3rd, 4th and 5th with **. I currently:

1) Export the query to a text CSV file
2) Load it in notepad and do a search/replace to replace ","** with nothing (the 5 fields are then enclosed in " " at each end.
3) Import the CSV into excel

There must be a quicker way!? Any ideas?!

 

[Craig0201]

You can concatenate the fields in a query and then export the query.

Craig

 

[CC801340]

Each field has a limit off 255 chars though so if I do that I lose data when I export straight to excel

 

[Craig0201]

Tried it....works fine....

Craig

 

[CC801340]

Would you be able to explain the structure of the query then?

Just say I have 5 fields "Descriptionx" where x is 1-5. These all need to be combined to go into one cell when exported to excel.

Many thanks

 

[tekola]

You need to use Excel Function CONCATENATE(A1,A2,A3,A4,A5) and place the new Description in a new cell. Or Use a Word macro on your original file to automate the replacement of the ",**". Word macros can be run with a command line, even in Access with a Macro like this "RunApp "c:\Microsoft Office\Office\Winword.exe /mmymacro". When you record the Macro "mymacro" in Word , Exit Word before stopping the recording.

 

[tekola]

OOPS! That path should include \Program Files\, but I tested and it works.