Import Excel into Access?

[GoinDeep]

I have a database which imports Excel field inspection data into my Access main table.

The only problem is the Excel table has a field labeled defects for each record which has a number(1-35) corresponding to a particular defect. Some records have zero defects(null) while others may have several (ex: 1,12,22,27).

How is the best way to store this data so I can querry each record by a particular defect and create reports which includes the defect descriptions? BTW I do have a table with each defect number an corresponding description.

Thanks in advance for any help!

 

[FoFa]

Your BEST solution is to parse the data and store it in indivdual rows in a table. Now that's said, lets talk real world. I don't really see an easy way out of this. LIKE would not work for you because LIKE "*2*" would match 2, 12 22, 20, 42, 102 (well you get the picture). But you have another issue, free form text (maybe numeric) data in a spreadsheet. What stops them from putting in 2 4 12 23 instead of 2,4,12,23? Or a typo and you get 2.4 (after all the period is next to the comma)? Since you (have too?) match these back to another lookup table, indivdual data elements is your true best bet, but parsing is going to be trouble prone at best. Can you make them put the defects in seperate columns? I think a formatted spreadsheet that controls the data input is going to be what you need.

 

[GoinDeep]

Fofa,

Yes, I agree with the concerns regarding data entry, good point.

Maybe there is a better way of approaching the data acquasition?

Let me explain the situation further. I work for an electric utility which hires contractors to inspect our structures. The contractors have been providing this service for several years with inspection data sent in a Excell spread sheet for each designated line segment. The contactors work from remote locations and are not connected to our server.

I have sucessufuly provided an import spec for our distribution system data (which does not contain any "multi select fields"). However, now I am attempting the transmission import spec with the "mult select fields" in question. Is ther a more efficient way to perform this overall process?

If not, could someone give me some direction on how to parse the data assuming it is entered correctly (ex: 2,12,22,23 ect.)?

 

[pmcleod]

Can you filter the data in TXT and use a RegX?

I'm new to this site, so my apologies if I error in etiquette. This is my first time trying to offer help.

Can you filter the data from Excel into a TXT environment like TextPad? If so then perhaps you could use a Regular Expression like: ',\([0-9]+\),' - which would find whole number patterns between commas. OR '\([0-9]\{1\}\),' - which finds the last number before a comma.

(ex: 1,12,22,27)

Find: ,\([0-9]+\),
Finds any comma delimited set of numbers

Replace: \t\1\t
Changes the comma delimited number grouping to TAB delimited - which you could then map to separate Access fields.

(ex: 1,12,22,27) The numbers would remain (in Reg-X the '\1' replaces with the original value) - only the commas would become tabs.

Is this helpful in any way?

 

[GoinDeep]

pmcleod
Yes that could work. however I did get the parse VBA code to work by copying code from another post. I'm just working on how to deal with "null" values since the code doesn't seem to be setup to address this. Thanks for your input!

 

[pmcleod]

Nesting If Not IsNull inside a While loop

What about nesting an "If Not IsNull..." within a While loop? BUT hey what the heck do I know.

Thanks for the feebback. Have a great day! or evening as the case may be.

 

[FoFa]

I would use two functions. One function would return the the number of arguments (basically counts the commas) the second returns the specified argument as a INT (or long or whatever). The logic flow would be something like:
ArgCnt = GetNumArgs([Defects])
If ArgCnt > 0 then
ReDim HoldArgs(ArgCnt)
For LoopIt = 1 to ArgCnt
HoldArgs(LoopIt-1) = GetArg([Defects],LoopIt)
Next LoopIt
end if
Now you have all the values in a array. Of course you do not have to take this approach, you can do whatever you want with the arguments when they are returned from the function. This more an example than a "must do it this way" thing.
Just some of my thoughts...

 

[pr2-eugin]

David, I think the thread is nearly 10 years old..