CSV File to Excel

[jwestan]

I have a CSV file that looks like this.
FL340628SC,1,94,2,94,3,95,4,93,5,95
MA341282SC,1,83,2,94,3,93
NJ331947SC,1,95,2,183,3,94,4,94,5,95
NY345922SC,,,,,
FL340445SC,1,91,2,92,3,93,4,95,5,92
NJ336078SC,1,95,2,91,3,93,4,90,5,95,6,94
IL300716SC,,,,,
MA368952SC,2,95
MA356874SC,3,95,4,98

I am able to create the Excel spread sheet and headers but am having trouble trying to figure out how to be able to take the values, ie 1,95 2,94 and have them under a column corresponding to the first value (Node). Since not all of the values will be 1, 2, 3, 4 etc. some lines will contain 2, 94,3,94.

I want the Excel spreadsheet to look like:
Site Number | Node 1| Node 2| Node3 | Node 4| Node 5 | Node 6
FL340445SC| 91 | 92| 93 | 95| 92 |
MA368952SC| | 95| | | |
MA356874SC| | | 95| 98| |

This is what I have so far.

Any suggestions would be appreciated.

Code: Select all

	Const ForReading = 1
	Dim arrFields, strName, strNewContent,strLine, arrLine 
Dim args : Set args = WScript.Arguments
	If wscript.Arguments.Count = 0 Then
 WScript.Echo "Usage: PCIUpdatetoExcel.vbs FilePath, i.e. C:Tempfile.txt"
 WScript.Quit
End If 
	Dim strFilePath : strFilePath = args.Item(0)
Dim strFileName : strFileName = strFilePath 
	Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not objFSO.FileExists (strFileName)then
 WScript.Echo strFileName & " does not exist!!!"
 WScript.Quit
 Else
	Dim objExcel : Set objExcel = CreateObject("Excel.Application")
 objExcel.Visible = True
 objExcel.Caption = "Memory - Status Report"
 Set objWorkBook = objExcel.Workbooks.Add ()
 Set objWorksheet = objWorkbook.Worksheets(1)
 
 objExcel.Range("A1").Cells(1) = "Site Number"
 objExcel.Range("B1").Cells(1) = "Node 1"
 objExcel.Range("C1").Cells(1) = "Node 2"
 objExcel.Range("D1").Cells(1) = "Node 3"
 objExcel.Range("E1").Cells(1) = "Node 4"
 objExcel.Range("F1").Cells(1) = "Node 5"
 objExcel.Range("G1").Cells(1) = "Node 6"
Set objWorkSheet = objWorkBook.Worksheets("Sheet1")
 objWorkSheet.Name = "PCIUpdate - Status Report"
 
Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)
  i = 1
Do Until objFile.AtEndOfStream
 i = i + 1
 strLine = objFile.ReadLine
 arrLine = Split(strLine,",")
 objExcel.Cells(i, 1).Value = arrLine(0)
 If arrLine(2) = "" Then
  objExcel.Cells(i, 2).Value = ""
 Else
  objExcel.Cells(i, 2).Value = arrLine(2)
 End If 
 If arrLine(4) = "" Then
  objExcel.Cells(i, 3).Value = ""
 Else 
  objExcel.Cells(i, 3).Value = arrLine(4)
 End If 
 If arrLine(6) = "" Then
  objExcel.Cells(i, 4).Value = ""
 Else
  objExcel.Cells(i, 4).Value = arrLine(6)
 End If 
 If arrLine(8) = "" Then
  objExcel.Cells(i, 5).Value = ""
 Else 
  objExcel.Cells(i, 5).Value = arrLine(8)
 End If 
 If arrLine(10) = "" Then
  objExcel.Cells(i, 6).Value = ""
 Else 
  objExcel.Cells(i, 6).Value = arrLine(10)
 End If 
 If IsNull (arrLine(12)) Then
  objExcel.Cells(i, 7).Value = ""
 Else 
  objExcel.Cells(i, 7).Value = arrLine(12)
 End If 
Loop
 'objExcel.Cells(1,1).EntireRow.Font.Bold = True
 Set objRange3 = objWorkSheet.UsedRange 'This works even though the method does not show up
  objRange3.AutoFilter
  objRange3.EntireColumn.AutoFit
 
End If 
	

[jwestan]

I wanted to format the Excel spreadsheet so that MA356874SC,3,95,4,98 would go under Node 3 and Node 4 and not just list out the numbers.

[jvierra]

The code you posted does not do that. It just copies things from one place to another. To do it you have tyo load teh whole file into a two dimensional array and rotate the array.

[jwestan]

This was my final solution, there may be a more elegant way to do this but the values are placed in the correct column.

Code: Select all

	
Const ForReading = 1
	
Dim arrFields, strName, strNewContent,strLine, arrLine 
Dim args : Set args = WScript.Arguments
	
If wscript.Arguments.Count = 0 Then
 WScript.Echo "Usage: POS_Mem_to_Excel.vbs FilePath, i.e. C:Tempfile.txt"
 WScript.Quit
End If 
	
Dim strFilePath : strFilePath = args.Item(0)
Dim strFileName : strFileName = strFilePath 
	
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not objFSO.FileExists (strFileName)then
 WScript.Echo strFileName & " does not exist!!!"
 WScript.Quit
 Else
	
Dim objExcel : Set objExcel = CreateObject("Excel.Application")
 objExcel.Visible = True
 objExcel.Caption = "POS Memory T160 - Status Report"
Dim objWorkBook : Set objWorkBook = objExcel.Workbooks.Add ()
Dim objWorksheet : Set objWorksheet = objWorkbook.Worksheets(1)
 
 objExcel.Range("A1").Cells(1) = "Site Number"
 objExcel.Range("B1").Cells(1) = "Node 1"
 objExcel.Range("C1").Cells(1) = "Node 2"
 objExcel.Range("D1").Cells(1) = "Node 3"
 objExcel.Range("E1").Cells(1) = "Node 4"
 objExcel.Range("F1").Cells(1) = "Node 5"
 objExcel.Range("G1").Cells(1) = "Node 6"
Set objWorkSheet = objWorkBook.Worksheets("Sheet1")
 objWorkSheet.Name = "PCIUpdate - Status Report"
 
Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)
Dim i : i = 1
Dim nodeNum, nodeNum2, nodeNum3, nodeNum4, nodeNum5 
Do Until objFile.AtEndOfStream
 i = i + 1
 strLine = objFile.ReadLine
 arrLine = Split(strLine,",")
    objExcel.Cells(i, 1).Value = arrLine(0)
    If UBound(arrLine) > 1 Then 
     nodeNum = arrLine(1)
      If nodeNum  "" Then 
       objExcel.Cells(i, nodeNum + 1).Value = arrLine(2)
      End If 
    End If       
    If UBound(arrLine) > 3 Then 
     nodeNum2 = arrLine(3)
     If nodeNum2  "" Then 
      objExcel.Cells(i, nodeNum2 + 1).Value = arrLine(4)
     End If 
    End If 
    If UBound(arrLine) > 5 Then         
     nodeNum3 = arrLine(5)
     If nodeNum3  "" Then
      objExcel.Cells(i, nodeNum3 + 1).Value = arrLine(6)
     End If 
    End If 
    If UBound(arrLine) > 7 Then
     nodeNum4 = arrLine(7)
     If nodeNum4  "" Then
      objExcel.Cells(i, nodeNum4 + 1).Value = arrLine(8)
     End If 
    End If 
    If UBound(arrLine) > 9 Then 
     nodeNum5 = arrLine(9)
     If nodeNum5  "" Then
      objExcel.Cells(i, nodeNum5 + 1).Value = arrLine(10)
     End If 
    End If 
    If UBound(arrLine) > 11 Then objExcel.Cells(i, 7).Value = arrLine(12)
    
Loop
 'objExcel.Cells(1,1).EntireRow.Font.Bold = True
 Set objRange3 = objWorkSheet.UsedRange 'This works even though the method does not show up
  objRange3.AutoFilter
  objRange3.EntireColumn.AutoFit