在一个单元格中处理多个值以导出为CSV [英] Handling Multiple Values in One Cell to Export To CSV
问题描述
我正在尝试设计一个VBA脚本，该脚本会将值从Excel电子表格导出到CSV文件.这将用于通过单独的Powershell脚本部署多个虚拟机.
I am trying to design a VBA script that will export values from an Excel spreadsheet to a CSV file. This will be used for deployment of multiple Virtual Machines by a separate Powershell script.
当前，导出到CSV"操作正在每个单元格处理一个虚拟机名称以及相应的设置，但是，在脚本的实际执行中，我们可能在单个单元格中有多个虚拟机名称(以逗号分隔)，但是将具有程序单元格中的设置相同.
Currently the Export to CSV operation is handling one single VM name per cell and the corresponding settings however, in the actual execution of the script we may have multiple VM names in a single cell (separated by a comma) but will have the same settings in the proceeding cells.
我想知道的是，是否有可能/如何在该单元格中将那些用逗号分隔的值填充到单独的行中，但仍在CSV文件中的适当标题下进行设置.
What I would like to know is whether it is possible/how to go about having those values in that cell that are separated by a comma populate a separate line but still have the settings under the appropriate headings within the CSV file.
代码:
Function BuildValuesString(colIndex As String, rows As String) As String
Dim val As Variant
For Each val In Split(rows, ",")
If Cells(val, colIndex) <> "" Then BuildValuesString = BuildValuesString & Cells(val, colIndex).Value & ","
Next val
End Function
Function BuildNullStrings(numNullStrings As Long) As String
Dim iNullStrings As Long
For iNullStrings = 1 To numNullStrings
BuildNullStrings = BuildNullStrings & "" & ","
Next iNullStrings
End Function
Sub WriteCSVFile2()
Dim My_filenumber As Integer
Dim logSTR As String
My_filenumber = FreeFile
logSTR = logSTR & "Srv. Descr." & ","
logSTR = logSTR & "E-mail" & ","
logSTR = logSTR & "Env." & ","
logSTR = logSTR & "Prot. Level" & ","
logSTR = logSTR & "DataC" & ","
logSTR = logSTR & "# VMs" & ","
logSTR = logSTR & "Name" & ","
logSTR = logSTR & "Cluster" & ","
logSTR = logSTR & "VLAN" & ","
logSTR = logSTR & "NumCPU" & ","
logSTR = logSTR & "MemoryGB" & ","
logSTR = logSTR & "C_System" & ","
logSTR = logSTR & "D_Homevg" & ","
logSTR = logSTR & "App_eHealth" & ","
logSTR = logSTR & "TotalDisk" & ","
logSTR = logSTR & "Datastore" & ","
logSTR = logSTR & "Template" & ","
logSTR = logSTR & Chr(13)
logSTR = logSTR & BuildValuesString("C", "18,19,20,21,22,26,27,28,29,30,31,32,33,34")
logSTR = logSTR & TotalIt(Range("C32:C33"))
logSTR = logSTR & Chr(13)
logSTR = logSTR & BuildNullStrings(5)
logSTR = logSTR & BuildValuesString("C", "18,19,20,21,22,37,38,39,40,41,42,43,44,46")
logSTR = logSTR & TotalIt(Range("C43:C44"))
logSTR = logSTR & Chr(13)
logSTR = logSTR & BuildNullStrings(5)
logSTR = logSTR & BuildValuesString("C", "18,19,20,21,22,48,49,50,51,52,53,54,55,58")
logSTR = logSTR & TotalIt(Range("C54:C55"))
Open "Z:\Operational Env. Requests\2016\Requests(Test)\" & ThisWorkbook.Name & ".csv" For Append As #My_filenumber
Print #My_filenumber, logSTR
Close #My_filenumber
End Sub
Function TotalIt(rng As Range)
Dim c, arr, t, rv As Double, v
For Each c In rng.Cells
If Len(c.Value) > 0 Then
arr = Split(c.Value, ",")
For Each v In arr
If IsNumeric(v) Then rv = rv + v
Next v
Exit For
End If
Next c
TotalIt = rv
End Function
I.E. 如果单元格C:27具有"VM1，VM2，VM3"，请在不同的行上分隔每个值，但仍输入与其他标头关联的值.
I.E. If cell C:27 has "VM1, VM2, VM3", separate each value on a different line but still input values associated with the other headers.
当前，VBA脚本会将值输入为:
Currently the VBA script would input the values as:
Name Cluster VLAN NumCPU MemoryGB
VM1 VM2 VM3 VMCluster VLAN1 etc.
我想做的是
Name Cluster VLAN
VM1 VMCluster VLAN1
VM2 VMCluster VLAN1
VM3 VMCluster VLAN1
如果我尝试以当前格式执行VBA脚本，它将完全弄乱对齐方式.我希望能获得一些帮助，以实现理想的结果.
It messes up the alignment completely if I attempt to execute the VBA script in its current format. I was hoping to get some help on how I could go about achieving the desired results.
感谢您提供的任何帮助
推荐答案
这在根本上是过分简化的，但是根据您在AC列中的名称"，集群"和"VLAN"的简化示例，可以采用这种方法并将输出拆分为CSV.
This is radically oversimplified, but based on your simplified example of Name, Cluster and VLAN in columns A-C, this would take that and split the output to a CSV.
Sub SplitToCsv()
Dim rw, lastRow As Long
Dim ws As Worksheet
Dim vms, vm, rowData As Variant
Set ws = Sheets("Sheet1")
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Open "C:\test.csv" For Output As #1
For rw = 1 To lastRow
vms = Split(ws.Cells(rw, 1).Value2, ",")
For Each vm In vms
Print #1, Join(Array(vm, ws.Cells(rw, 2).Value2, ws.Cells(rw, 3).Value2), ",")
Next vm
Next rw
Close #1
End Sub
这将作为输入:
Name Cluster VLAN
VM1,VM2,VM3 VMCluster VLAN1
VM4 VMCluster2 VLAN2
VM5,VM6 VMCluster3 VLAN3
并将其转换为如下所示的文件:
And turn it into a file that looks like this:
Name,Cluster,VLAN
VM1,VMCluster,VLAN1
VM2,VMCluster,VLAN1
VM3,VMCluster,VLAN1
VM4,VMCluster2,VLAN2
VM5,VMCluster3,VLAN3
VM6,VMCluster3,VLAN3
-编辑1/3/2017-
-- EDIT 1/3/2017 --
我想我明白了.这是代码的精简版/重构版.我可能是错的，但是我相信，这就是您要执行的操作:
I think I get it now. This is a slimmed-down / refactored version of your code. I could be wrong, but I believe this is what you're trying to do:
Sub WriteCSVFile2()
Dim My_filenumber As Integer
Dim header, line As String
Dim values, vlan, names As Variant
Dim row As Integer
My_filenumber = FreeFile
Open "c:\cdh\" & ThisWorkbook.Name & ".csv" For Append As #My_filenumber
Print #My_filenumber, Join(Array("Srv. Descr.", "E-mail", "Env.", _
"Prot. Level", "DataC", "# VMs", "Name", "Cluster", "VLAN", _
"NumCPU", "MemoryGB", "C_System", "D_Homevg", "App_eHealth", _
"TotalDisk", "Datastore", "Template"), ",")
header = Join(Application.Transpose(Range("C18:C22").Value), ",")
For row = 26 To 50 Step 12
values = Application.Transpose(Range("C" & row & ":C" & row + 8).Value)
names = values(2)
For Each vlan In Split(names, ",")
values(2) = vlan
line = Join(values, ",")
Print #My_filenumber, header & "," & line
Next vlan
Next row
' TotalIt(Range("C54:C55"))
Close #My_filenumber
End Sub
一些注意事项:
-我将TotalIt注释掉只是因为我认为它可以正常工作，而不是专注于该部分
-您的代码查看了第26、37、48行，但电子表格实际上在26、38、50处有引用(另外一行).我的代码反映了后者
Some notes:
- I commented out TotalIt just because I assume it works and wasn't focused on that part
- Your code looked at rows 26, 37, 48 but the spreadsheet actually had the references at 26, 38, 50 (one additional row). My code reflects the latter
我认为，如果您添加更多配置选项，则较小的代码库将更易于调试，并希望具有更高的可扩展性.
I think the smaller code base will be easier to debug and hopefully more scalable, if you ever add more config options.
这是我的输出:
Srv. Descr.,E-mail,Env.,Prot. Level,DataC,# VMs,Name,Cluster,VLAN,...
Service,Email,Environment,Protection,Data Center,,VM1,VMCluster,VLAN1 etc.,,,,,
Service,Email,Environment,Protection,Data Center,,VM2,VMCluster,VLAN1 etc.,,,,,
Service,Email,Environment,Protection,Data Center,,VM3,VMCluster,VLAN1 etc.,,,,,
这篇关于在一个单元格中处理多个值以导出为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
