vba 在 Access 中将分隔条目拆分为新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19797377/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split delimited entries into new rows in Access
提问by Kevin Malloy
So someone gave me a spreadsheet of orders, the unique value of each order is the PO, the person that gave me the spreadsheet is lazy and decided for orders with multiple PO's but the same information they'd just separate them by a "/". So for instance my table looks like this
所以有人给了我一个订单电子表格，每个订单的唯一值是 PO，给我电子表格的人很懒惰，决定处理具有多个 PO 但相同信息的订单，他们只是用“/”将它们分开. 所以例如我的桌子看起来像这样
PO Vendor State
123456/234567 Bob KY
345678 Joe GA
123432/123456 Sue CA
234234 Mike CA
What I hoped to do as separate the PO using the "/" symbol as a delimiter so it looks like this.
我希望使用“/”符号作为分隔符将 PO 分开，因此它看起来像这样。
PO Vendor State
123456 Bob KY
234567 Bob KY
345678 Joe GA
123432 Sue CA
123456 Sue CA
234234 Mike CA
Now I have been brainstorming a few ways to go about this. Ultimately I want this data in Access. The data in its original format is in Excel. What I wanted to do is write a vba function in Access that I could use in conjunction with a SQL statement to separate the values. I am struggling at the moment though as I am not sure where to start.
现在，我一直在集思广益，想出一些方法来解决这个问题。最终我希望这些数据在 Access 中。原始格式的数据在 Excel 中。我想要做的是在 Access 中编写一个 vba 函数，我可以将它与 SQL 语句结合使用来分隔值。我目前正在挣扎，因为我不知道从哪里开始。
采纳答案by Gord Thompson
If I had to do it I would
如果我必须这样做，我会
- Import the raw data into a table named [TempTable].
- Copy [TempTable] to a new table named [ActualTable] using the "Structure Only" option.
- 将原始数据导入名为 [TempTable] 的表中。
- 使用“仅结构”选项将 [TempTable] 复制到名为 [ActualTable] 的新表。
Then, in a VBA routine I would
然后，在 VBA 例程中，我会
- Open two DAO recordsets,
rstInfor [TempTable] andrstOutfor [ActualTable] - Loop through the
rstInrecordset. - Use the VBA
Split()function to split the [PO] values on "/" into an array. For Eacharray item I would userstOut.AddNewto write a record into [ActualTable]
- 打开两个 DAO 记录集，分别
rstIn用于 [TempTable] 和rstOut[ActualTable] - 循环遍历
rstIn记录集。 - 使用 VBA
Split()函数将“/”上的 [PO] 值拆分为数组。 For Each我将用于rstOut.AddNew将记录写入 [ActualTable] 的数组项
回答by Chris Rolliston
About about this:
关于这个：
1) Import the source data into a new Access table called SourceData.
1) 将源数据导入到名为 SourceData 的新 Access 表中。
2) Create a new query, go straight into SQL View and add the following code:
2）新建一个查询，直接进入SQL View并添加以下代码：
SELECT * INTO ImportedData
FROM (
SELECT PO, Vendor, State
FROM SourceData
WHERE InStr(PO, '/') = 0
UNION ALL
SELECT Left(PO, InStr(PO, '/') - 1), Vendor, State
FROM SourceData
WHERE InStr(PO, '/') > 0
UNION ALL
SELECT Mid(PO, InStr(PO, '/') + 1), Vendor, State
FROM SourceData
WHERE InStr(PO, '/') > 0) AS CleanedUp;
This is a 'make table' query in Access jargon (albeit with a nested union query); for an 'append' query instead, alter the top two lines to be
这是 Access 行话中的“制作表”查询（尽管带有嵌套的联合查询）；对于“追加”查询，请将前两行更改为
INSERT INTO ImportedData
SELECT * FROM (
(The rest doesn't change.) The difference is that re-running a make table query will clear whatever was already in the destination table, whereas an append query adds to any existing data.
（其余部分不变。）不同之处在于，重新运行 make table 查询将清除目标表中已有的任何内容，而附加查询会添加到任何现有数据中。
3) Run the query.
3) 运行查询。
回答by Johnny Bones
In Excel, try this:
在 Excel 中，试试这个：
IF(ISERROR(FIND("/",A1,1))=TRUE,A1, Left(A1, 6))
In the cell next to it, put something like this:
在它旁边的单元格中，放置如下内容：
IF(ISERROR(FIND("/",A1,1))=TRUE,"", Right(A1, 6))
This will break out your PO's into 2 columns. From there, write a loop that creates a new record where appropriate.
这会将您的采购订单分成 2 列。从那里，编写一个循环，在适当的地方创建一个新记录。
