Import fr Excel - Force Text Format for 9 Character Cell

[Jim Dudley]

Background:
There are two different Excel Templates that are filled in at various campuses and then forwarded to a Central Administration office where the contents are Imported into Access 2010.

The import function is automated via VBA code and inserts the contents of each spreadsheet into a Temporary Table. (This code was created by a Contract Programmer that is no longer available) A series of queries sorts the Temporary tables' records into 2 Categories. (Complete and data missing). The completed records are appended to the main table and the incomplete records are appended to a holding table. Depending on the source spreadsheet the tables differ in as much as each source spreadsheet has its' own set of tables.

Issue:
The Excel templates are filled in by volunteer student help at the various College campuses before being forwarded to Central Administration for uploading to Access 2010. Each student is supposed to fill in a pre-set template with the following columns formated as follows:
Wdate (Workshop date) as 'Short date' (mm/dd/yy) - (This concern has been resolved)
Snum as Text (Snum is a 9 Character unique identifier assigned to each student. It is treated as Text and in all the tables it is Text field. Many of these identifiers begin with a"0" {Zero}. Therefore if they are put into an Excel Cell the leading Zero is dropped unless the Cell is formatted to Text.)
This dropping of the leading zero either on input or Import creates a problem.

Required solution would be to have some VBA code examine each [Snum] Cell and check the length of the data. If it is an 8 digit number, add a zero to the beginning and import the 9 digit identifier as TEXT. If it is already 9 Characters/Digits long, import it as TEXT.
e.g. Iif(len[Snum]=8),"0"&[Snum],[Snum]. If the [Snum] is eight characters long, add a "0", if it is 9 Characters long leave it as is but import it as TEXT not Numeric.

Question:

How do I put this statement into VBA and where should it be inserted?


System:
MS Office 2010
Windows 7

User:
Almost a beginner at using VBA

Other info:

I have attached a zipped copy of the import Code.

 

[spikepl]

In Excel, select the entire column and format it to text. Afterwards you can deal with the missing 0's in some update query in Access

 

[Jim Dudley]

In other words forget about what excel does and query the temporary table the import creates to find any 8 digit identifiers and update them to a 9 digit identifier by concatenating a 0 (zero). The temporary file when created is treating the [SNum] field as Numeric not Text. How can I force this field to be text when it is created? Can this be done in the code?
Second thought, what needs to be done is a Select/Update query to deal with any 8 digit Identifiers [SNum] and then just proceed with the rest of the code that already has been created. Some sort of Iif proceedure that cheques all the fields in the first tmp_table. Would that be correct?
It appears that the existing code forces the [SNum] field to Text.
Jim

 

[spikepl]

Did you try what I suggested?

Do not create code unless necessary. Your issue is that input data apparently is not consistent, in that some fields are TEXT others are NUMBER in the same column. That throws Access import gnomes. You could write a lot of code to get into the Excel sheet and do stuff cell by cell, but that is kind of pointless, me thinks.

And yes, an update query that for each field that is less than X characters prepends "0" in sufficient number.

 

[Pat Hartman]

This is an excerpt from a procedure I use to import spreadsheets that are also rife with problems. In particular, take a look at the code for the ZipCode since zip codes have the same problem. The string of 0's in the Format() function should equal the total length of the field, so use 9 0's if your field is 9 characters long.

    ' trim extraneous spaces and convert all fields except dates to strings
    'this SQL is embedded to keep Access from breaking it if the temp_ table has been deleted.
    strSql = "INSERT INTO tblMembers ( EmpNum, FirstName, MiddleInit, LastName, DOB, PlanType, "
    strSql = strSql & " StreetAddress1, StreetAddress2, City, State, ZipCode, HomePhone, CompanyPhone,"
    strSql = strSql & " CompanyExt, CompanyEmail, LanguagePreference, Comments, DivisionName, AuditParmsID, "
    strSql = strSql & " UpdatedBy, UpdatedDT, InsertedDT )"
    strSql = strSql & " SELECT Trim(cStr(EmpNum)) as strEmpNum, Trim(FirstName) as strFN, Trim(MiddleInit) as strMI, "
    strSql = strSql & " Trim(LastName) as strLN, DOB, Trim(PlanType) as strPT, Trim(StreetAddress1) as strSA1, "
    strSql = strSql & " Trim(StreetAddress2) as strSA2, Trim(City) as StrCity, Trim(State) as strSt, Format(Val(ZipCode), ""00000"") as strZip, "
    strSql = strSql & " Trim(cStr(HomePhone)) as strHomePhone, Trim(cStr(CompanyPhone)) as strCompPhone, Trim(cStr(CompanyExt))as strCompExt, "
    strSql = strSql & " Trim(CompanyEmail) as strCE, Trim(LanguagePreference) as strLP, Trim(Comments) as strC, "
    strSql = strSql & " Division, Forms!frmLogin!cboAuditParmsID AS Expr1, 'Import' AS Expr3, #" & gHoldMemberDateTime & "# AS Expr2, #" & Now() & "# as Expr4"
    strSql = strSql & " FROM temp_Members"
    strSql = strSql & " WHERE EmpNum Is Not Null;"

    Set db = CurrentDb()
    db.TableDefs.Delete "temp_Members"  'delete last import
    'count current rows
    BeforeCount = DCount("*", "tblMembers", "AuditParmsID = " & Forms!frmLogin!cboAuditParmsID)
    'link spreadsheet, txt, or csv file
    Select Case Mid(Me.txtFileNameMember, InStrRev(Me.txtFileNameMember, ".") + 1, 3)
        Case "csv"
            DoCmd.TransferText acLinkDelim, , "temp_Members", Me.txtFileNameMember, True
        Case "txt"
            DoCmd.TransferText acLinkDelim, "SubPipe", "temp_Members", Me.txtFileNameMember, True
        Case Else
            DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "temp_Members", Me.txtFileNameMember, True
    End Select

 

[Jim Dudley]

Yes, I have tried what you suggested in the past and am aware of how it works. That is why I created the templates with the date column and the SNum column pre-formatted. But if a user makes a mistake and erases a row or several rows of entry the pre-formating is destroyed.

I like what you are doing in the sample code. It resolves user related input issues and puts control back under the control of Access.

I will try to adapt this to our application and let you know how I make out.

I appreciate your time to help with this matter.

Have a good rest of the day.

Regards,

Jim