Results 1 to 10 of 10
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Access Import Routine Throws Errors based on Excel's alpha-numeric column/field

    Experts:

    I need some assistance with "tweaking" the data type/field output based on an import routine. Attached is both a spreadsheet and database for demo purposes. Below is information about these two files:

    1. Spreadsheet "Test.xlsx":
    - Contains two columns... "Field1" and Field2"
    - "Field1" is the critical field that requires some tweaking.
    - The data in "Field1" is a 5-digit alpha-numeric code. I can neither change the format in the source system nor do I want to change the format in the XLS to, e.g., "text" is it would drop the leading zeros.
    - For testing purposes only, "Field2" (temporary) uses a formula an converts values from column 1 into a text field with leading zeros.

    Again, "Field2" is temporary and it doesn't exist in my source data.

    2. Access "Test.accdb" contains the following:


    - "Table1" -- which mimics the spreadsheet structure.
    - "Form" including an import routine in VBA.
    - Note: Once you extraced/unzipped the files, please ensure to update the filepath (in VBA's line 6) accordingly.

    3. Process and Existing Issues:
    a. Open form and click "Import Records"
    b. Assuming you updated the filepath in the VBA, the content from the Excel file is appended to Table1.
    c. Here's the problem now... executing the import routine resulted in some import errors. Specifically, records #13, #29, and #30 were NOT imported into Field1. These are three XLS records containing alpha-numeric values.

    4. How data should look like:
    a. Field2 represent an output how Field1 should look like.
    b. At the risk of repeating myself, please keep in mind that my source data doesn't have Field2 in its data source. I merely used this for testing purposes only.

    All that said, how can I use the attached Form's VBA and somehow tweak my table format so that Field1 will a) not throw any import errors, b) will show all 30 alpha-numeric values, and c) not lose the leading zeros so that I show, e.g., "00060" (vs. just 60)?

    Thank you for any help in advance!
    EEH
    Attached Files Attached Files

  2. #2
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    SOLVED PROBME MYSELF... I used an import routine and set the datatype = text in the routine. Then, in VBA, I'm calling the import routine and it works as intended.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this is always one of this issues with importing from excel - much better to save as a .csv file and import from that - you can then specify the column types

    If you have headers, you can force the issue and set headers = no, then ignore the first row

    my preferred method is using sql instead - you can convert this to a maketable, insert or update query and include other criteria, link to other tables etc, rather than inserting to a temporary table and using that instead

    SELECT * FROM [sheet1$A:B] IN 'C:\tmpFiles\test.xlsx'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes] WHERE F1<>'Field1';

    and if you want to retain field names rather than F1 etc you can alias them

    SELECT F1 as Field1, F2 as Field2 etc

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax - thank you... I tried the SQL but still get the errors.

    As mentioned on immediately follow-up post, I ended up saving an import routine (including the conversion into a text field for Field1). Then, I my VBA I execute the import routine.

    Thank you for chiming in... very much appreciated.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I used exactly that code on your Xl file and did not get any errors. all you should need to have done is change the path. It is how I import from Xl and has been for many years without any problems. Suggest post what you actually used and if based on a different file, provide the file as well.

    Edit: but if you have a solution then don't worry about it

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you would post your import routine to help others in this situation??

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax:

    Attached is the modified DB.... currently, I modified the path in 2 places (on top of function and SQL statement). No records are imported at this time though. What am I missing in the VBA?

    Also, if needed, how can I streamline the VBA. I only want to import one file and one file only. How can I specify the file name to be imported?
    Attached Files Attached Files

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I hear you, ssanfu. The import routine is saved via routine name. Not sure how to extract it though. Please expand on how to extract code/steps of a saved import routine.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    all you've done is created the sql string

    you need to modify it to insert into table1 which means aliasing the fields and then execute it

    Code:
    'DoCmd.TransferSpreadsheet acImport, , "Table1", strPath & strFileList(intFile), True
    strFile2 = "INSERT INTO table1" & _
                                    " SELECT  F1 AS Field1, F2  As Field2 FROM [Sheet1$A:B] IN 'C:\Users\dellc\Desktop\TestingImport\Test.xlsx'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes] WHERE F1<>'Field1'"
    CurrentDb.Execute strFile2
    How can I specify the file name to be imported?
    same as you do for your transferspreadsheet

    " SELECT F1 AS Field1, F2 As Field2 FROM [Sheet1$A:B] IN '" & strPath & strFileList(intFile) & "'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes] WHERE F1<>'Field1'"

    I only want to import one file and one file only
    remove the loop and supply the filename

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax - thank you... still not exactly clear on the syntax that includes Excel file name, e.g., "Test.xlsx". Below is the latest code:

    Code:
    Private Sub cmdButton_ImportSurvey_Click()
    
         'Import Excel files to a specified Access Table
         Const strPath As String = "C:\Users\dellc\Desktop\TestingImport\"
         
         'Filename | File Array | File Number
         Dim strFile As String
         Dim strFileList() As String
         Dim intFile As Integer
         Dim MySql As String
         
         
         Dim strFile2 As String
         
         'Drop records from table "Tabl1"
         MySql = "DELETE Table1.* FROM Table1;"
         CurrentDb.Execute MySql
            
         'Loop through the import folder
         strFile = Dir(strPath & "*.xlsx")
         While strFile <> ""
             'Add files to the list
             intFile = intFile + 1
             ReDim Preserve strFileList(1 To intFile)
             strFileList(intFile) = strFile
             strFile = Dir()
         Wend
          'Check whether or not any files were found
         If intFile = 0 Then
              MsgBox "No files were found!", vbInformation, "Information"
             Exit Sub
         End If
    
    
        strFile2 = "INSERT INTO Table1" & _
                   " SELECT  F1 AS Field1, F2  As Field2 FROM [Sheet1$A:B] IN 'C:\Users\dellc\Desktop\TestingImport\Test.xlsx'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes] WHERE F1<>'Field1'"
        
                   
        'strFile2 = "INSERT INTO table1" & _
                   " SELECT F1 AS Field1, F2 As Field2 FROM [Sheet1$A:B] IN '" & strPath & strFileList(intFile) & "'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes] WHERE F1<>'Field1'"
                         
        CurrentDb.Execute strFile2
        
        'Throw message box upon file input
        MsgBox "Records have been successfully imported!", vbInformation, "Information"
    
    End Sub
    Please advise how to modify the VBA for importing filename "Test.xlsx". Thank you!

    //

    Final question... if my actual spreadsheet includes, e.g., 50 columns, is there a way to identify the cell range? Further, do I then have to list every single column and specify the target field?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DMax +1 a field with alpha and numeric characters
    By beewerks in forum Programming
    Replies: 6
    Last Post: 02-12-2018, 05:06 PM
  2. Import VBA routine does not recognize the Excel File
    By jyellis in forum Import/Export Data
    Replies: 4
    Last Post: 09-08-2017, 01:20 PM
  3. Replies: 12
    Last Post: 10-08-2014, 02:35 PM
  4. Replies: 1
    Last Post: 03-09-2014, 06:30 PM
  5. Alpha Numeric auto id?
    By arshadmgic in forum Forms
    Replies: 7
    Last Post: 09-02-2012, 03:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums