Link to home
Start Free TrialLog in
Avatar of D F
D FFlag for United States of America

asked on

How to make a list coma delimited

I have a list of emails in note pad and i want to make it coma delimited?
My list looks like this
email1@email.com
email2@email.com
email3@email.com
ASKER CERTIFIED SOLUTION
Avatar of jppinto
jppinto
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lee W, MVP
There are a number of ways this could be done - Access is NOT one I'd recommend, though that may be in part because I've not used it in a while.

A relatively simple vbscript should work.  Change the paths in GetFile and OpenTextFile (2nd and 3rd lines below) so that they properly reflect the path of your source data and where you want to save the new file.

This script can also be modified to accommodate multiple addresses on one line (assuming they are separated by a delimiter of some kind).

Save the script to a .vbs file - then double-click or type the file name on the command line.  I used a file name of "makecsv.vbs" but you can use "whatever.vbs"
Option Explicit
 
'Declare the File name variables
Dim ReadFromFile, SaveToFile
 
'Declare the file system object variables
Dim objFSO, objReadFile, ObjCreateFile, objWriteFile
 
'Declare the source data variable
Dim SourceData
 
'Set the name and path of the file we are reading from
ReadFromFile="C:\temp\scripts\makecsv.txt"
 
'Set the name and path to the file we are writing to (Creating)
SaveToFile="C:\temp\scripts\makecsv.csv"
 
'Create and/or open the files to work with
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objReadFile = objFSO.OpenTextFile(ReadFromFile)
Set objCreateFile = objFSO.CreateTextFile(SaveToFile)
ObjCreateFile.Close
Set objWriteFile = objFSO.OpenTextFile(SaveToFile, 2, True)
 
'Read every line of the file and append a comma to the end of every line.  
Do Until objReadFile.AtEndOfStream
 
	'Put the current line into the SourceData variable and remove any leading and trailing spaces
	SourceData = Trim(objReadFile.ReadLine)
 
	'Check if we have any more lines after this one and write the data with or without the comma
	If ObjReadFile.AtEndOfStream = False Then 
		'If we haven't reached the last line yet, make sure to include the comma
		objWriteFile.WriteLine (SourceData & ",")
	Else
		'If we are at the last line, make sure to JUST list the name, no comma
		objWriteFile.WriteLine (SourceData)
	End If	
Loop
 
'Close the Files
objReadFile.Close
objWriteFile.Close
Set objFSO = Nothing

Open in new window

Copy your list to Word, click Edit, Replace, in Find click Special, and select Manual Line Break. For With insert a comma (,) - and presto!
Avatar of D F

ASKER

admin@bla.com
info@gl.com
feedback@bea.com
snclr08@g.com
in word it looks like this
 word 2007
I tried Manual Line Break it said nothing found
Did you've tryed my solution? It works...

jppinto
Avatar of D F

ASKER

yes it didnt give me a Delimiter option, im using 2007.
It worked for me!  Maybe try:

Copy your list to Word, click Edit, Replace, More, in Find click Special, and select Manual Line Break. For With insert a comma (,) - and presto!
Is there a reason your ignoring the script?
Avatar of D F

ASKER

no sorry Leew,
i havnt been able to set down long enough today to test it.can u tell me what a vbscript is?
This is something Word does so much better that a bunch of script or code in Access.
vb - Visual Basic.  Windows Supports scripts (various commands combined to complete a task).  There are several types of scripts - Batch files are scripts.  PowerShell Scripts, VBScripts, and others.

As I stated earlier, save the script to a .vbs file - then double-click or type the file name on the command line.  I used a file name of "makecsv.vbs" but you can use "whatever.vbs"

Don't forget to change the paths for ReadFromFile and SaveToFile as noted below - I used the path "c:\temp\scripts" with the file names "makecsv.txt" and "makecsv.csv"
ReadFromFile="C:\temp\scripts\makecsv.txt"
SaveToFile="C:\temp\scripts\makecsv.csv"

NOTE: the lines in the script that start with a ' are comments - they could be deleted and NO EFFECT on the script functionality would occur.

To show you an example, see the picture/series of pictures below (note, in the picture below, I have removed all comments and blank lines so that it could fit in one graphic):


EE24332342.JPG
Avatar of D F

ASKER

i tried word for some reason it didnt work
Note: in the above screen shot, I first "type" (which displays the contents) of the makecsv.txt file - which is a list of e-mail addresses, one per line.
Then I type the script - so you can see, with the exception of the file path, comments, and blank lines, it's exactly what I posted.
Then I run the script by simply typing the script name - "makecsv.vbs"
Then I list the files in the directory - you can see a new file, makecsv.csv
Finally, I type the makecsv.csv file and you can see that it is the same as the original makecsv.txt file, only it has a comma at the end of every line, EXCEPT the last line.
Oh really - the script - and screen shot - would seem to work fine.  ARe you familiar with scripting?
Avatar of D F

ASKER

leew i think what you are explaining may be a bit to advance for me, i havnt ever used vbscript
So?  What's difficult? cut and paste the lines of code above into a file named "makecvs.vbs" - then look at the image I posted.  (Click it to open it FULL SIZE).
It sounds like you're over-thinking this.  Read what I wrote and follow the directions - YOU don't need to program - just enter the correct file locations and your done.
This little mole hill has become the proverbial mountain, and as such requires a lot more thought.  Beyond me!