any excel guru's around?

[Friar]

i am trying to get an extract from our main system (which runs under sql server) of our name and address file in to excel.

the field has a maximum size of 160 characters and it is being exported with embedded carriage returns at name, address1, address2 etc.

i need to be able to split these into seperate cells a1=name, b1=address1, c1=address2 etc

any one know how to do this or if its even possible ?

[B16Enk]

Pasting as text doesn't work?

Can you define the carriage return as the field delimiter?

Don't have excel installed at the moment, I'm loathe to install it on this incarnation of my boot!

An alternative can be to use something like Ultra Edit (which I have) and create a macro to strip out the crap and format it ready for excel.

But then Chris will be along in a minute and tell you just how easy it is to do this

[BBloke]

Excel loves comma seperated files where one line is a record and the data is put into corresponding columns where ever recognised character is found (normally a comma , ).

If your data from sql server is formatted like so:

Example 1

Name (new line)

add1 (new line)

add2 (new line)

add3 (new line)

country (new line) etc

Then copy and paste will put easch line into a new row in one column.

[Friar]

IF i was female i would be your bitch

the only thing is that is does not seem to work for multiple accounts, i suppose it cant tell where one finishes and the next begins, so understandable i suppose. guess i will just have to do them 1 by 1

[B16Enk]

Do they all end in a post code?

How many do you have to do?

This could be scripted, but far easier if you can get the SQL server to do it's bloody job and give you the data the way you want it.

After all that's what it's designed to do...

[Friar]

they should all have postcodes.

there are about 1400

the only way i know how to extract them is via a Crystal Report I have written, then export that to a .txt file and then import to excel. (although I can export straight to excel from the report)

[B16Enk]

Crystal Reports will run the SQL query you give it, with 1400 to do you would be well advised to structure the query right.

Again Chris is the SQL guru...

[B16Enk]

What version of Crystal Reports are you using?

[BBloke]

Mmmmmm.. need to have a conversation but its sounds as though you are running a report through an SQL Server Query that is giving you a mailing list and you want the mailing list (like sticky address labels) to go into excel as one record per row.

I have a database of contacts in MDB and to get the data out I would use an SQL command like so:

SELECT CompanyName, Address1, Address2, Address3, County, PostCode FROM tblCompany;

This would give me:

Company Name Address1 Address2 Address3 County Post Code

Valbruna U.K. Ltd c/o MYCCI Chamber St Thomas Road HUDDERSFIELD West Yorkshire HD1 3LG

ABS Brymar Floors Ltd Dane Road Dane Road Industrial Estate SALE Cheshire M33 7BH

Adams Kara Taylor Charter House 2 Farringdon Road LONDON EC1M 3HN

As a query result.

[BBloke]

If your report looks like this:

[attach=1]

Then you'll need to put your fields inline (left to right) and it should produce a file which is tabulated (MSAccess did it this way)

CR is far better than MSAccess but the design is similar.

This would be in line fields:

[attach=2]

as oppose to:

[attach=3]

[BBloke]

Had another thought and that would be to just do a database dump to txt file as though its an SQL script and clean out the header details

[BBloke]

Here's something that may help in Excel Colin:

=SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),""),CHAR(10),"
")

Exchange the 
to a single no description character and your address:

Name

Add1

Add2

County

would become:

Name
Add1
Add2
County

I have a function to grab a selected word so watch this space it may very well be possible within excel formula and something I have.

[BBloke]

Colin, give us a buzz.. may have it licked

[BBloke]

Well, anyway here's a formula that may help:

=CHAR(34)&A5&CHAR(34)&CHAR(44)&CHAR(34)&SUBSTITUTE(SUBSTITUTE(A6,CHAR(13),""),CHAR(10),CHAR(34)&CHAR(44)&CHAR(34))&CHAR(34)&CHAR(44)&CHAR(34)&A7&CHAR(34)

its pretty simple.

[BBloke]

Slit screw up but here is a shorter formula:

=CHAR(34)&A2&CHAR(34)&CHAR(44)&CHAR(34)&SUBSTITUTE(A3,CHAR(10),CHAR(34)&CHAR(44)&CHAR(34))&CHAR(34)&CHAR(44)&CHAR(34)&A4&CHAR(34)

I removed a substitute formula and changed the CHAR(13) to CHAR(10)

You ge tthe same result.

[BBloke]

Document can be viewed on google: http://spreadsheets.google.com/pub?key= ... fj9-vJPzIg

[B16Enk]

Good work Chris

[Rattler]

When you paste, try "Paste Special" instead of "Paste" from the EDIT menu.

[Friar]

wow, thanks guys.

i have had my head down all afternoon working on this and have only just just checked the forum.

i was pretty pleased with myself having established that the character i was after was char(13). having done that i was able to make progress using left amd mid functions to pull out what i need and think that i am nearly there.

many thanks chris for all your all work and giving me the direction.

salute

Colin.

[BBloke]

Colin,

If you are going to use MID then this may come in handy.

[Friar]

thats great chris, many, many thanks for helping out on this one.

Cheers

Colin.