sub grouping convert to flat file

**tushar101** · 05-10-2011, 06:29 AM

Hi

I'm trying to process an Excel file that contains sub groupings/outlines in the data.

I would like to convert this to a flat file for import and further processing into an Access database. Is it possible to do this without writing custom VBA code? I've tried exporting the data but the sub headers end up as data rows. I've also tried exporting as XML, DBF etc and re-importing into Access but that doesn't work either.

The reason the Excel file has groupings in the first place is because the third party product we're using only exports in that format.

Any suggestions gratefully received.

Tushar

**davegugg** · 05-13-2011, 11:34 AM

I'm not sure whether or not it can be done without vba (I doubt it), but it would be quite easy to do with VBA. Create a sub in Access that will import the file, then run a simple DELETE SQL Query to get rid of the subtitles. It should be about 4 lines of code, something like this:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tablename", "workbooknameandpath", True
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tablename WHERE [fieldname] Is Null"
DoCmd.SetWarnings True

Pick a field for fieldname that is not populated only for the subgroups records.

**tushar101** · 05-16-2011, 04:52 AM

thanks, Dave. With the above method the child records would not have a link to the parent record, which doesn't help us. I'd have to go create VBA code to go through each line, grouping records together under the same parent, and give them a unique identifier. Only then will I be able to process the data in Access. I'm really surprised that there is no way to export the data so that the child records are automatically linked to the parent, so that we can process the data in the way we need to. I know it can be done in VBA but going through each record will be more time consuming. I'm hoping there's another way round it.

Thanks again

Tushar