Solved

Excel Import into Access

Posted on 2013-11-20
15
583 Views
Last Modified: 2013-11-22
I have an excel file where there are number of columns that are formatted as Date fields in Excel. When I import them into Access via a script it changes the format to Text on them because some of the fields are blank. Is there any way to fix this? Currently I have a straight import where I don't have a separate piece of code parsing the data and this is the only spreadsheet I have issues with. Any way to fix without having to create a piece of code specific to just this one spreadsheet? It appears that it is looking at the first row that has data and if it is blank it does text and if there is a date it does a date. There is no scenario that will have every single cell in a row that has to be a date have a date........ HELP!!!!!!
0
Comment
Question by:dminx13
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39664567
I think you need to import to an existing Access table. In this case the table will have all the column with pre-defined types and nothing will be changed
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39664811
open the excel file and do a sort in the Date column so that the first row will have value, save and do your import
0
 

Author Comment

by:dminx13
ID: 39664942
Right now it deletes the table and recreates it. Is it easy to code it so that it just deleted that contents of the table and not the whole table?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:dminx13
ID: 39664944
Capricorn1: I've got 6 date columns and the top row will never have all 6 dates filled in :-( otherwise I would do that.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39664945
to delete the content from tables use TRUNCATE Table1 syntax. I guess you use DROP Table1 syntax to delete the tables. As you can see the syntax is not much bigger to clear the content, but provides much better options in terms of data integrity
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39665088
> Is it easy to code it so that it just deleted that contents of the table
> and not the whole table?

You need two queries. One to delete all records in the table, the other to append your Excel data to the table.
However, the data fields may still be text. If so, add another field of data type Date and run an update query where you update that field using the expression:

CVDate([YourTextDateFieldname])

like this:

UPDATE
    tblYourTable
SET
    [YourDateFieldname] = CVDate([YourTextDateFieldname])

/gustav
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39665705
< I've got 6 date columns and the top row will never have all 6 dates filled in >

In that case, sorry to say but you will need a vba solution to get this done correctly while importing the  content of the excel file.
0
 

Author Comment

by:dminx13
ID: 39669097
So I normally write a query in query wizard and then traslate it to VB Code. I don't know how to update a format in just the straight query design.

Here is what happens:

Private Sub ImportPSExcelFiles()
    Dim db As Database
    Dim rs As Recordset
    Dim sSQL As String
    Dim sFile As String
    Dim sTable As String
    Dim sProgress As String
   
    sSQL = "SELECT DataDirectories.Directory, "
    sSQL = sSQL + "DataFiles.FileName, DataFiles.TableName, DataFiles.Hidden "
    sSQL = sSQL + "FROM DataDirectories INNER JOIN DataFiles ON "
    sSQL = sSQL + "DataDirectories.DirectoryID = DataFiles.Directory "
    sSQL = sSQL + "WHERE (((DataFiles.FileType)="
    sSQL = sSQL + "'" + "Excel" + "'"
    sSQL = sSQL + ") AND ((DataFiles.Status)="
    sSQL = sSQL + "'" + "A" + "'"
    sSQL = sSQL + "));"
   
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL)
   
    With rs
        Do
            sFile = .Fields("Directory") + .Fields("FileName") + ".xls"
            sTable = .Fields("TableName")
            '  Delete existing table if it exist
            Call DeleteTable(sTable)
            '  Update progress display
            sProgress = BlankSpace + BlankSpace + BlankSpace + "Deleting Data in " + sTable
            Call RefreshProgress(sProgress)
            sProgress = BlankSpace + "Importing " + sFile
            Call RefreshProgress(sProgress)
            '  Import spreadsheet
            Call ImportSpreadsheet(sFile, sTable)
            '  Update FileDate attribute in the DataFile table
            Call UpdateFileDate(sFile, sTable)
            '  Hide the table
            '  If record is marked to hide the table, hided it
            If rs.Fields("Hidden") = -1 Then
                Call MakeHiddenTable(sTable)
            End If
            .MoveNext
        Loop Until .EOF
    End With
   
    Set rs = Nothing
    Set db = Nothing
   
End Sub

So that deletes and imports the table called 'Dates'

After it is created I was thinking that I could call a function like this:
Private Sub UpdateDates()
    '  Procedure is used to update the Dates Table
    Dim sSQL As String
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL (sSQL)
       
    '  Update the fields in Dates so they are the correct format
    sSQL = ""
    sSQL = sSQL + " "
    sSQL = sSQL + " "
    sSQL = sSQL + " "
    sSQL = sSQL + " "
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
       
    DoCmd.RunSQL (sSQL)
    DoCmd.SetWarnings True

End Sub

But I don't know what to put on the lines to update the fields to dates. These are the fields:

ID = Text
Rcd# = Number
Brcd# = Number
Hire Date = Date
Last Asgn Start = Date
Rehire Dt = Date
Service Dt = Date
Return Dt = Date (this is always blank I've never seen it populated but it should be a date format.
Term Date = Date
Probtn Dt = Date
SPO Print = Test
Last Date = Date
Eff Date = Date
Indicator = Text

Right now depending on what row is on top of the excel file when it imports date formats are coming in as text so I need to have the UpdateDates convert it so they are date......Thoughts?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39669181
I use a query to retrieve the data from Excel as clean as possible, then another query to transform, filter, concat, split, and convert as needed. This way is quite fast for try and error which often is needed initially.

/gustav
0
 

Author Comment

by:dminx13
ID: 39669212
OK.... so how does that codeing look? I am going on vacation next week and am trying to make this as neat for the person doing it as possible. Which means she pushes button and it goes......... The code that I put above is just one piece of a larger one. After all the excel files automatically inport into access there are sub routines that create more tables from the excel files. Then about 300 report automatically print out.

So right now I am trying to code the update to the dates table because up to this point I was halting the process before the print. Manually changing the field types, saving the file and then printing the reports. Which I coude write into a step by step for her, but at this point she is already getting frustrated with the whole thing so I am trying to fins a solution that does not involve manual intervention.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39669292
Problem is that what to need to done with the data often is very specific to the actual Excel file. This is very hard to do in code, and I've found it much easier to perform via queries built using the query designer.

I have up to three layers of queries: The first to attach the file and perhaps rename some fields to match the purpose and avoid conflicts, and filter out totally meaningless rows. The second and/or third to perform conversions (like your date fields) and clean-up (like trim or replacing invalid characters), and finally - perhaps applying some basic business logic - to produce the real source of data which - at this point - should have a quality as if you pulled the data from an Access table.
In rare cases, where I cannot get rid of #Num or #Error, I insert an additional step between layer one and two writing to a temp table which I then can clean up before proceeding.

Now data are ready for processing like you describe, running a series of queries, applying business logic, printing reports, etc.

/gustav
0
 

Author Comment

by:dminx13
ID: 39669812
I did some of what you suggested I think. I had the excel import as normal (Dates2). Then I just created an insert query to take the data from the Excel Import and put it into a table (Dates) that had the correct formats and is used in all my reports. It has worked 3 times in testing it so I think that I am fixed. Is that what you were trying to say above?

    sSQL = "INSERT INTO Dates ( EmplID, [Empl Rcd#], [Ben Rcd#], [Hire Date], [Last Asgn Start], "
    sSQL = sSQL + "[Rehire Dt], [Service Dt], [Return Dt], [Term Date], [Probtn Dt], "
    sSQL = sSQL + "[SPO Print SSN], [Last Date], [Eff Date], Indicator) "
    sSQL = sSQL + "SELECT Dates2.EmplID, Dates2.[Empl Rcd#], Dates2.[Ben Rcd#], "
    sSQL = sSQL + "Dates2.[Hire Date] ,Dates2.[Last Asgn Start], "
    sSQL = sSQL + "Dates2.[Rehire Dt], "
    sSQL = sSQL + "Dates2.[Service Dt] , Dates2.[Return Dt], "
    sSQL = sSQL + "Dates2.[Term Date], "
    sSQL = sSQL + "Dates2.[Probtn Dt] , Dates2.[SPO Print SSN], "
    sSQL = sSQL + "Dates2.[Last Date],  Dates2.[Eff Date], Dates2.Indicator "
    sSQL = sSQL + "FROM Dates2; "
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39669934
Yes, exactly. Your setup will of course differ, as no two scenarios are equal. Neither have I had the requirement to generate 300 reports.

/gustav
0
 

Author Comment

by:dminx13
ID: 39670207
It is for our data entry double check. We call it Error Check they don't all print. But a good number of them do! Thanks for your help!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39670613
You are welcome!

/gustav
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question