![代码](https://linux22.com/image/1251409/%E4%BB%A3%E7%A0%81.png)
我必须使用网站以 XLS 格式导出数据,然后将其转换为 CSV,再转储到 MySQL 数据库中。
我的问题是,这个POS导出器把原始数据中的回车符当成了新字段,这样Excel文件就乱了:
例子:
[Name] [Age] [Employment History] // Excel File:
Smith, John 30 2005-2006 Bennigan's // row 1
2006-2007 Shenanigan's // row 2
2007-2008 Grizzlebee's // row 3
Smith, Karen . . . // row 4
这完全破坏了我的一步式 CSV 转换。标准合并命令毫无用处,因为它会删除除第一个字段之外的所有数据。
我发现的唯一解决方案是插入一个相邻列,合并相应的行,将这 3 行连接到第二列的合并后块中,然后删除原始行。但这非常耗时,几乎不可能。
有谁有更好的方法可以解决这个问题?POS 网络导出器由另一家公司控制,完全不受我控制……
编辑以添加“正确”的示例:
[Name] | [Age] | [Employment History] | // Excel File:
______________|__________|__________________________|__
Smith, John | 30 | 2005-2006 Bennigan's | // row 1
| | 2006-2007 Shenanigan's |
| | 2007-2008 Grizzlebee's |
______________|__________|__________________________|___
Smith, Karen | 25 | 2001-2010 Ma Bell | // row 2
_____________|__________|__________________________|___
答案1
根据我们在评论中的讨论,我认为这里有一个宏可以实现您的目标。当然,请先在复制的数据上测试它,以防发生意外。
希望您唯一需要调整的是colToMerge
靠近顶部的常量,以匹配要合并的数据所在的列。
编辑
我刚刚发现Excel 2007在你的问题标题中,所以请注意我写这个2003.
希望如此应该仍然有效,因为它只进行非常基本的移动和单元格值操作。如果无效,请告诉我,我今晚回家后会调整它以适应 Excel 2007。
代码
Sub MergeEmploymentCol()
'PARAMETER: The column number you are merging. A=0,B=1,etc
Const colToMerge = 2
'First unmerge everything
Cells.UnMerge
'Select First "valid" row, done manually incase top rows are packed together
Range("A1").Select
Do While ActiveCell.Value <> 0
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Select
'Some variables
Dim thisRow As Integer, nextRow As Integer, i As Integer
Do While ActiveCell.Value <> 0
'Only need to merge if next row is blank
If (ActiveCell.Offset(1, 0).Value = 0) Then
'Get the row numbers for this valid row and the next valid row
thisRow = ActiveCell.Row
Selection.End(xlDown).Select
If (ActiveCell.Value <> 0) Then
nextRow = ActiveCell.Row
Else
'must be last row - no valid rows below this one
'special handling to find correct row
ActiveCell.Offset(0, colToMerge).Select
Selection.End(xlUp).Select
nextRow = ActiveCell.Row + 1
ActiveCell.Offset(0, -colToMerge).Select
'Exit if this last valid row has no duplicates
If (thisRow + 1 = nextRow) Then
Exit For
End If
End If
Selection.End(xlUp).Select
'Merge the data between here and the next valid row
Dim combinedData As String
combinedData = ""
For i = 0 To (nextRow - thisRow) - 1
combinedData = combinedData & ActiveCell.Offset(i, colToMerge).Value & Chr(10)
Next i
'Trim last (unnecessary) line-return
combinedData = Mid(combinedData, 1, Len(combinedData) - 1)
'Place data in cell
ActiveCell.Offset(0, colToMerge).Value = combinedData
'Delete unneeded rows
ActiveCell.Offset(1, 0).Rows("1:" & nextRow - thisRow - 1).EntireRow.Select
ActiveCell.Offset(0, 0).Range("A1").Activate
Selection.Delete Shift:=xlUp
'Select first cell of next valid row
ActiveCell.Offset(0, 0).Select
Else
'Next row not blank, so no need to merge
'Simple step down to next row
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub