Building an import routine

  • Thread starter Thread starter Mus' via AccessMonster.com
  • Start date Start date
M

Mus' via AccessMonster.com

Hi

I am trying to build an import routine to handle the import of data from an .
xls file. I am interested to hear feedback from people who have already
tackled similar challenges. I'm not necessarily looking for full answers
(whilst it would be nice it's not for you to do my work) but rather hints,
tips, and considerations.

So far I have written the code to import the data into a new import table.
Once imported I then have added a coupled of extra fields. One of which is
'Errors' which is a memo field. From here I wish to run a number of checks on
the data and where they fail add a pointer to the memo field. Later in the
procedure records that have a null/empty 'Error' field will be imported and
the rest reported back to the user with the error description.

Checks to perform:
1) Remove empty records (empty rows are sometimes pulled in from Excel).
I did consider counting number of fields in the table and for each record if
the value is null/empty incrementing an integer. If the final value of the
integer equals the field count then delete the record. Any better suggestions?


2) Check field names are valid (against table that data will be appended to)
I’m considering creating an array of field names from the destination table,
and then checking all field names against this array….but haven’t used arrays
before!

3) Possibly change any user friendly fieldnames/headings imported from the
worksheet to match those in the destination tables. I should be OK with this.

4) Check compulsory fields have been completed. I should be ok on this.

5) Check data confirms to any constraints/validation. I should be ok on this.

6) Check that the data will go to destination table.
Preferably before running the final append. Any ideas welcome?

7) Trap error/acknowledge whether append is successful.
Any ideas welcome?

All thoughts welcome.

Thanks.
 
You seem to be headed in the right direction. Here is a link to an API that
will make locating the spreadsheet you want to import and getting the correct
name and path:

http://www.mvps.org/access/api/api0001.htm

Here is a sample of how I call the API code from the above site:

Dim varGetFileName As Variant 'Pass to Common Dialog to open workbook
Dim strDefaultDir As String 'Pass Directory to search for common dialog
Dim strfilter As String 'Limit common dialog search to excel workbooks
Dim lngFlags As Long 'Hide readonly check box on common dialog

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If

The above will give the user the ability to locate and get the path and name
of the spreadsheet. It does not do anything with it. It only returns the
path and file name. Note that I set up the defaults where the file is
expected to be, but the user can locate the file if it is not there.

I have some additional comments below:
Mus' via AccessMonster.com said:
Hi

I am trying to build an import routine to handle the import of data from an .
xls file. I am interested to hear feedback from people who have already
tackled similar challenges. I'm not necessarily looking for full answers
(whilst it would be nice it's not for you to do my work) but rather hints,
tips, and considerations.

So far I have written the code to import the data into a new import table.
Once imported I then have added a coupled of extra fields. One of which is
'Errors' which is a memo field. From here I wish to run a number of checks on
the data and where they fail add a pointer to the memo field. Later in the
procedure records that have a null/empty 'Error' field will be imported and
the rest reported back to the user with the error description.

Checks to perform:
1) Remove empty records (empty rows are sometimes pulled in from Excel).
I did consider counting number of fields in the table and for each record if
the value is null/empty incrementing an integer. If the final value of the
integer equals the field count then delete the record. Any better suggestions?
Click to expand...
Here is the easy way to get the number of fields in a record:
CurrentDb.TableDefs("MyTableName").Fields.Count
2) Check field names are valid (against table that data will be appended to)
I’m considering creating an array of field names from the destination table,
and then checking all field names against this array….but haven’t used arrays
before!
Click to expand...

You really don't need an array for this. Here is a routine for getting the
field names in a table:

Sub CountFields()
Dim rst As Recordset
Dim fld As Field

Set rst = CurrentDb.OpenRecordset("tblMasterActivity", dbOpenSnapshot)

For Each fld In rst.Fields
Debug.Print fld.Name ' Do Your Stuff Here
Next fld

rst.Close
Set rst = Nothing

End Sub
3) Possibly change any user friendly fieldnames/headings imported from the
worksheet to match those in the destination tables. I should be OK with this.

4) Check compulsory fields have been completed. I should be ok on this.

5) Check data confirms to any constraints/validation. I should be ok on this.

6) Check that the data will go to destination table.
Preferably before running the final append. Any ideas welcome?
Click to expand...

If you have done all the above, there should not be a problem. The only
checks needed here would be to be sure the data types are compatible and that
text fields in the destination table are long enough to accept the length of
the incoming data.
7) Trap error/acknowledge whether append is successful.
Any ideas welcome?
Click to expand...
I would suggest this method for doing the append. the Execute method is the
fastest way to run an Action query. That is because it goes directly to Jet.
The popular RunSQL has to go through Access to Jet. Use your Error handler
to trap any errors:

CurrentDb.Execute("MyAppendQueryName"), dbFailOnError

You can use either a saved query or you can write the SQL code and pass it
as a string. That is, in fact, what happens when you reference a saved
query. The SQL string is passed to the Execute.

Good Luck
 
Thanks Klatuu.

Import of spreadsheet already achieved yesterday with clsCommonDialog though
the default file location is a nice touch.

1) After a major set back this morning (VBA Project corrupted), achieved step
one but your code is more efficient so have now replaced this as per your
suggestion.

2) I am already with you on capturing the field names in the destination
table but how can I check the field names in the import table against these?
(Bare in mind that the order may be different).

6) Good point on text length, I will check this at the same time as any
validation. Any tips on data type?

7) I am familiar with db.execute sql. I guess I'll test for errors and traps
when I get this far.

Thanks for help and support.
 
Mus' via AccessMonster.com said:
Thanks Klatuu.

Import of spreadsheet already achieved yesterday with clsCommonDialog though
the default file location is a nice touch.
Click to expand...

If you are referring to the ActiveX common dialog control, dump it! It is
more trouble than it is worth. It often the case it will not work when you
move your mdb to another computer.
1) After a major set back this morning (VBA Project corrupted), achieved step
one but your code is more efficient so have now replaced this as per your
suggestion.

2) I am already with you on capturing the field names in the destination
table but how can I check the field names in the import table against these?
(Bare in mind that the order may be different).
Click to expand...
This will take a little creative coding. You can use the code for getting
the field names I sent earlier as a starter. Then you can nest two of them.
One for each recordset. Untested Air Code example:

Sub CheckFields()
Dim rstOne As Recordset
Dim rstTwo As Recordset
Dim fldOne As Field
Dim fldTwo As Field
Dim lngCtrOne as Long
Dim lngCtrTwo as Long
Dim lngFieldsOne as Long
Dim lngFieldsTwo as Long
dim blnMatched as Boolean

Set rstOne = CurrentDb.OpenRecordset("YourAccessTable", dbOpenSnapshot)
Set rstTwo = CurrentDb.OpenRecordset("YourImportTable", dbOpenSnapshot)
lngFieldsOne = rstOne.Fields.Count - 1
lngFieldsTwo = rstTwo.Fields.Count - 1

For lngCtrOne = 0 To lngFieldsOne
blnMatched = False
For lngCtrTwo = 0 to lngFieldsTwo
If rstOne.Field(lngCtrOne).Name = rstTwo.Fields(lngCtrTwo) Then
blnMatched = True
Exit For
End If
If Not blnMatched Then
MsgBox "No Match For Field " & rstOne.Fields(lngCtrONe).Name _
& " In Your ImportTable"
End If
Next lngCtrTwo
Next lngCtrOne

rstOne.Close
rstTwo.Close
Set rstOne = Nothing
Set rstTwo = Nothing
End Sub
6) Good point on text length, I will check this at the same time as any
validation. Any tips on data type?
Click to expand...
You can get the data type with rst.Fields(fieldnumber).Type
I don't have a list of the types, but for example 10 = Text
You can get the size of the field with rstFields(fieldnumber).size
 
Hi Klatuu

Not sure which common dialogue it is, it's something I've inherited. How can
I tell?

You just beat me to no 2, we' ve both came up with something similar though
using differnet tools. See below:

Private Sub sbCheckFldNames()

Dim db As Database
Dim DestTbl As TableDef
Dim ImpTbl As TableDef
Dim fld As Field
Dim MasterFld As Field
Dim strErr As String
Dim strFldName As String
Dim blnCheck As Boolean


Set db = CurrentDb()

Set DestTbl = db.TableDefs("tblImportTest")
Set ImpTbl = db.TableDefs(p_strImportTable)

strErr = "Imported rejected for the following reasons; "

For Each fld In ImpTbl.Fields

strFldName = fld.Name
blnCheck = False
For Each MasterFld In DestTbl.Fields
If (MasterFld.Name = strFldName) Or (strFldName = "Errors") Then
blnCheck = True
End If
Next
If blnCheck = False Then
strErr = strErr & vbCrLf & strFldName & " is not a valid field name."
End If
Next

If strErr <> "" Then
MsgBox strErr, vbCritical
DoCmd.DeleteObject acTable, p_strImportTable
GoTo Exit_sbCheckFldNames
End If


Exit_sbCheckFldNames:
Set db = Nothing
Set DestTbl = Nothing
Set ImpTbl = Nothing
fld = ""
strFldName = ""
MasterFld = ""
blnCheck = Null
Exit Sub


End Sub

I'm calling it a day for now and will continue again tomorrow.

Thanks again.

:o)

Klatuu wrote:
 
If it is a control you put on a form, then it is an ActiveX control. If it
is just code, then it is probably somebody's version of doing the API call.
 
Mus' via AccessMonster.com said:
Hi

I am trying to build an import routine to handle the import of data from an .
xls file. I am interested to hear feedback from people who have already
tackled similar challenges. I'm not necessarily looking for full answers
(whilst it would be nice it's not for you to do my work) but rather hints,
tips, and considerations.

So far I have written the code to import the data into a new import table.
Once imported I then have added a coupled of extra fields. One of which is
'Errors' which is a memo field. From here I wish to run a number of checks on
the data and where they fail add a pointer to the memo field. Later in the
procedure records that have a null/empty 'Error' field will be imported and
the rest reported back to the user with the error description.

Checks to perform:
1) Remove empty records (empty rows are sometimes pulled in from Excel).
I did consider counting number of fields in the table and for each record if
the value is null/empty incrementing an integer. If the final value of the
integer equals the field count then delete the record. Any better suggestions?


2) Check field names are valid (against table that data will be appended to)
I’m considering creating an array of field names from the destination table,
and then checking all field names against this array….but haven’t used arrays
before!

3) Possibly change any user friendly fieldnames/headings imported from the
worksheet to match those in the destination tables. I should be OK with this.

4) Check compulsory fields have been completed. I should be ok on this.

5) Check data confirms to any constraints/validation. I should be ok on this.

6) Check that the data will go to destination table.
Preferably before running the final append. Any ideas welcome?

7) Trap error/acknowledge whether append is successful.
Any ideas welcome?

All thoughts welcome.

Thanks.
Click to expand...

You can always create an Excel Template for the users to use with
correct column headings and include data validation/error checking in
other columns that you do not import.

1. Create the workbook.
2. save it as an XLT, not an XLS file type
3. It should be in your directory:
C:\ Doucments and Settings\UserID\Application Date\Microsoft\Templates
4. Copy the template and tell th eusers to put it into the same folder
on their computer.

This wil help reduce or eliminate future variations with the Workbooks
you import in the future, reducing the risk of your program breaking.

Ron
 
Back
Top