Converting Data into A Flat File

Topic

New Reply

I have a data source that groups data by company, with several rows of data for each company. I want to get the data into a flat file (one record/Row) for each company to more easily join the data to other data tables. I could live with multiple records for each company (as is currently shown, but still need to be able to get the identifying information (Company Name) on each line and remove all the extra field names/spaces in the current worksheet. I’ve attached a sample of how the data is downloaded. There are about 70 companies with data, currently taking some time to format the data to use in a database. Plus, since the data is updated periodically, I would like a solution that can be easily used for each update. I am (currently) limited to using Access 2003 and/or Excel 2003.

Any help would be appreciated!

David

Reply | Quote

Replies

David,

Attached is your worksheet with a macro added that will flatten the data. Note: I changed the field value for the beginning & ending fields in each set for visual verification of the flattened data. The flattened data appears in sheet 2.

Note: You will have to add the field names to the flattened sheet by inserting a row 1.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks for your quick response. I haven’t worked with macros much or with VBA at all, but there are a couple people I work with that looked at the responses and are helping me work with my live data.

I appreciate your help!

Reply | Quote

I take it the Excel spreadsheet is the incoming format, and you have to start with that. Unfortunately the data isn’t normalized at all, so doing something to create a flat file record is not a trivial task. If the format of the data is always in exactly that format, then you could use VBA to create a flattened record for each company. I would do it using Automation to Excel by opening the workbook and then extracting data from each of the cells and putting it into a flattende record in Access. If you aren’t comfortable out Automation 101 Tutorial would be helpful. If you aren’t really comfortable with VBA in Excel, you could record a macro while you manually copy cells to a flattend row in Excel, and then use the resulting VBA to construct the Access record inside automation. For that matter you could also just run the VBA in Excel and then link to the Excel sheet and use an append query to put the data into and Access database. And Office 2003 will do this sort of thing just fine.

Reply | Quote