Best Solution for Importing Excel Data to Access

[koolwaters]

Hi!

I have an Excel file which is received from IT daily. This file is not normalized and presently, it contains 73 columns and just under 7000 rows. Not all of the columns are used.

The data in this file is split into 7 tables in an Access database. Initially, the data was manually copied into each table in the database at month end but reporting now has to be done daily on this data.

The Access tables are already created and there are underlying queries, forms and reports using the data in this table.

I was exploring using Make Table queries but then I will have issues with the relationships among theses 7 tables as all 7 are related.

Can anyone offer a solution that would easily allow me to get the information in the database daily without having to make too many changes to the existing structure?

In addition, there are a number of columns containing dates. --/--/-- appears for the date fields that do not contain an actual date.

Thanks!

 

[iliace]

It sounds like instead of a MAKE TABLE query you need an APPEND query. You can probably use ADO or something along those lines to get data from each Excel file and write to the database.

As far as the date field, that sounds like a formatting issue. Possibly data was not previously written, so it is blank.

 

[xenou]

I have an Excel file which is received from IT daily. This file is not normalized and presently, it contains 73 columns and just under 7000 rows. Not all of the columns are used.

** All the data is on one sheet/one tab?
** By column not used does that mean column is empty or data in it is there but is simply ignored b/c you don't need it?
** Is the structure reliable - no blank columns, no blank rows, no data inconsistencies (numbers and text stored in the same column, or text and dates stored in the same column?

My main concern is consistency. Almost anything can be imported without problems - IF it is always in the same format and has the same structure.

 

[koolwaters]

Yes, all the data is one one sheet.

All columns contain data but I do not need all of them in the imported.

Yes, the data is consistent. There may be blank cells but no blank rows or columns.

 

[koolwaters]

An append query will not work as the existing data will sometimes change. I understand that the date field is formatted that way as no data is sometimes entered in the date field so the default format is that way.

 

[xenou]

I would almost always start by importing the data into a temp table / staging table. For text and dates (and even for numbers) all the fields in the staging table would be Text. If you have more certainty about the data you can set the data types more precisely.

This lets you check the data for import errors (reported during the import). You also have a record (if you want to keep it) of the original data. You can review the data here with some checks. You could also do some cleanups here - for instance, updating those '--/--/--' values to Null. When ready, then add to your permanent tables.

Typically I run a lot of validation checks on imported data if I think it is necessary - you can create checksums, test data types, count rows, compare to the same results in Excel, etc. etc. - as much as you can think of that helps you to verify the data is correct. If I am using queries, I'll generally script the queries as a batch using a VBA module or form code (or macros) to run them one after another so I don't have to click them all in turn myself.

Naturally, depending on your skill with Excel, you can do some of this Excel-side. For instance, using find/replace to clean up the weird dates.

 

[xenou]

Also, btw, if data is strange (such as text and numbers in the same columns ... or, for that matter, your odd dates) I will export Excel as a delimited file (File ... save as ... CSV or tab delimited).

Imported a text file gives you less problems with the data (essentially, because it is understood to be a text file, so you can decide at import time whether to leave the fields as text or give them a data type). When importing Excel directly, unfortunately, there are routines that kick in to "determine" what the data type is, and this leads to problems when data in the column is not consistent.

 

[Joe4]

Also, btw, if data is strange (such as text and numbers in the same columns ... or, for that matter, your odd dates) I will export Excel as a delimited file (File ... save as ... CSV or tab delimited).

Imported a text file gives you less problems with the data (essentially, because it is understood to be a text file, so you can decide at import time whether to leave the fields as text or give them a data type). When importing Excel directly, unfortunately, there are routines that kick in to "determine" what the data type is, and this leads to problems when data in the column is not consistent.

I agree with this 100%, and use this method a lot. It can be difficult when you cannot control the data format. People often think that is a lot of extra work to do this, but I usually just create an Excel macro to export the data files in the format I want, and then import those created text files.

 

[koolwaters]

The code below is what I have to create a new workbook and separate the data into several sheets to match the tables in the database.

The only columns where the format is an issue are the date columns because where a date is not entered, it automatically inserts the --/--/-- . I am currently working on the code to find --/--/-- and replace with blanks.

I am not sure if the code below is the best but it works in terms of extracting only the columns that I need. Let me know if there is a better method of if my code needs to be "cleaned up".

Option Explicit

Sub AddNewWkb()
Application.ScreenUpdating = False
Dim Wkb As Workbook

    Set Wkb = Workbooks.Add
    ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count), Count:=4
        Sheets("Sheet1").Name = "tblCustomer"
        Sheets("Sheet2").Name = "tblCustomerAcc"
        Sheets("Sheet3").Name = "tblCustomerT"
        Sheets("Sheet4").Name = "tblCustomerTAP"
        Sheets("Sheet5").Name = "tblCustomerNewT"
        Sheets("Sheet6").Name = "tblCustomerP"
        Sheets("Sheet7").Name = "tblCustomerVEC"
    
    Application.DisplayAlerts = False
        Wkb.SaveAs "C:\Users\Lovelife\Desktop\Customer\Data\CustomerImport.xlsx"
        Wkb.Close
    Application.DisplayAlerts = True
    
    Call tblCustomer
    Call tblCustomerAcc
    Call tblCustomerT
    Call tblCustomerTAP
    Call tblCustomerNewT
    Call tblCustomerP
    Call tblCustomerVEC

    Windows("CustomerImport.xlsx").Activate
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    
    Application.DisplayAlerts = False
        Windows("OPEN.LN.TRACK.xls").Activate
        ActiveWorkbook.Close
    Application.DisplayAlerts = True

    If Application.Workbooks.Count = 1 Then
        Application.Quit
    Else
        ActiveWorkbook.Close
    End If
    
    Application.ScreenUpdating = True
End Sub

Sub tblCustomer()
Dim Wkb As Workbook
    Range("A:A,B:B").Copy
    
    Set Wkb = Workbooks.Open("C:\Users\Lovelife\Desktop\Customer\Data\CustomerImport.xlsx")
        Wkb.Sheets("tblCustomer").Activate
            ActiveSheet.Paste
            Range("A2").Select
    
    ActiveSheet.UsedRange.Select
        With ActiveSheet.UsedRange
            .RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
        End With
    
    Range("A1").Select
End Sub

Sub tblCustomerAcc()
Dim Wkb As Workbook

    Windows("OPEN.LN.TRACK.xls").Activate
        Range("A:A,C:C,D:D,E:E").Copy

    Windows("CustomerImport.xlsx").Activate
        Sheets("tblCustomerAcc").Activate
        ActiveSheet.Paste
        Range("A1").Select
End Sub

Sub tblCustomerT()
Dim Wkb As Workbook

    Windows("OPEN.LN.TRACK.xls").Activate
    Range("A:A,C:C,AF:AF,AG:AG,AY:AY").Copy

    Windows("CustomerImport.xlsx").Activate
        Sheets("tblCustomerT").Activate
        ActiveSheet.Paste
        Range("A1").Select
End Sub

Sub tblCustomerTAP()
Dim Wkb As Workbook

    Windows("OPEN.LN.TRACK.xls").Activate
    Range("A:A,C:C,BM:BM,BN:BN,BO:BO,BP:BP,BQ:BQ").Copy

    Windows("CustomerImport.xlsx").Activate
        Sheets("tblCustomerTAP").Activate
        ActiveSheet.Paste
        Range("A1").Select
End Sub

Sub tblCustomerNewT()
Dim Wkb As Workbook

    Windows("OPEN.LN.TRACK.xls").Activate
    Range("A:A,C:C,BR:BR,BS:BS,BT:BT,BU:BU,BV:BV").Copy

    Windows("CustomerImport.xlsx").Activate
        Sheets("tblCustomerNewT").Activate
        ActiveSheet.Paste
        Range("A1").Select
End Sub

Sub tblCustomerP()
Dim Wkb As Workbook

    Windows("OPEN.LN.TRACK.xls").Activate
    Range("A:A,C:C,BC:BC,BD:BD,BE:BE,BF:BF, BH:BH,BB:BB, BK:BK, BL:BL").Copy

    Windows("CustomerImport.xlsx").Activate
        Sheets("tblCustomerP").Activate
        ActiveSheet.Paste
        Range("A1").Select
End Sub

Sub tblCustomerVEC()
Dim Wkb As Workbook

    Windows("OPEN.LN.TRACK.xls").Activate
    Range("A:A,C:C,J:J,K:K,L:L,M:M, N:N,O:O, V:V, W:W, X:X").Copy

    Windows("CustomerImport.xlsx").Activate
        Sheets("tblCustomerVEC").Activate
        ActiveSheet.Paste
        Range("A1").Select
End Sub