Results 1 to 3 of 3
  1. #1
    dante2004 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    21

    Point me in the right direction...who to format data during import

    Meant to type HOW to format...not WHO to format... haha

    I'm trying to search on this, but I don't even know the words to use for the search so I haven't had much luck.

    I have two excel files that I want to import (I know how to do this)
    There is a relationship between the two tables, but the data in those columns is not in the same format



    Sales Order number

    Table1: 16/123456
    Table2: 123456

    Can I remove the 16/ from the one column during import, or do I need to do this manually before the import? This is something I do daily, so a macro, or formatting in Access would be ideal

    I also have to change another column (actually 6) that are a direct export from SAP. It's spitting out dates like '12/12/16 rather than 12/12/16. I convert this with "text to column" in the data tab in Excel, but again, I'd rather have a more automated process.


    If someone has an easy solution, I would appreciate it. But ideally I'm looking for the what this would be called so I can research it and learn for myself.

    Thanks!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What many do is bring the Excel data into separate temporary tables in Access; edit/reformat as necessary;
    then move the data to "final" tables; when all is well--delete the temp tables.

    If you have to do this repeatedly.

    Design the final tables with appropriate data types and relationship(s).
    Read data into temporary tables
    Create user defined functions to reformat the data etc as necessary
    Create a routine to put the data into designed tables.

    It is unclear if you have to append the data to the table(s), or
    if you delete previous contents before inserting the latest data into the tables.

  3. #3
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    For the table containing the format 16/123456, you could run a vba macro in excel before importing. I am assuming that the data is in Column A.

    Code:
    Option Explicit
    
    
    Sub delslash()
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr
    Range("A" & i) = Right(Range("A" & i), Len(Range("A" & i)) - 3)
    Next i
    Application.ScreenUpdating = True
    MsgBox "completed"
    End Sub

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

Similar Threads

  1. Creating Proper format on Address Direction
    By wes9659 in forum Programming
    Replies: 11
    Last Post: 09-03-2014, 06:40 PM
  2. Please Point Me in Right Direction on Database Design
    By Hallway903 in forum Database Design
    Replies: 11
    Last Post: 07-26-2013, 01:45 PM
  3. Can anyone help point me in the right direction?
    By bhamhawker in forum Access
    Replies: 2
    Last Post: 11-04-2012, 08:06 AM
  4. Beginner needs a point in the right direction!
    By Gapco1 in forum Database Design
    Replies: 7
    Last Post: 07-30-2012, 07:13 PM
  5. Replies: 86
    Last Post: 10-07-2011, 06:06 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