read excel column to txtfile

Anything VBScript-related, including Windows Script Host, WMI, ADSI, and more.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 11 years and 7 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
Daan73
Posts: 9
Last visit: Tue Aug 21, 2012 11:01 pm

read excel column to txtfile

Post by Daan73 »

I have the following issueI read one column from an excel file with usernames without using excelif i echo it it displays all users in de filebut when i try to put them in a txt file or even better get the email address of every user in a csv file i get an error on accessing the temptxtfile Option ExplicitDim arrSheet, intCount, strlist, strLog, objRootDSE, strDomain, fso, list, result' Read and display columns A,B, rows 2..8 of "CMFS1_i-drive.xls"arrSheet = ReadExcel( "CMFS1_i-drive.xls", "Sheet1", "A7", "B46", False )For intCount = 0 To UBound( arrSheet, 2 )wscript.echo arrSheet( 0, intCount )result = arrSheet( 0, intCount )NextDim filesys, testfile, lineSet filesys = CreateObject("Scripting.FileSystemObject") Set strList= filesys.CreateTextFile("tmpfile.txt", True) strList.WriteLine result & vbcrlfstrList.Close 'reference to our filesstrLog = "userlog.csv"'constant which specifies the search scope of a query.Const ADS_SCOPE_SUBTREE = 2Set objRootDSE = GetObject("LDAP://RootDSE")'ADsPath to the current domainstrDomain = "LDAP://" & objRootDSE.Get("DefaultNamingContext")Set fso = CreateObject("scripting.filesystemobject")'open list fileSet list = fso.OpenTextFile(strList,1)'create log fileSet objlog = fso.CreateTextFile(strLog,True)'Create an ADO Connection object to access the Active Directory databaseSet objConnection = CreateObject("ADODB.Connection")Set objCommand = CreateObject("ADODB.Command")objConnection.Provider = "ADsDSOObject"objConnection.Open "Active Directory Provider"Set objCommand.ActiveConnection = objConnection'get the 1,000 items. continue until all the items have been returned.objCommand.Properties("Page Size") = 1000'a search will be conducted in a container and all its sub-containersobjCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREEDo Until list.AtEndOfStream'read line and split first name and last name to arraystrTemp = (list.ReadLine)on error resume next'query active directory with above valuesobjCommand.CommandText = _ "SELECT mail FROM '" & strDomain & "' WHERE objectCategory='user' " & _ "AND sAMAccountName='" & strTemp & "'"Set objRecordSet = objCommand.ExecuteobjRecordSet.MoveFirstDo Until objRecordSet.EOF 'write to log file the sAMAccountName we got objlog.WriteLine strTemp & ";" & objRecordSet.Fields("mail").Value objRecordSet.MoveNextLoopLoop'close stream to text filesobjlog.Closelist.CloseFunction ReadExcel( myXlsFile, mySheet, my1stCell, myLastCell, blnHeader )' Function : ReadExcel' Version : 3.00' This function reads data from an Excel sheet without using MS-Office'' Arguments:' myXlsFile [string] The path and file name of the Excel file' mySheet [string] The name of the worksheet used (e.g. "Sheet1")' my1stCell [string] The index of the first cell to be read (e.g. "A1")' myLastCell [string] The index of the last cell to be read (e.g. "D100")' blnHeader [boolean] True if the first row in the sheet is a header'' Returns:' The values read from the Excel sheet are returned in a two-dimensional' array; the first dimension holds the columns, the second dimension holds' the rows read from the Excel sheet.'' Written by Rob van der Woude' http://www.robvanderwoude.com Dim arrData( ), i, j Dim objExcel, objRS Dim strHeader, strRange Const adOpenForwardOnly = 0 Const adOpenKeyset = 1 Const adOpenDynamic = 2 Const adOpenStatic = 3 ' Define header parameter string for Excel object If blnHeader Then strHeader = "HDR=YES;" Else strHeader = "HDR=NO;" End If ' Open the object for the Excel file Set objExcel = CreateObject( "ADODB.Connection" ) ' With IMEX=1 numbers won't be ignored; tip by Thomas Willig. ' Connection string updated by Marcel Niënkemper to open Excel 2007 (.xslx) files. objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _ & myXlsFile & ";Extended Properties=""Excel 12.0;IMEX=1;" _ & strHeader & """" ' Open a recordset object for the sheet and range Set objRS = CreateObject( "ADODB.Recordset" ) Dim rsSchema, field, lineSet rsSchema = objExcel.OpenSchema(20)rsSchema.MoveFirstDo While Not rsSchema.EOFFor Each field In rsSchema.FieldsIf field <> "" Thenline = line & field &" "End ifNext'line = line & vbcrlfrsSchema.MoveNextLooprsSchema.CloseDim sheetsp, sheetsheetsp = split(line, " " )sheet = sheetsp(0)msgbox sheetmysheet = sheet strRange = mySheet & my1stCell & ":" & myLastCell objRS.Open "Select * from [ & strRange & ]", objExcel, adOpenStatic ' Read the data from the Excel sheet i = 0 Do Until objRS.EOF ' Stop reading when an empty row is encountered in the Excel sheet If IsNull( objRS.Fields(0).Value ) Or Trim( objRS.Fields(0).Value ) = "" Then Exit Do ' Add a new row to the output array ReDim Preserve arrData( objRS.Fields.Count - 1, i ) ' Copy the Excel sheet's row values to the array "row" ' IsNull test credits: Adriaan Westra For j = 0 To objRS.Fields.Count - 1 If IsNull( objRS.Fields(j).Value ) Then arrData( j, i ) = "" Else arrData( j, i ) = Trim( objRS.Fields(j).Value ) End If Next ' Move to the next row objRS.MoveNext ' Increment the array "row" number i = i + 1 Loop ' Close the file and release the objects objRS.Close objExcel.Close Set objRS = Nothing Set objExcel = Nothing ' Return the results ReadExcel = arrDataEnd Functionthx in advance for helping me regardsdaan
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

read excel column to txtfile

Post by jvierra »

In VBScript it is not possible to write an array to a file in one operation. You can enumerate the array and write teh individual members to the file.


You are echoing the following line:
wscript.echo arrSheet( 0, intCount )

You are writing a different variable.
strList.WriteLine result & vbcrlf
Note that the vbCrLf is unnecesary as WriteLine adds that on its own.

The value you are writing is teh very last value in the array. I suspect that the last value in the array is blank.

Which value are you trying to write?

To write the last value just access it

WScript.Echo arrSheet( 0, Ubound(arrSheet,2))

This will produce the identical results as you loop does. It will echo the last value in the array. YOu are no echoing all values and probably can't see that the last one is blank.

You could also change your trace statement:wscript.echo "At location " $ intCount & " we have value - " & arrSheet( 0, intCount )



jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

read excel column to txtfile

Post by jvierra »

Exactly the way you are doing it to write to the screen.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

read excel column to txtfile

Post by jvierra »

Please use this trace statement:
wscript.echo "At location " $ intCount & " we have value - " & arrSheet( 0, intCount )

jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

read excel column to txtfile

Post by jvierra »

Yes - that is exactly how you wrote your code. OU are listing the value of the array leemts and saving them, one at a time, int o'result' You then write 'result' to a file. Is this not what you want to do.

It would be much easier to jjsut write the last element to the file directly.

The follwing gets thae lst item in the array and write it to a file. Ther is no need to loist the whole array to get the last item. Just get it directly.

Code: Select all

	
arrSheet = ReadExcel( "CMFS1_i-drive.xls", "Sheet1", "A7", "B46", False )
' echo last item
wscript.echo arrSheet( 0, UBound( arrSheet, 2 ) )
' write last item to a file
strList.WriteLine result & vbcrlf
	
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

read excel column to txtfile

Post by jvierra »

How come you can't just write them to the file the same way you are writing them to the console?

Code: Select all

	
For intCount = 0 To UBound( arrSheet, 2 )
     wscript.echo arrSheet( 0, intCount )
     file.writeline  arrSheet( 0, intCount )
Next
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

read excel column to txtfile

Post by jvierra »

Why are you closing the file?
User avatar
Daan73
Posts: 9
Last visit: Tue Aug 21, 2012 11:01 pm

read excel column to txtfile

Post by Daan73 »

when i dont' i get an error on line11 while its echoing all the entry's:Set strList= filesys.CreateTextFile("tmpfile.txt", True) permission denied
User avatar
Daan73
Posts: 9
Last visit: Tue Aug 21, 2012 11:01 pm

read excel column to txtfile

Post by Daan73 »

whats the best way to use then ?
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

read excel column to txtfile

Post by jvierra »

1. open your file.
2. Output all of you text to the file
3. close the file.

I think you should start by getting a book or a training video for VBScript.


THisite has books and videos,

You can also use Internet resources.
http://technet.microsoft.com/en-us/libr ... 98742.aspx

This topic is 11 years and 7 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked