Opening a File Help

[camidon]

Okay, I've passed hurdle 1 with my file renaming problem. Now that I've renamed the file I would like to parse in the date. I'm getting a file not found on line 15.

Option Explicit
Const ForReading = 1, ForWriting = 2, ForAppending = 8

Dim FileObject
Dim SourceFolder
Dim FileCollection
Dim File
Dim Readfile

Set FileObject = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FileObject.GetFolder("C:\files\")
Set FileCollection = SourceFolder.Files

for each File in FileCollection
Set ReadFile = FileObject.OpenTextFile(File.Name, ForReading)
thisTxt = ReadFile.ReadAll
Readfile.close

thisTxt = Replace(thisTxt, vbCRLF, "")
thistxt = replace(thistxt, "~", "~" & vbCRLF)
finaltxt = thistxt & vbCRLF & thistxt
Next

set WriteFile = FileObject.CreateTextFile("c:\pmsp0000.txt", ForWriting, false)
WriteFile.Write(finaltxt)
WriteFile.Close

Msgbox "Done removing carriage returns and parsing data!"

Any ideas?

Thanks,

Chris

 

[PHV]

Try this:
Set ReadFile = File.OpenAsTextStream(ForReading)


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[camidon]

Okay, that got me further, but using this code, I'm now getting garbage in my pmsp0000.txt file:

Option Explicit
Const ForReading = 1, ForWriting = 2, ForAppending = 8

Dim FileObject
Dim SourceFolder
Dim FileCollection
Dim File
Dim Readfile
Dim ThisTxt
Dim FinalTxt
Dim WriteFile

Set FileObject = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FileObject.GetFolder("C:\files\")
Set FileCollection = SourceFolder.Files

for each File in FileCollection
Set ReadFile = File.OpenAsTextStream(ForReading)
ThisTxt = ReadFile.ReadAll
Readfile.close

ThisTxt = Replace(ThisTxt, vbCRLF, "")
ThisTxt = replace(ThisTxt, "~", "~" & vbCRLF)
FinalTxt = ThisTxt & vbCRLF & ThisTxt
Next

set WriteFile = FileObject.CreateTextFile("c:\pmsp0000.txt", ForWriting, false)
WriteFile.Write(FinalTxt)
WriteFile.Close

Msgbox "Done removing carriage returns and parsing data!

 

[PHV]

Which sort of garbage ? Somehow twice the initial data ?
FinalTxt = ThisTxt & vbCRLF & ThisTxt
Can you please post some input examples, garbage obtained and expected result ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[camidon]

Sure, the data SHOULD look like this:

NM1*87*2*KILDARE ASSOC*****24*581234567~
N3*2345 OCEAN BLVD~
N4*MIAMI*FL*33111~

What I'm getting looks like this:

t¢–Ó<¤‘å*ˆc «õúa&Yú¯©Vú!ÚᦫçKš^Á‡ÓâË^nÚ-ÈÆ6Û27õx2Ái
‹:‘w'¸ÀÇÆÑÖ$/¶iNbW¶‘ƒ.‚n4ŽùvwORàoõ$ÈMód3gCŠÂÜ|.'ý2µ¦JŠ~·•‘k\[7mçOÓ\¯j¢ày„ï6ÓR'ß=—)&—é‰) yÕáŠ71µã‹‚G¬
ŒNWyš¼ ƒYÛ¡¡Œ;ªLÒ¡é/umÏ7AºlæÕ—`£)

The original data is spanned across multiple files but looks like this:

NM1*87*2*KILDARE ASSOC*****24*581234567~N3*2345 OCEAN BLVD~N4*MIAMI*FL*33111~

This may not look like much of a big deal, but these files can be thousands of lines long.

Thanks for your help,

Chris

 

[PHV]

Try to replace this:
set WriteFile = FileObject.CreateTextFile("c:\pmsp0000.txt", ForWriting, false)
by this:
set WriteFile = FileObject.CreateTextFile("c:\pmsp0000.txt", false, True)
The syntax of this method is:
object.CreateTextFile(filename[, overwrite[, unicode]])
So I guess you're in trouble with Unicode.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[camidon]

I'm still getting the same garbage. I'm not sure what this could be now..

 

[PHV]

What is the format of the input file ? ASCII, Unicode, EBCDIC ?
Have you take a look at them the same manner as your output file ?
You can try to open the file as Unicode:
Set ReadFile = File.OpenAsTextStream(ForReading, -1)
or ASCII:
Set ReadFile = File.OpenAsTextStream(ForReading, 0)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tsuji]

Hello camidon,

Make a function to test unicode.

function IsInUnicode(textfilespec)

'return	0 ascii
'return	-1 unicode
'return	-2 File not exists

const ForReading=1, ForWriting=2
const TristateFalse=0, TristateTrue=-1, TristateUseDefault=-2

dim fso, oF, ts, x
set fso=createobject("scripting.filesystemobject")
if not fso.FileExists(textfilespec) then
	IsInUnicode = TristateUseDefault
	set fso=nothing
	exit function
else
	set oF=fso.getfile(textfilespec)
	set ts=oF.openastextstream(ForReading,TristateFalse)
	x=asc(ts.read(1))
	if x=&hff then
		IsInUnicode=TristateTrue
	else
		IsInUnicode=TristateFalse
	end if
	ts.close
end if
set ts=nothing
set fso=nothing

end function

Then, before reading the _text_ file, test on the format and design the reading scheme.

'...above refer to original
[COLOR=green]FinalTxt=""[/color]
for each File in FileCollection
    Set ReadFile = File.OpenAsTextStream(ForReading,[COLOR=green]IsInUnicode(File.path)[/color])
    ThisTxt = ReadFile.ReadAll
    Readfile.close

    ThisTxt = Replace(ThisTxt, vbCRLF, "")
    ThisTxt = replace(ThisTxt, "~", "~" & vbCRLF)
    FinalTxt = [COLOR=red]FinalTxt[/color] & vbCRLF & ThisTxt
Next
'...the rest refer to original

regards - tsuji