Formatting a text file

[Mark]

I need to take a text file with a list of numbers that are formatted like:

80012345
91290999
89894566
48277770

and so on.... and turn it into a one long string with commas seperating each
number. there can be no line breaks and I must be able to copy and paste the
entire string once it is done. I though I could use access for this but
frankly I have not had any luck. Will I need to right VBA code ? Is there a
better MS program to use to solve my problem?

Mark

 

[Vsn]

If your data shown below is in a table structure you could use below code to
extract it to a file C:\datadump.txt

use the Sub TableDataDump('your tablename','fieldname with your data').


Public Sub TableDataDump(stgTableName As String, stgFieldName As String)
Dim rs As New ADODB.Recordset
Dim stgSQL As String

stgSQL = "SELECT " & stgFieldName & " FROM " & stgTableName & ";"
rs.Open stgSQL, CurrentProject.Connection

Open "C:\DataDump.txt" For Output As 1

Do While Not rs.EOF
Print #1, rs.Fields.Item(stgFieldName);
rs.MoveNext
If Not rs.EOF Then
Print #1, ", ";
End If

Loop

Print #1,
Close 1

rs.Close
Set rs = Nothing

Shell "notepad.exe c:\datadump.txt", vbNormalFocus

End Sub

Success,
Ludovic

 

[John Spencer]

You could do this in word if the size of the list was reasonable.
-- Open the text file in word
-- Do a find and replace to replace all paragraph marks with a comma.
---- Select Edit: Replace from the menu
---- Enter ^p (caret p)into the Find What box
---- Enter , (a comma) into the Replace what box
---- Click the Replace all button

Otherwise, you are going to need to use VBA as noted elsewhere.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Mark]

Thank you both for your answers, I will need to make the as user friendly as
possible so I will give them both a try and see which one works the best for
my situation.