.CSV to text

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

.CSV to text

Logged in as: Guest

arrSession:exec spGetSession 2,2,62552

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

Printable Version

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

Page: [1]

Message

<< Older Topic Newer Topic >>

.CSV to text - 7/21/2008 5:33:48 AM

foster.craig.d

Posts: 2
Score: 0
Joined: 7/18/2008
Status: offline

Hello,

I've been beating my head against a wall for quite sometime now. I need a script that will read in a .CSV file, and find where the first column value is zero. Once that point is established a text file
that contains the next 500 rows of the first column and the next column needs to be written. This will be repeated, for the remaining columns. That is column one and whichever column is the next, with a seperate file for each pair.

Problem is finding where this value of zero occurs, and identifying the cell location. I've tried many differnt snippets of code found here an there. At this point I'm thoroughly confused. I tried
to do this initially with AWK, it seems AWK doesn't behave as expected through the command prompt. So now I've resorted to VBScript, however this is relatively new and of course it doesn't seem
to behave as expected either.

I did try the macro recorder method as suggested on this forum. The latest bit of code snippet I've tried is coming from the Sam's 21 day book, and combined with a snippet out of
the Visual Basic reference.

Thank you in advance for any help that may be forthcoming.

The snippet that I am currently working with is as follows:

Dim appXL

Set appXL = WScript.CreateObject("Excel.Application")
'Set appWS = WScript.CreateObject("Excel.Worksheet")
'Set appRG = WScript.CreateObject("Excel.Range")
appXL.Visible = True

appXL.Workbooks.Open("C:\P1_2_4ms.CSV")

MsgBox(appXL.Windows(1).Caption)

MsgBox(appXL.Cells(1, 3).Value)

With ActiveSheet(1)appXL.Cells("a1:a500")
Set c = appXL.Find(0, lookin:=xlValues)
   If Not c Is Nothing Then
       firstAddress = c.Address
       Do
           c.Value = 5
           Set c = .FindNext(c)
       Loop While Not c Is Nothing And c.Address <> firstAddress
   End If
End With

If appXL.ActiveWorkbook.Saved = False Then
   MsgBox("Save me")
End If

Post #: 1

RE: .CSV to text - 7/24/2008 7:31:17 AM

mcds99

Posts: 434
Score: 4
Joined: 2/28/2006
Status: offline

Excel has specific needs when using it for data.

Take a look at this code to see what you can get from it.

iRow = 1
Set objExcel = CreateObject("Excel.Application")
strPathExcel = "pathtoExcelfile.xls"
objExcel.Workbooks.open strPathExcel3
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
  do while objSheet.Cells(iRow, 1).Value <> ""
     strCN1 = Trim(objSheet3.Cells(iRow, 1).Value) 'scopeAddress
     scopeAddress = strCN1
        Call ReadWriteExcel(scopeAddress)
        iRow = iRow + 1
  Loop 
Sub ReadWriteExcel(scopeAddress)
intRow = 1
   Set objExcel2 = CreateObject("Excel.Application")
      strPathExcel2 = "pathtoExcelfile.xls"
      objExcel2.Workbooks.open strPathExcel2
   Set objSheet2 = objExcel2.ActiveWorkbook.Worksheets(1)
   
     do while objSheet.Cells(intRow, 1).Value <> ""
        strCN1 = Trim(objSheet.Cells(intRow, 1).Value) 'dhcp
        strCN2 = Trim(objSheet.Cells(intRow, 2).Value) 'server
        strCN3 = Trim(objSheet.Cells(intRow, 3).Value) 'server IP
        strCN4 = Trim(objSheet.Cells(intRow, 4).Value) 'add or Scope
        strCN5 = Trim(objSheet.Cells(intRow, 5).Value) 'scope or subnet
        strCN6 = Trim(objSheet.Cells(intRow, 6).Value) 'subnet, set, or add
        strCN7 = Trim(objSheet.Cells(intRow, 7).Value) 'sn mask, state, iprange, optionvalue
        strCN8 = Trim(objSheet.Cells(intRow, 8).Value) 'comment, start range, 6, 51, 15, 3
        strCN9 = Trim(objSheet.Cells(intRow, 9).Value) 'end range, IPADDRESS, DWORD, STRING, 
        strCN10 = Trim(objSheet.Cells(intRow, 10).Value) 'routerIP, domain, numeric values, dns, wins, 
        strCN11 = Trim(objSheet.Cells(intRow, 11).Value) 'dns2, wins2
           scpaddr = strCN5
        If scpaddr = scopeaddress then 
           Set objExcel2 = CreateObject("Excel.Application")
           strPathExcel2 = "pathtoExcelfile.xls"
           objExcel2.Workbooks.open strPathExcel2
           Set objSheet2 = objExcel2.ActiveWorkbook.Worksheets(1)
               objSheet2.Cells(intRow, 1).Value = strCN1
               objSheet2.Cells(intRow, 2).Value = strCN2
               objSheet2.Cells(intRow, 3).Value = strCN3
               objSheet2.Cells(intRow, 4).Value = strCN4
               objSheet2.Cells(intRow, 5).Value = strCN5
               objSheet2.Cells(intRow, 6).Value = strCN6
               objSheet2.Cells(intRow, 7).Value = strCN7
               objSheet2.Cells(intRow, 8).Value = strCN8
               objSheet2.Cells(intRow, 9).Value = strCN9
               objSheet2.Cells(intRow, 10).Value = strCN10
               objSheet2.Cells(intRow, 11).Value = strCN11
             objExcel2.DisplayAlerts = False
             objExcel2.ActiveWorkbook.SaveAs strPathExcel2
             objExcel2.Workbooks.close
             objExcel2.Application.Quit
        End if
          intRow = intRow + 1
     loop
     objExcel.DisplayAlerts = False
     objExcel.Workbooks.close
     objExcel.Application.Quit
End Sub

_____________________________

Sam

Keep it Simple Make it Fun KiSMiF

(in reply to foster.craig.d)