+ Reply to Thread
Results 1 to 6 of 6

Problem cleaning data for import

  1. #1
    Registered User
    Join Date
    06-23-2020
    Location
    Milton Keynes, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    3

    Wink Problem cleaning data for import

    Hi
    I am having a problem with a data import from CSV.

    The data is problematic and as such requires cleaning before it can be imported.

    Below is the section in my VBA that prepares the column for importing.


    Please Login or Register  to view this content.
    The data used to be OK but the app version column now brings down numeric values with multiple periods and "N/A". The upshot is that when I examine the sheet in Access - the app version column is blank.

    I guess I need to replace the N/A with a numerical value e.g. 0, but I am unsure how to force the remaining data to be numerical.

    Any advice much appreciated.

    Many thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    1,912

    Re: Problem cleaning data for import

    Welcome to the forum

    Attach a sample csv file but change the real data to false in advance to keep it confidential.
    Show what the expected result should look like.

  3. #3
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365
    Posts
    963

    Re: Problem cleaning data for import

    Hi aboutautism:

    What is the desired resulting format of col. G?
    For example, what should :
    1.00.0003
    2.3.7
    2.7
    505000
    227
    2.3.3
    look like?
    Take 2.3.3 for example/ What number is it trying to be? 2.33? 23.3? 233?

    Pete

  4. #4
    Registered User
    Join Date
    06-23-2020
    Location
    Milton Keynes, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    3

    Re: Problem cleaning data for import

    Hi Pete
    We are at the hands of Apple here.
    This is how they display the currently installed app version. To try to make sense of it is very difficult as there seems to be no standard with how app versions are submitted by app developers.

    In that instance I guess 2.3.3
    I see no benefit in considering beyond the first 5 digits (including periods).

  5. #5
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365
    Posts
    963

    Re: Problem cleaning data for import

    aboutautism:

    Looks to me like there's no benefit to formatting col. G to be numeric. Text will probably be fine for version numbers. A Find and Replace on N/A's to be zero's.

    You can then highlight your data in G, and multiply everything by 1. (Enter 1 somewhere, copy it, highlight your column, paste special, multiply.)

    HTH.

    Pete

  6. #6
    Registered User
    Join Date
    06-23-2020
    Location
    Milton Keynes, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    3

    Re: Problem cleaning data for import

    Thanks Pete
    That is good advice. I will sort that out tomorrow.

    Thanks again

    Martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Import data problem
    By Immerkaer in forum Excel General
    Replies: 7
    Last Post: 04-04-2019, 04:59 AM
  2. [SOLVED] Requesting assistance in cleaning up some text import VBA
    By #DIV/0! in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-18-2017, 04:11 AM
  3. Import data from the web, problem.
    By New2vbabutloveit in forum Excel General
    Replies: 0
    Last Post: 09-19-2014, 08:40 PM
  4. Cleaning Messy Data for Import
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2011, 12:14 PM
  5. Data import Problem..
    By marc5354 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2010, 10:06 PM
  6. Data import problem
    By Iain in forum Excel General
    Replies: 1
    Last Post: 05-02-2006, 09:15 PM
  7. Import Data Problem
    By Eamon in forum Excel General
    Replies: 1
    Last Post: 03-12-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1