Need Help on Importing from Excel

[TanmedIT]

Here is the format of the spread sheet in excel:

Part# Serial# RMA#
P4120 124,127 300
P4120 150,151,152 400


Is there a way I can create seperate records based on serial numbers if there is more than one serial number in that cell. For instance importing the above spreadsheet would yield 5 records:

Part# Serial# RMA#
P4120 124 300
P4120 127 300
P4120 150 400
P4120 151 400
P4120 152 400

I need Access to know a new record is needed when it hits a comma.

Is this possible?

 

[GIA]

I would recommend Excel VBA to loop through the whole sheet and rewrite it (with the extra records). The code could use IF logic, roughly something like

i = Instr(foo$,",&quot
if (i=0)
(write out record)
else
while (i<>0)
bar$ = left(foo$,i)
(write out record)
j = i 'avert potential side effects
i = Instr(mid(foo$,j+1),&quot;,&quot + j
wend
endif

Then you can cleanly import &quot;one-for-one&quot; to Access &quot;Outside of a dog, a book is probably man's best friend; and inside a dog, it's too dark to read&quot; - G. Marx