Find and Replace in csv Files

[vjay883]

Hi All,

I have had no luck in finding code that does what I need so have turned to you GURU's for help! :bow:
I have managed to open a notepad session and find the file I need using VBA but what I need to do now is perform a find and replace specifically:
I need excel VBA code to perform:

1.Open Find and Replace
2.Replace all Commas with Pipes (|)
3.Find the word 'EN' on the first line of text file
4.Delete all pipes to the right of the word 'EN' (But only on the first line)
5.Save the file in a directory
6.Save the file name as the current opened work book name
7.Save the file in the directory choosen but save as a UTF-8 format.
8.Close Notepad

If anyone would like a challenge and can help then please respond to this as it would be much appreciated.

Many Thanks
V

[pasta_lover]

Hi I need some clarification in terms of what you're trying to do.

So far, I understand that you need to:
1. Open notepad from Excel
2. Find and open another Excel file
3. Find and replace in the newly opened Excel file
4. Do the list of stuff (1-8) that you posted

Is this correct?

[vjay883]

Hi I need some clarification in terms of what you're trying to do.

So far, I understand that you need to:
1. Open notepad from Excel
2. Find and open another Excel file
3. Find and replace in the newly opened Excel file
4. Do the list of stuff (1-8) that you posted

Is this correct?

Hi,
(Excel 2007)
OK - so far I have code for the following steps:
-Search a directory for the latest created excel file. (File currently has a name like 'test' but this can be any name and will change)

-Open the file and convert file as a csv (comma demilited) within the same directory with the same file name 'test'

-Then I have got vba to open that csv in a notepad sessIon.

Now I need to do the following:

-Within notepad find and replace all commas (,) with a pipes (|)

-Find the word 'EN' on the first line and remove all pipes (|) to the right of the word and ONLY on the first line which 'EN' is always used.

-Once that's complete I need to save the file as a UTF-8 csv file within the same location or different one doesn't matter.

I hope this is clear enough? Any more questions please ask.

Much appreciated
V

[westconn1]

do you really need to open and edit the file in notepad?
or just edit the file and save, no user interaction?
notepad is not easy to automate, the latter option is simple like

' open file to read
f = freefile
open "myfullpath\filename.csv" for input as f
strfile = input(lof(f), #f)
close f
'edit file content
strfile = replace(strfile, ",", "|")
strlines = split(strfile, vbnewline)
pos = instr(strlines(0), "EN")
if pos > 0 then
strlines(0) = replace(strlines(0), "|", "", pos + 1)
strfile = join(strlines, vvbnewline)
end if
' save file
f = freefile
open "newpath\filename.txt" for output as f
print #f, strfile
close fthis is untested, so may contain errors
this does not convert file to utf-8, but can be done within this code, i will try to look it up later

[vjay883]

do you really need to open and edit the file in notepad?
or just edit the file and save, no user interaction?
notepad is not easy to automate, the latter option is simple like

' open file to read
f = freefile
open "myfullpath\filename.csv" for input as f
strfile = input(lof(f), #f)
close f
'edit file content
strfile = replace(strfile, ",", "|")
strlines = split(strfile, vbnewline)
pos = instr(strlines(0), "EN")
if pos > 0 then
strlines(0) = replace(strlines(0), "|", "", pos + 1)
strfile = join(strlines, vvbnewline)
end if
' save file
f = freefile
open "newpath\filename.txt" for output as f
print #f, strfile
close fthis is untested, so may contain errors
this does not convert file to utf-8, but can be done within this code, i will try to look it up later


Hi,

Thanks for the reply but I think it isn't that simple because when you open the csv in excel you don't see the commas. But if you open in notepad you get the commas which I need to replace with pipes and ultimately all columns get concat to column A separated by the pipes... On top of that I don't know if you can save as the utf-8 format.

But on the plus side your reply has made me think of just doing a standard concat and then try save as utf 8. I'll see if it works and update.. But if anyone knows how to do this please post.

Love to know and see the different ways of doing things and the way people think.

Cheers
V

[SamT]

Uh, he is not opening the csv file in Excel, he is opening and editing it memory. I can't comment on any errors in his code.

[vjay883]

Uh, he is not opening the csv file in Excel, he is opening and editing it memory. I can't comment on any errors in his code.

Thanks for schooling me SamT I will try westcons code.
Cheers
V

[SamT]

:thumb

[westconn1]

to save as utf-8, change as below


If pos > 0 Then
strlines(0) = replace(strlines(0), "|", "", pos + 1)
strlines(0)= chr(239) & chr(187) & chr(191) & strlines(0)
strfile = join(strlines, vbnewline)
End If

again untested, so may contain errors
vvbnewline was a typo in first code

note excel treats .csv files differently from any other text files and will only open .csv files correctly with comma delimiters

[vjay883]

I have tried to add this code to my macro and I get the errors stating that the below isn't defined.

error on f = freefile
error on strfile
error on strlines

I added the below to my code which unsure if correct but does go past the first two (f = freefile,strfile):

Dim f As String
Dim strfile As String
Dim strlines As Integer

But when running the macro again I get the error on strlines:

Compile Error: Expected Array

Now I don't know if declaring like I have is correct and need your input again please?

Cheers
V

[Tinbendr]

Dim FF as Integer
Dim StrFile as Variant
Dim StrLines as Variant

[vjay883]

I have added the Variables into the code and get an error on 'pos' which I think needed to be declared also.

I added Dim pos as Variant

I ran the code and it does what I need but it deletes the first row column headers and just leaves the letter 'N' in the first column (There are 4 column headers and EN is the last one , column headers can be variable and will not always be 5 headers could be 10 or 20 etc.

Also I changed the variable type for 'pos' a few times and it also worked but want to know which is the correct type to use. (Worked with Integer and string)

Appreciated as always.

Cheers
V

[westconn1]

but want to know which is the correct type to use.just about any variable can be a variant, though it is not always the best choice, as it uses more memory and maybe slower than using a specific variable type
variant can contain any other variable
pos should be an integer or a long (long can be bigger value than integer)
strlines would be a string array
dim srtrlines() as string (can also be contained within a variant)


it deletes the first row column headers and just leaves the letter 'N' in the first columnmy error, the way i used replace, to replace only the pipes after EN
try changing to

If pos > 0 Then
strlines(0) = left(strlines(0), pos - 1) & replace(strlines(0), "|", "", pos)
strlines(0)= chr(239) & chr(187) & chr(191) & strlines(0)
strfile = join(strlines, vbnewline)
End If

[vjay883]

This is now working perfectly.. Thank you for your continued support. You guys are legends.

Cheers
V