RE: convert txt to xls

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

RE: convert txt to xls

Logged in as: Guest

arrSession:exec spGetSession 2,2,24209

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> RE: convert txt to xls
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: << < prev 1 [2]

Message

<< Older Topic Newer Topic >>

RE: convert txt to xls - 8/1/2005 9:27:56 PM

ehvbs

Posts: 2078
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi hahapat,

I had problems to construct a valid input file from your posting. Therefore the following remedy may fail.
If so, can you send me a sample file via email?

But first let's try this. Our strategy to get at the columns by RegExp "\S+" (sequence of non-spaces) fails
for column names like "Built-in account for administering the computer/domain". I'd have prefered to keep
all information about the input files from our magic, but that seems to be impossible. So edit doOurMagic():

Dim nIdx

Dim aFNamRpl
aFNamRpl = Array(   "Built-in account for anonymous access to Internet Information Services" _
                    , "Built-in_account_for_anonymous_access_to_Internet_Information_Services" _
                  )

If Right( sTail, 2 ) <> vbCrLf Then
     sTail = sTail + vbCrLf
End If

sLine2      = Left( sTail, InStr( sTail, vbCrLf ) - 1 )
For nIdx = 0 To UBound( aFNamRpl ) Step 2
      sLine2 = Replace( sLine2, aFNamRpl( nIdx ), aFNamRpl( nIdx +1 ) )
Next
Set oRE     = New RegExp

If I overlooked some not-so-nice column names, you'll have to add them to aFNamRpl.

I was afraid, that replace markers > "$9" would pose another problem. It was a nice surprise that VBScript
handled "$54" gracefully. But this feature could be version dependend. So be prepared for errors in line

sTail       = oRE.Replace( sTail, sRpl )

Did you solve the task of processing more than one file?

(in reply to hahapat)

Post #: 21

RE: convert txt to xls - 8/4/2005 2:40:36 PM

hahapat

Posts: 16
Score: 0
Joined: 7/27/2005
Status: offline

hi, ehvbs,

I have tried your fix, it overlooked those columns. but can't handle the column which is blank. let me send u the text file for u to take a look.

and for processing multi-files, haha... it's ok after I search the previous posts here. hehe.

(in reply to ehvbs)

Post #: 22

RE: convert txt to xls - 8/4/2005 8:32:19 PM

ehvbs

Posts: 2078
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi hahapat,

thanks for the files. In doOurMagic() change

sTail = oRE.Replace( sTail, sRpl )
oRE.Pattern = " +\t"
sTail = oRE.Replace( sTail, vbTab )

The old pattern was "\s+\t" which matched all whitespaces instead of spaces only. So something
like "<space><space><tab><space><space><tab>" (empty fields) were replaced by a single
<tab>.

My (rather dumb) mistake. Unfortunately didorno wasn't around to proof read my Regexps.

P.S. I just realized that "Built-in account for anonymous access to Internet Information Services" is
not a column haeder, so you may delete aFNamRpl and the corresponding code.

(in reply to hahapat)