Batch text file replacer

[akki007]

Hi all,

I have a directory with 120 CSV files. I need to loop through each file and replace any instance of the string [null] with genuine null.

Is this possible using VBA via excel or access?

 

[JerryKlmns]

Yes!

Take a look at Scripting FileSystemObject to handle the looping in the folder and TextStream Object to open the file, read it all or line by line, use the Replace function and save it.
All issues are very well and many times referenced in Tek-Tips!

Do your reading, start your code genarator and post back for errors, problems or a "Can't figure it out" question!

 

[CautionMP]

akki007,
How big are the CSV files in terms of disk space?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[akki007]

100k for all!

 

[CautionMP]

akki007,
That's not too big so this should run pretty quick. One word of caution, test this on a COPY of your 120 files since it will destroy the originals.

I also opted not to use the [tt]FileSystemObject[/tt] (FSO) so I had to use the Kill, Copy, Kill trick to rename the files.

Typed and un-tested in Excel 2003, you will need to change the path in the first [tt]Dir[/tt] statement to match your files and double check the [tt]Repalace()[/tt] statement to see if that's what you were thinking when you said you wanted to replace [null] with a true null.

Sub BatchReplace()
Dim intFileIn As Integer, intFileOut As Integer
Dim strFileIn As String, strFileOut As String
Dim strLine As String

intFileIn = FreeFile
'Change the following line to match your actual
'directoty
strFileIn = Dir("C:\*.csv")

intFileOut = FreeFile

Do
  'Open the original file
  Open strFileIn For Input As #intFileIn
  'Create the name and open the output file
  strFileOut = strFileIn & ".tmp"
  Open strFileOut For Output As #intFileOut
  Do
    'Read a line from the input file
    Line Input #intFileIn, strLine
    'Do the replacement
    strLine = Replace(strLine, "[Null]", "")
    'Write the new line to the output file
    Print #intFileOut, strLine
  Loop Until EOF(intFileIn)
  'Close int input file and delete
  Close #intFileIn
  Kill strFileIn
  'Close the output file
  Close #intFileOut
  'Copy the new file to the old file name
  FileCopy strFileOut, strFileIn
  'Delete the temp file
  Kill strFileOut
  'Check to see if there is another file to convert
  strFileIn = Dir
Loop Until strFileIn = ""

End Sub

Please remember to Test this on a copy of your 120 files.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[akki007]

Thats excellent!

Although, it's falling over at...

strFileOut = strFileIn & ".tmp"

Breaking at the line after this command shows...

strFileOut =

 

[akki007]

Also, it doesn't like the open command.

 

[CautionMP]

akki007,
Oops, here are a couple of changes. But in a place holder for the path and moved the assignement of [tt]intFileOut[/tt] later in the code (the same file number was being issued by [tt]FreeFile()[/tt] because a file hadn't been opened yet.)

Sub BatchReplace()
Dim intFileIn As Integer, intFileOut As Integer
Dim strFileIn As String, strFileOut As String
[b]Dim strPath As String[/b]
Dim strLine As String

[b][green]'Change the following line to match your actual
'directoty[/green][/b]
[b]strPath = "C:\"[/b]

intFileIn = FreeFile

strFileIn = Dir([b]strPath & [/b]"*.csv")

[s][red]intFileOut = FreeFile[/red][/s]


Do
  'Open the original file
  Open [b]strPath &[/b] strFileIn For Input As #intFileIn
  'Create the name and open the output file
  strFileOut = strFileIn & ".tmp"
  [b]intFileOut = FreeFile[/b]
  Open [b]strPath &[/b] strFileOut For Output As #intFileOut
...

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[akki007]

Open strPath & strFileOut For Output As #intFileOut

Bad File name or Number

 

[akki007]

Ignore my last post, fixed it.

 

[akki007]

Thanks for the help guys, it's working like a charm!