Reading a spreadsheet into access

[hgus393]

Hi all,

I get a report in excel that is formatted quite strangely. I would like to import this file into access. The problems are the following:
The file itself has headers -which is good. However, the file itself contains a lot of merged cells which need to be unmerged. The second problem is that the file contains subheaders located under the headers, which need to be incorporated into the datarows ie a new column. The third problem is the spaces bewteen the subheaders and the datarows. Any clues how to get this into access with vba? (I have included an example)

 

[darbid]

Hi all,

I get a report in excel that is formatted quite strangely. I would like to import this file into access. The problems are the following:
The file itself has headers -which is good. However, the file itself contains a lot of merged cells which need to be unmerged. The second problem is that the file contains subheaders located under the headers, which need to be incorporated into the datarows ie a new column. The third problem is the spaces bewteen the subheaders and the datarows. Any clues how to get this into access with vba? (I have included an example)

Hi hgus393
I am picking up your question as nobody else has yet. Your question is pretty broad at the moment and would take me a long time to even get started with an answer.

May I suggest you get started and then use this thread to get some of the finer problems solved.

I have no idea how to unmerge cells, but the first thing I would do is to open excel and record a macro. While recording I would merge and unmerge cells and view the results of the code recorded.

Dealing with formatting is always difficult. Personally I try to get the person that makes the document to format it more friendly (which probably will fail). Then try to understand how it is formatted and what can could or will change each time. Then set about thinking how to parse/copy/read the data.

Once you get more information on the format etc you can start to decide how to read the information. If you have a perfect table with headings, you can actually build a record set of the data and read it. If however things are bad and unreliable you might have to start with the first cell A 1 and go step by step through each cell, check its content and decide what to do with it. But once you get more information on the format etc you can then work on this part.

 

[gemma-the-husky]

I would try and avoid this if possible

where does the spreadsheet data come form. If it comes from a csv or text file initially, I would import THAT, not the spreadsheet.
The trouble with spreadsheets is twofold. Data may be changed manually, and you aren't sure what you are getting. Access makes (sometimes incorrect) assumptions about spreadsheets that you cannot change. If you use a csv/text file, you have complete control.

iIf you HAVE to import the spreadsheet, I would get it changed (ie get the users to change it) to remove the merged cells, and features that prevent it importing. After you import it, I would verify that you have all the column names you expected. If you haven't reject the import.

Maybe the spreadsheet users can save a csv for you

Don't mess around with this. You want to automate this procedure, which means it needs to be consistent. It ought to be up to the users/dept managers to sort their data out properly. There are limits to what you can do otherwise. If the spreadsheet is wrong (ie won't import), and it isn't your spreadsheet, then it shouldn't be your job to assume how it should be fixed.

 

[hgus393]

The spreadsheet comes from a batch system. The spreadsheet will be dynamic in rows but the headers will be the same. Yeah I guess that going to the source system will be my best bet.
Cheers for the insights
Rob

I would try and avoid this if possible

where does the spreadsheet data come form. If it comes from a csv or text file initially, I would import THAT, not the spreadsheet.
The trouble with spreadsheets is twofold. Data may be changed manually, and you aren't sure what you are getting. Access makes (sometimes incorrect) assumptions about spreadsheets that you cannot change. If you use a csv/text file, you have complete control.

iIf you HAVE to import the spreadsheet, I would get it changed (ie get the users to change it) to remove the merged cells, and features that prevent it importing. After you import it, I would verify that you have all the column names you expected. If you haven't reject the import.

Maybe the spreadsheet users can save a csv for you

Don't mess around with this. You want to automate this procedure, which means it needs to be consistent. It ought to be up to the users/dept managers to sort their data out properly. There are limits to what you can do otherwise. If the spreadsheet is wrong (ie won't import), and it isn't your spreadsheet, then it shouldn't be your job to assume how it should be fixed.

 

[gemma-the-husky]

I bet your system generates an ascii csv, or a txt file then, They are the ones you need.

fixed width, or csv - try docmd.transfertext