Solved

Excel Import into Access

Posted on 2013-11-20
15
584 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 25

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 25

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 50

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 50

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 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 39670613
You are welcome!

/gustav
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

710 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