Text File converter 1

[SQLScholar]

Hey all,

I am trying to take a text file and convert it into a better format so i can upload it in SQL. Currently the file is tab delimited but then columns 2,3 and 4 have comma separate information in. I want this comma separated stuff one per line. If it helps the data is coming from this app.

http://technet.microsoft.com/en-us/sysinternals/bb897332.aspx

So the data looks like the attached file.

here is my script:

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\SourceSafe\AccessEnums Cleaner\AccessEnum.txt", ForReading)

Set objFile2 = objFSO.CreateTextFile("C:\SourceSafe\AccessEnums Cleaner\AccessEnumClean.txt")

objFile2.WriteLine "Location,AccessType,User"
objFile.ReadLine
Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    strLine = replace(strLine,chr(34),"")

    arrFields = Split(strLine,vbtab)
    arrRead = split(arrFields(1),",")
    arrWrite = split(arrFields(2),",")
    arrDeny = split(arrFields(3),",")

	for each Strperm in arrRead
		objFile2.WriteLine arrFields(0) + "," + "Read" + "," + Strperm
	next

	for each Strperm in arrWrite	
		objFile2.WriteLine arrFields(0) + "," + "Write" + "," + Strperm
	next
	
	for each Strperm in arrDeny
		objFile2.WriteLine arrFields(0) + "," + "Deny" + "," + Strperm
	next
Loop


objFile.Close
objFile2.Close

and it currently errors on line 17 with a "subscript out of range [number: 3]"

However it does provide the first few results and i get:

Location,AccessType,User
D : ,Read, E v e r y o n e
D : ,Write, A d m i n i s t r a t o r s
D : ,Write, U s e r s
D : ,Deny,
D : \ 8 a 5 0 a 7 4 b 0 e d f a 5 b e 9 4 4 e 0 5 ,Read, A d m i n i s t r a t o r s
D : \ 8 a 5 0 a 7 4 b 0 e d f a 5 b e 9 4 4 e 0 5 ,Write, A d m i n i s t r a t o r s
D : \ 8 a 5 0 a 7 4 b 0 e d f a 5 b e 9 4 4 e 0 5 ,Deny,

Where have all the spaces come from???? Its probably really obvious but i cant seem to see it!

Any help greatfully recieved.

Dan




----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[tsuji]

The file to be read is in unicode encoding. Hence, it must be treated accordingly during reading in.
>Set objFile = objFSO.OpenTextFile("C:\SourceSafe\AccessEnums Cleaner\AccessEnum.txt", ForReading)
[tt]Set objFile = objFSO.OpenTextFile("C:\SourceSafe\AccessEnums Cleaner\AccessEnum.txt", ForReading[red],false,-1[/red])[/tt]

The false is used above meaning if the file does not exist, it won't be created. This is chosen here taken into account of special format understood in the processing (such as neglecting the first line etc...) and creating a file (true) would just postpone the runtime error.

The setting of 4th parameter is the critical one. Without it, it is taken as 0 (ascii), and in that case, a misalignment of data treated as character would result in all the following errors and wrong encoding in the output file.

 

[SQLScholar]

Thanks, that really helped.

Stupid question - how can you tell what encoding a file is?

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[tsuji]

>how can you tell what encoding a file is?
That is not a question that vbs carries a stable solution, in particular, major lower level support is off-limit. One way to do it is to use ascii setting and read the first couple of bytes checking out the bom. Something like this.
[tt] s=objFile.read(3) '3 bytes say[/tt]
But, not all files created with proper bom.

Besides, determination of the exact encoding is not an exact science even with all the low level api available. Sometimes, one just has to resort to statistical estimation of the most probable encoding in those sectors of computing. That takes the matter too afar.