Comma Separated

[darrent]

I'm trying to create a file with all my clients phone numbers in a comma separated format e.g.

0123456789,0153647472,374646376237 etc

But when I try and save the data in Excel or Access as CSV with a comma as the delimiter it does not work. When I open it up in notepad it's just in one big neat column of numbers which is no good! Anybody any ideas? I need to be able to cut and paste into an application but when I do this it just copies the first number on row 1!!! Arggg!

[pdV6]

That's the way its supposed to work, because all your data is in one column rather than one row.

If you had multiple rows and columns, you'd have a line for each row with each column entry for the row on the line, comma separated.

e.g.

If your spreadsheet looks like:

1
2
3
4
5
6
7
8
9

then the csv will be:

1
2
3
4
5
6
7
8
9

If your spreadsheet was:

1 2 3
4 5 6
7 8 9

the csv would be:

1,2,3
4,5,6
7,8,9

And finally, if your spreadsheet was:

1 2 3 4 5 6 7 8 9

the csv would be:

1,2,3,4,5,6,7,8,9



>> Edited by pdV6 on Friday 18th November 10:13

[Munter]

As an aside I prefer to use tab delimited rather than comma as some countries use commas as their decimal point and all of a sudden your open to a few interesting problems that if your using tabs you can ignore.

[darrent]

Ah bugger - whats the easiest way to get all my data from;

01234455
33232232
23232333

to
01234455,33232232,23232333

???!?!?!?!

[pdV6]

A bit of VBScript?

[darrent]

pdV6 said:

A bit of VBScript?

Out of my depth with VB - anyone got a nice script I can nick

[Munter]

darrent said:

Ah bugger - whats the easiest way to get all my data from; 01234455 33232232 23232333 to 01234455,33232232,23232333 ???!?!?!?!

Take a copy.

Open it in Notepad.

Manually use the delete key to remove the line feeds.

Save.

Try and open in Excel and see if it's all on line 1.

[skittle]

In excel highlight the numbers select edit copy, find a new cell and select edit paste special and tick the transpose box

They will all fall into new cells which you then save as a csv file to give you what you want

[Jinx]

skittle said:

In excel highlight the numbers select edit copy, find a new cell and select edit paste special and tick the transpose box They will all fall into new cells which you then save as a csv file to give you what you want

Though you are in trouble if you have more than 256 numbers

[_dobbo_]

Download textpad, open the file, use "find and replace" and select "regular expression".

Replace \n
with , and you should find it works. To change back repeat in reverse.



edit cos the forum removed the character!


>> Edited by _dobbo_ on Friday 18th November 13:10