Split delimited entries into new rows in Access
Solution 1
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.
Then, in a VBA routine I would
- Open two DAO recordsets,
rstIn
for [TempTable] andrstOut
for [ActualTable] - Loop through the
rstIn
recordset. - Use the VBA
Split()
function to split the [PO] values on "/" into an array. For Each
array item I would userstOut.AddNew
to write a record into [ActualTable]
Solution 2
About about this:
1) Import the source data into a new Access table called SourceData.
2) Create a new query, go straight into SQL View and add the following code:
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
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.
3) Run the query.
Kevin Malloy
Updated on July 26, 2022Comments
-
Kevin Malloy over 1 year
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 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 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.