Solved

Excel Import into Access

Posted on 2013-11-20
15
576 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 119

Expert Comment

by:Rey Obrero
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
 

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 119

Expert Comment

by:Rey Obrero
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now