[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to make a list coma delimited

Posted on 2009-04-17
18
Medium Priority
?
568 Views
Last Modified: 2013-11-15
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
0
Comment
Question by:overcolor
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 24169571
Copy and paste all of your data into an access table. Then Export the table to an text file using the Export Text Wizad and selecting the Delimiter option.

This will create a file like this:

"email1@email.com"
"email2@email.com"
"email3@email.com"

jppinto
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 24171167
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

0
 
LVL 44

Expert Comment

by:GRayL
ID: 24171958
Copy your list to Word, click Edit, Replace, in Find click Special, and select Manual Line Break. For With insert a comma (,) - and presto!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:overcolor
ID: 24172211
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
0
 
LVL 33

Expert Comment

by:jppinto
ID: 24172412
Did you've tryed my solution? It works...

jppinto
0
 

Author Comment

by:overcolor
ID: 24172454
yes it didnt give me a Delimiter option, im using 2007.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24172860
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!
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 24173517
Is there a reason your ignoring the script?
0
 

Author Comment

by:overcolor
ID: 24173652
no sorry Leew,
i havnt been able to set down long enough today to test it.can u tell me what a vbscript is?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24173696
This is something Word does so much better that a bunch of script or code in Access.
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 24173710
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
0
 

Author Comment

by:overcolor
ID: 24173713
i tried word for some reason it didnt work
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 24173716
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.
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 24173725
Oh really - the script - and screen shot - would seem to work fine.  ARe you familiar with scripting?
0
 

Author Comment

by:overcolor
ID: 24173734
leew i think what you are explaining may be a bit to advance for me, i havnt ever used vbscript
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 24174466
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).
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 24174469
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24176295
This little mole hill has become the proverbial mountain, and as such requires a lot more thought.  Beyond me!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question