+ Reply to Thread
Results 1 to 7 of 7

Automate keying in a small amount of data from a large csv.file into an excel spreadsheet

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    manchester
    MS-Off Ver
    Excel 2007
    Posts
    6

    Automate keying in a small amount of data from a large csv.file into an excel spreadsheet

    Hi All,

    I am very new to VBA programming, and cabn see I will need to invest a lot of time to get to a decent level, but the first problem I have is sorting daily information into order.

    Basically every day we get a large csv file that has numerous columns, some have the information we need, many colums have information that is not used and just needs to be discarded. Next I need to choose the data that I do need and then flow it in data order so my spreadsheet looks like


    Data Y, Data A, Data B etc (ie data is not in alphabetical order but is in the order we need to see the information)

    23 Sep 2012
    24 Sep 2012
    etc

    Once I have this information I can save at least an hour of re-keying time per day.

    Can anyone help ?

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

    Re: Automate keying in a small amount of data from a large csv.file into an excel spreadsh

    You don't provide enough detailed information for us to help you. Provide a dummy sample CSV file and a sheet of the expected results.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    09-25-2012
    Location
    manchester
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Automate keying in a small amount of data from a large csv.file into an excel spreadsh

    Quote Originally Posted by Tinbendr View Post
    You don't provide enough detailed information for us to help you. Provide a dummy sample CSV file and a sheet of the expected results.
    # ----------------------------------------
    # PL
    # Location
    # 22266445
    # ----------------------------------------

    Country / Territory Visits Pages / Visit Avg. Visit Duration % New Visits Bounce Rate
    United Kingdom 17,952 1.5 00:16:23 22.37% 4.28%
    United States 4,263 1.32 00:12:36 23.08% 3.71%
    Canada 638 1.63 00:16:59 30.56% 5.02%
    Italy 416 1.42 00:13:01 18.99% 10.10%
    Australia 395 1.24 00:15:20 24.81% 4.30%
    Germany 260 1.31 00:08:03 21.54% 7.69%
    France 246 1.28 00:18:31 18.70% 7.32%
    Ireland 216 1.66 00:17:47 23.61% 6.02%
    Philippines 191 1.19 00:08:56 27.75% 22.51%
    Greece 167 1.2 00:14:12 19.16% 4.79%
    24,774 1.45 00:15:19 22.46% 4.88%

    Day Visits
    8/22/12 4,165
    8/22/12 3,954
    8/22/12 3,760
    8/22/12 3,622
    8/22/12 3,905
    8/22/12 4,250
    8/22/12 3,786
    27,442



    Expected Results - When re-keyed would look something like this



    Total USA UK Canada Czech Republic France Germany Greece Ireland Italy Poland Portugal Phillippines Australia Spain Turkey


    16/08/2012
    17/08/2012
    18/08/2012
    19/08/2012
    20/08/2012
    21/08/2012
    22/08/2012 24,744 4,263 17,952 638 - - 246 260 167 216 416 - - 191 395 - -

    the above was copied from an excel file does this help ?

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

    Re: Automate keying in a small amount of data from a large csv.file into an excel spreadsh

    OK a few more questions.

    Is the date taken from the Day visits data block?

    Do these numbers only occur once? In other words, you would only have one block of Country data per file. The next day would have another block?

    Can any/all of the countries listed in your output file exist in the data?

  5. #5
    Registered User
    Join Date
    09-25-2012
    Location
    manchester
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Automate keying in a small amount of data from a large csv.file into an excel spreadsh

    Hi David and Thanks for taking the time to look at this.

    The answer to your questions are :

    - Just to give you background we are looking at the data downloaded from Google Analytics as visitor data from countries, each day the countries may differ, on one day we may have visits and on other days no visits. We may well have a number of visits from small countries giving just a few visits. After we have populated our top 15 countries (that are all on fixed headers), we can move onto the next day, and so on. There is a lot of other data such as unique visits, time of visit etc. - we usually ignore this data, or discard it.


    The date is taken from the start of the report and it is in the form # 20120822-20120822 for the 22nd August 2012, not from the data block

    The numbers dont just occur once, you see a typical pattern emerge, but if you for example advertise in a new country for a few days, you would then expect to see numbers from that country increase, and so this is what you would measure on the Google Analytics report, and you can see then if your advertising spend is effective.

    We can choose which countries we want to summarise, and they will be keyed in Row 1 of the spreadsheet for each column from around column G onwards ie G1 USA, H1 UK, I1 Portugal etc.
    We can vary at anytime which countries we want to see summarised, and then we ignore the remainder of the data.

    I hope this clarifies, but if not please com back and I will try to help. I understand you are not supposed to download excel spreadsheets, but if this is not the case, I will send you an excel spreadsheet if this helps

    Regards

    NoBol

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,052

    Re: Automate keying in a small amount of data from a large csv.file into an excel spreadsh

    1. Record a macro that imports the .csv file and manipulates it.
    2. Post the sample file with a dozen or so rows.

    3. Post a workbook, so that no one has to create one from scratch by copying and pasting from this thread.
    --------------------
    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
    To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.


    On this page, below the message box, you will find a button labelled 'Manage Attachments'.
    Clicking this button will open a new window for uploading attachments.


    You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
    Alternatively you can click the Attachment Icon to open this page.


    To upload a file from your computer, click the 'Browse' button and locate the file.


    To upload a file from another URL, enter the full URL for the file in the second box on this page.
    Once you have completed one of the boxes, click 'Upload'.


    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Ben Van Johnson

  7. #7
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

    Re: Automate keying in a small amount of data from a large csv.file into an excel spreadsh

    OK Try this. It doesn't add the date or the total. You will have to add those manually.

    If this works for you, then we can address the date/total.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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