Create Access Table From Excel Flat File

[Rupert Bennett]

I have several Excel worksheets that make up my annual budget. These worksheets have account numbers in one column and one column for each month, going across the page. I would like to get these tables into Access in a manner that would allow easy manipulation of the data.
The only way I know, is to bring these tables into Access, create a new table with a date field and with a series of queries, append each month to this new table.
Can anyone suggest an easier way? It seems like my method will take too much time.
Thanks for your help.
Rupert

 

[Dan Rittgers]

I would cut and paste in Excel for 60,000 lines and then import into Access, but before you do, try to get copy "Special Edite"/transpose (In Excell) to flatten the fille so the accounts and months are on a row. Copy the acocunt umber down the twelve months and then cut and paste in Excel then import into Access.

Use Cross-tab in Access

 

[Rupert Bennett]

Dan,
I am sorry for not closing this thread long ago. Thanks for your suggestion, however. I needed to get this done in a hurry and was hoping that someone could give me a VBA routine to accomplish my task.
I brought in each month as a separate table and append them all to one main table.
Thanks again for your response.
Rupert

 

[Fazza]

Hi, Rupert.

The very active Excel forum might have been a better place to address the question. A little VBA could create an *.mdb file (even without Access being installed) using ADOX and then ADO used to populate the table. The ADO can pull data from one or more Excel files. (Being VBA, the code can in fact be located in MS Outlook or MS Word - Excel need not be opened or even be installed!) Here is a sample from a few months ago http://www.mrexcel.com/forum/showthread.php?t=301503

For your situation, one way might be with some SQL a bit like below. 'January' AS [MonthField] is entering the text January into the MonthField field and January AS [BudgetValue] is entering the value from the Excel table in the column headed "January" into the BudgetValue field. I understand that your Excel columns have headers "January", February", "March", etc. Untested. HTH, Fazza

INSERT INTO [tblName]
SELECT AccountNum, MonthField, BudgetValue
FROM
(SELECT AccountNum, 'January' AS [MonthField], January AS [BudgetValue]
FROM [WksSheetName$] IN 'C:\DataFiles\Book1.xls' 'Excel 8.0;'
UNION ALL
SELECT AccountNum, 'February' AS [MonthField], February AS [BudgetValue]
FROM [WksSheetName$] IN 'C:\DataFiles\Book1.xls' 'Excel 8.0;'
UNION ALL
SELECT AccountNum, 'March' AS [MonthField], March AS [BudgetValue]
FROM [WksSheetName$] IN 'C:\DataFiles\Book1.xls' 'Excel 8.0;'
etc, etc )
WHERE BudgetValue Is Not Null

 

[Fazza]

PS
I remember testing the CREATE table SQL after posting the example linked above. After taking out all the COMPRESSION stuff it was still OK.
F

 

[Rupert Bennett]

Thank you, Fazza.
I have not been able to test it out, but looking at the code, I think it is just what I was looking for. I wish I had gotten this before now, because I am sure it would have saved me a lot of time.
However, I have many nore uses for it. I have more budgets coming up and there are several properties involved. I am sure this will be extremely useful.
Thanks again.
Rupert