Trying to convert a text in csv to a certain format of date

[dashen]

I need to make a text on each line to be MM/DD/YY where the leading zeros are kept

Import String

function getfile(byval filename)
dim fs: set fs = createobject("Scripting.FileSystemObject")
If InStr(FileName, ":\") = 0 And Left (FileName,2)<>"\\" Then 
    fileName = FS.GetSpecialFolder(0) & "\" & FileName
End If
getfile = fs.opentextfile(filename).readall
End function


Dim inp
inp = getfile("C:\test.csv")
for each line in inp
	line = Format(cDate(line),"MM/DD/yy")
next
inp.close()

This is what I have so far, but it doesn't even run right now. Any help?

 

[PScottC]

Below are some pointers as to why the code doesn't work.

To help you on formatting the date, we will need a sample of the data that you are reading from the csv file.

Function getfile(ByVal filename)
	Dim fs

	Set fs = CreateObject("Scripting.FileSystemObject")

[green]' Check if full path is specified, otherwise set path to windows folder[/green]
	If InStr(filename, ":\") = 0 And Left(filename,2) <> "\\" Then
		filename = fs.GetSpecialFolder(0) & "\" & filename
	End If

	[s]getfile = fs.opentextfile(filename).readall[/s][red] <== Not sure if this line is legal. Suggest opening TextStream object, shown below[/red]
	Dim oTS
[green]' Instantiate TextStream object to open the text file[/green]
	Set oTS = fs.OpenTextFile(filename, 1)
[green]' Read entire file[/green]
	getfile = oTS.ReadAll
[green]' oTS closes when function ends[/green]
End Function


Dim inp
inp = getfile("C:\test.csv")

[s]For each line in inp[/s][red] <== Illegal. 'inp' is not a collection or array[/red]
    [s]line = Format(cDate(line),"MM/DD/yy")[/s]
[s]Next[/s]
[s]inp.close()[/s][red] <== Illegal. 'inp' is not a TextStream object[/red]

Dim aLines, ctr
[green]' Split the lines of the text file into an Array
' using CRLF as the delimeter[/green]
aLines = Split(inp, VbCrLf)

[green]' Loop through array and do something to it.[/green]
For ctr = 0 To UBound(aLines)
	[green]' Do something to array element[/green]
Next

PSC

Governments and corporations need people like you and me. We are samurai. The keyboard cowboys. And all those other people out there who have no idea what's going on are the cattle. Mooo! --Mr. The Plague, from the movie "Hackers

 

[PHV]

And for the formatting issue:

If IsDate(line) Then
  line = Right("0" & Month(line),2) & "/" & Right("0" & Day(line),2) & "/" & Right(Year(line),2)
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dashen]

function getfile(byval filename)
    dim fs: set fs = createobject("Scripting.FileSystemObject")
    If InStr(FileName, ":\") = 0 And Left (FileName,2)<>"\\" Then 
      fileName = FS.GetSpecialFolder(0) & "\" & FileName
    End If
    Dim oTS
    Instantiate TextStream object to open the text file
    Set oTS = fs.OpenTextFile(filename, 1)
    getfile = oTs.ReadAll
End function


Dim inp
inp = getfile("C:\test.csv")
Dim aLines, ctr
aLines = Split(inp, VbCrLf) 'You may have to split by comma as well
For ctr = 0 To UBound(aLines)
    If IsDate(aLines(ctr)) Then
      'write back to line using this format
      ' Right("0" & Month(aLines(ctr), 2) & "/" Right("0" & Day(aLines(ctr), 2) 

& _
      ' "/" & Right("Year(aLines(ctr), 2)
    End If
next

So is this close to what you were saying? Thanks a whole bunch btw.