Reformat a text file (and a date) 1

[cyberfreak95]

Hi,

I need to reformat a file called upload.txt that contains the following:

OR1387L3 Jan 20 2015 6:59PM
OR1363L3 Feb 4 2011 6:00PM

to this:

OR1387 01202015
OR1363 02042011

Does anyone have any suggestions on how to approach this?

Thanks in advance!

 

[SkipVought]

If this is a one time shot, I'd IMPORT into an Excel sheet, specifing FIXED WIDTH and the DATE in MDY and DO NOT IMPORT the TIME column.

Excel IMPORT manager will CONVERT to a real date.

FORMAT the date column as MMDDYYYY.

SaveAs a TXT, if so inclined.

Could be done in less than 3 min!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cyberfreak95]

It's a weekly file that gets generated with hundreds of lines. I wish it were that easy

 

[PWise]

Well you can record what skip said as a marco and run it as A Vb Script

 

[SkipVought]

Yes, could probably be set up in Excel and coded with little effort. Once you have the QueryTable for the IMPORT added to a sheet, it just needs to be refreshed. Pretty simple stuff.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cyberfreak95]

True, but I would have to install Excel on the server or have something map a share to it to run the macro. While this will work it's not my first choice because there's too many moving parts. If I can't figure out how to do a vbscrpit for this I may resort to a VBA script using excel - but I have tried this in the past and many "weird things" happen over time - so it's not the most reliable method.

 

[Geates]

- Open the input file
- Loop
- Read line
- Parse string
- Build new string contain with 8 digit date (MMDDYYY)
- Write to output file
- Rename output file to input file by copying
- Done

strInFile = "c:\temp\CoolBreeze\input.txt" 
strOutFile = "c:\temp\CoolBreeze\output.txt" 

set objFSO = CreateObject("Scripting.FileSystemObject")
set objInFile = objFSO.OpenTextFile(strInFile, 1)
set objOutFile = objFSO.OpenTextFile(strOutFile, 2, true)

do while NOT (objInFile.AtEndOfStream)
   strLine = objInFile.ReadLine
   strSN = left(strLine, inStr(strLine, " "))
   strDate = mid(strLine, inStr(strLine, " "))
   strMonth = right("00" & month(strDate), 2)
   strDay = right("00" & day(strDate), 2)
   strYear = year(strDate)
   objOutFile.WriteLine strSN & strMonth & strDay & strYear
loop

objInFile.Close
objOutFile.Close

objFSO.CopyFile strOutFile, strInFile, true

-Geates