Import Excel Spreadsheet and expand into multiple rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to import an excel spreadsheet, but have run into a data issue.
One of the columns I am trying to import containes multiple values, separated
by a comma. For each value in this column, I need to create a new row during
import, replicating all of the values from the other columns in the
spreadsheet.

Is there a way to do this systematically?
 
Hi Gina,

Systematically, yes; in one step, no - or not without some serious
programming. Here's how I'd do it for a one-off or occasional import:

1) Import or link the spreadsheet as it stands, with the multiple values
in (let's call it) field M.

2) Create a new table with the fields you require, excluding M but
including a field (let's call it V) for the single values from M.

(Normally this table should not include all the fields from the original
spreadsheet, with consequent repeating data, but only the primary key
and the new field V.)

3) Paste this little SafeSplit() function into a module in your database
and save the module (I'll call it vbMisc):

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

4) Create an append query that gets its data from the original table and
appends to the new one. To extract individual values from M, use a
calculated field like this:
V: Safesplit([M], 0)

5) Run the query. This should append a record to the new table for each
record in the original, with each V containing the first value in the
corresponding M.

6) Change the 0 in the SafeSplit() expression to 1 and run the query
again. This gets you records with the second value in each M.

7) Continue until you've run the query as many times as there are values
in the longest M. It doesn't matter if some Ms have fewer values than
others.

8) If some Ms do have fewer values than others, there will be records in
the table with NULL values in V. Dispose of these with a delete query
that deletes all records from the new table where V IS NULL.
 
Back
Top