Batch Replacer Having Funny Effect

[akki007]

I have a script that loops around a directory. It opens each .CSV file in turn, replaces the string "[null]" with "". It then saves the file as a new version and deletes the original.

The script works like a charm, except... When I come to load the new files into a table via DTS, it does not like them! I guess my question is... What does the following script do to the files that may change it's structure or format?

...

Dim intFileIn As Integer, intFileOut As Integer
Dim strFileIn As String, strFileOut As String
Dim strPath As String
Dim strLine As String
Dim strInput As String

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

Do
'Open the original file
Open strPath & strFileIn For Input As #intFileIn
'Create the name and open the output file
strFileOut = strPath & strFileIn & ".tmp"
intFileOut = FreeFile
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 strPath & strFileIn
'Close the output file
Close #intFileOut
'Copy the new file to the old file name
FileCopy strFileOut, strPath & strFileIn
'Delete the temp file
Kill strFileOut
'Check to see if there is another file to convert
strFileIn = Dir
Loop Until strFileIn = ""

 

[rmikesmith]

I'm not familiar with DTS but perhaps it does not like a null string between commas. Try removing the comma associated with "[Null]" and see if that makes a difference.


Regards,
Mike

 

[JerryKlmns]

Does your .csv surround everything with double quotes?
Maybe [Null] looks like in the file ,"[Null]", and should be ,"",

 

[akki007]

353,2001,"1757,0",M353204500097,Joe,Bloggs,,19/02/1997,F,17,"Made Up St",,Oldham,"OL9 6DT",,,,05/06/2006,[null],F,,,F,[null],,,"0161 287 5844"

This is an example row in a .csv. The fields that are double quoted are ones that could potentially have a comma within them. The [null] is not enclosed in "".

 

[JerryKlmns]

A trick

strLine = Replace(strLine, ",[null],", ",,")

 

[akki007]

Ah! Thanks people!