Replace values in Notepad file from CSV/Excel

G

GreenBox

New Member
Joined
Jan 3, 2014
Messages
7
Hi All,

I need your help to develop a Macro which will help me to eradicate a continuous task...

I have output in CSV as below

CSV FILE

Business NameAddress 1CityStateZipPhone
Tips & Toes Nail Salon9430 W 191stMokenaIllinois60448 (708) 478-7420
L A Nails11322 LincolnMokenaIllinois60423 (815) 464-9445
Asia Nails19610 La GrangeMokenaIllinois60448 (708) 478-8590
Nails Palace & Spa19834 La GrangeMokenaIllinois60448 (708) 995-5529
Kal Nails Spa10148 W 191st tMokenaIllinois60448 (708) 478-6285
Perfect Nails9500 W LincolnFrankforIllinois60423 (815) 806-8789
Jen Nails2063 E LarawayLenoxIllinois60451 (815) 462-1059
Classy Nails15784 S LaGrangeOrlandIllinois60462 (708) 226-1212
Best One Massage16902 Oak ParkTinleyIllinois60477 (708) 532-3103
Magnificent Nails19133 Wolf RdMokenaIllinois60448 (708) 478-7210

<tbody>
</tbody>

I need the above data to be replaced (TEXT File) in the below BLUE highlighted areas

TEXT FILE

Business Name Address 1 City, State Zip
< website details will be placed>
Business Name
Address 1
City, State Zip
Phone
< website details will be placed>
< website details will be placed>
Business Name
< website details will be placed>
City
< website details will be placed>

The final output should be creation of 10 TEXT (.txt) files as per the record count in CSV file in the above Text Format.

Please reply me the possibilities...

Thanks for all your help.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
<website details="" will="" be="" blessed=""><Website details will be placed>, does that mean leaving a line before the next item, 'cos am trying to understand the format of the text file. Maybe you could use record 1 for example</website>
 
Last edited:
Upvote 0
Hello,

Something along those lines?

! Careful: you must reference the "Microsoft Scripting Runtime" library in the VBA editor
! Careful: save your workbook as one accepting macros (*.xlsm)

> this code will output a text file for each CSV line in the folder where the workbook is stored.

*************************************************
Public Sub ParseContacts()
Dim objFSO As FileSystemObject
Dim objNewFile As TextStream
Dim i As Integer
Dim objSheet As Worksheet
i = 2 'don't parse header row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objSheet = ThisWorkbook.Worksheets(1)
While Trim$(objSheet.Cells(i, 1)) <> ""
Set objNewFile = objFSO.CreateTextFile(ActiveWorkbook.Path & "\" & Trim$(objSheet.Cells(i, 1)) & ".txt", True)
With objNewFile
.WriteLine Trim$(objSheet.Cells(i, 1)) & " " & Trim$(objSheet.Cells(i, 2)) & " " & Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteBlankLines 1
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine Trim$(objSheet.Cells(i, 2))
.WriteLine Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine Trim$(objSheet.Cells(i, 6))
.WriteBlankLines 2
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteBlankLines 1
.WriteLine Trim$(objSheet.Cells(i, 3))
.Close
End With
i = i + 1
Wend
End Sub
**********************************

Let me know if this works for you.

Cheers,
drW
 
Upvote 0
Hi Momentman,

Thank you for your interest in my query.

It is not about leaving a line. there may be more line in between the BLUE highlighted Text.

Regards,
GreenBox

Momentman said:
<website details="" will="" be="" blessed=""><website details="" will="" be="" placed="">, does that mean leaving a line before the next item, 'cos am trying to understand the format of the text file. Maybe you could use record 1 for example</website>
Click to expand...

</website>
 
Upvote 0
Hi drWatson

Thank you for your interest in my query.

The output was to replace the data in provided TEXT Template based on the keywords which are highlighted in BLUE which will like

Business Name Address 1 City, State Zip
< website details will be placed>
Business Name
Address 1
City, State Zip
Phone
< website details will be placed>
< website details will be placed>
Business Name
< website details will be placed>
City
< website details will be placed>

Please review and reply

Regards,
GreenBox


drWatson said:
Hello,

Something along those lines?

! Careful: you must reference the "Microsoft Scripting Runtime" library in the VBA editor
! Careful: save your workbook as one accepting macros (*.xlsm)

> this code will output a text file for each CSV line in the folder where the workbook is stored.

*************************************************
Public Sub ParseContacts()
Dim objFSO As FileSystemObject
Dim objNewFile As TextStream
Dim i As Integer
Dim objSheet As Worksheet
i = 2 'don't parse header row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objSheet = ThisWorkbook.Worksheets(1)
While Trim$(objSheet.Cells(i, 1)) <> ""
Set objNewFile = objFSO.CreateTextFile(ActiveWorkbook.Path & "\" & Trim$(objSheet.Cells(i, 1)) & ".txt", True)
With objNewFile
.WriteLine Trim$(objSheet.Cells(i, 1)) & " " & Trim$(objSheet.Cells(i, 2)) & " " & Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteBlankLines 1
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine Trim$(objSheet.Cells(i, 2))
.WriteLine Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine Trim$(objSheet.Cells(i, 6))
.WriteBlankLines 2
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteBlankLines 1
.WriteLine Trim$(objSheet.Cells(i, 3))
.Close
End With
i = i + 1
Wend
End Sub
**********************************

Let me know if this works for you.

Cheers,
drW
Click to expand...
 
Upvote 0
Hello,

Then simply replace the lines that said .writeblanklines with "< website details will be placed>", as follows:
Option Explicit

*******************************************
Public Sub ParseContacts()
Dim objFSO As FileSystemObject
Dim objNewFile As TextStream
Dim i As Integer
Dim objSheet As Worksheet
i = 2 'don't parse header row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objSheet = ThisWorkbook.Worksheets(1)
While Trim$(objSheet.Cells(i, 1)) <> ""
Set objNewFile = objFSO.CreateTextFile(ActiveWorkbook.Path & "\" & Trim$(objSheet.Cells(i, 1)) & ".txt", True)
With objNewFile
.WriteLine Trim$(objSheet.Cells(i, 1)) & " " & Trim$(objSheet.Cells(i, 2)) & " " & Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine Trim$(objSheet.Cells(i, 2))
.WriteLine Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine Trim$(objSheet.Cells(i, 6))
.WriteLine "< website details will be placed>"
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 3))
.WriteLine "< website details will be placed>"
.Close
End With
i = i + 1
Wend

set objfso = nothing
End Sub
**************

Cheers,
drW
 
Upvote 0
Hi drWatson,

Thanks for the code.

When I replace the "< website details will be placed>" with
<a href="http://website.com"><img class="size-full wp-image-123 alignleft" alt="Reviews" src="http://website.com/Reviews.png" width="200" height="168" /></a>

I am getting "End of Statement" error. How to overcome it.

Regards,
GreenBox

drWatson said:
Hello,

Then simply replace the lines that said .writeblanklines with "< website details will be placed>", as follows:
Option Explicit

*******************************************
Public Sub ParseContacts()
Dim objFSO As FileSystemObject
Dim objNewFile As TextStream
Dim i As Integer
Dim objSheet As Worksheet
i = 2 'don't parse header row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objSheet = ThisWorkbook.Worksheets(1)
While Trim$(objSheet.Cells(i, 1)) <> ""
Set objNewFile = objFSO.CreateTextFile(ActiveWorkbook.Path & "\" & Trim$(objSheet.Cells(i, 1)) & ".txt", True)
With objNewFile
.WriteLine Trim$(objSheet.Cells(i, 1)) & " " & Trim$(objSheet.Cells(i, 2)) & " " & Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine Trim$(objSheet.Cells(i, 2))
.WriteLine Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine Trim$(objSheet.Cells(i, 6))
.WriteLine "< website details will be placed>"
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 3))
.WriteLine "< website details will be placed>"
.Close
End With
i = i + 1
Wend

set objfso = nothing
End Sub
**************

Cheers,
drW
Click to expand...
 
Upvote 0
Hi drWatson,


Thanks for the code.


When I replace the "< website details will be placed>" with





I am getting "End of Statement" error. How to overcome it.


Regards,
GreenBox


GreenBox said:
Hi drWatson,

Thanks for the code.

When I replace the "< website details will be placed>" with


I am getting "End of Statement" error. How to overcome it.

Regards,
GreenBox
Click to expand...
 
Upvote 0
Hi drWatson,

The website link is not showing here.

So I sent you private message.

Please check and reply.

Regards,
GreenBox

GreenBox said:
Hi drWatson,


Thanks for the code.


When I replace the "< website details will be placed>" with





I am getting "End of Statement" error. How to overcome it.


Regards,
GreenBox
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
VBA macro to copy the data from opened text file and paste into excel
Replies
3
Views
10K
rlv01
R
J
  • Question
Export Email Data to specific fields in CSV file (or XLS file)
Replies
6
Views
984
John_w
J
A
  • Question
Need help rewriting a Macro for Importing Excel file instead of Text file into Access
Replies
0
Views
977
adibakale
A
J
  • Question
Exporting from Excel to Outlook
Replies
0
Views
562
John North
J
G
  • Question
macro for data range that varies on text file import
Replies
1
Views
1K
Ziggy12
Z

Forum statistics

Threads
1,216,077
Messages
6,128,685
Members
449,463
Latest member
Jojomen56

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top