1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Editing downloaded data

Discussion in 'Business Applications' started by ImaGrump, Feb 5, 2019.

Thread Status:
Not open for further replies.
Advertisement
  1. ImaGrump

    ImaGrump Thread Starter

    Joined:
    Nov 14, 2017
    Messages:
    122
    Tech Support Guy System Info Utility version 1.0.0.4
    OS Version: Microsoft Windows 10 Home, 64 bit
    Processor: Intel(R) Core(TM) i3-3227U CPU @ 1.90GHz, Intel64 Family 6 Model 58 Stepping 9
    Processor Count: 4
    RAM: 3977 Mb
    Graphics Card: Intel(R) HD Graphics 4000, -2043 Mb
    Hard Drives: C: 454 GB (402 GB Free);
    Motherboard: Dell Inc., 033MX4
    Antivirus: Windows Defender, Enabled and Updated
    > I added this: I am using Windows Office Pro 2016
    Am I going to have to do this each time I post?

    I download data from Brisnet daily which I have to edit in order to use it. Since I started this, I have been doing it manually which is too time consuming. I am here to see if it can be automated and,if so, how?

    Here are 2 files on Notepad. "Unedited" is as it downloads and "HOU" is after editing. I do this to put it into ACCESS and query for results. Now, if someone shows me how I can download the data directly into ACCESS as a usable table, I will name my next child for him/her! Automating this process will save plenty of time, so let us not explore that right now.

    If you need additional info, ask.
     

    Attached Files:

  2. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    1,046
    First Name:
    Morten
    I'll look at it later today. It shouldn't be too hard to make some automation.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If Fireflycph does not find the time or has problems let me know.
    I have specialised in Importing files directly in to Access using VBA and the original BASIC file input.

    ps I have looked at the data and it look fairly straight forward to import using VBA.

    pps I have the code that you need for finding the horse name but I will need the Table name to put them in.
     
    Last edited: Feb 6, 2019
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Here is the code to place the data in to a table called "Horse Imports" with the fields "Import Number" and "Horse Name".


    Code:
        Dim strFile As String
        Dim strInput As String
        Dim varSplit As Variant
        Dim intCount As Integer
        Dim rs As Object
       ' CHANGE THE TABLE NAME HERE
       Set rs = CurrentDb.OpenRecordset("Horse Imports")
       ' CHANGE THE TEXT FILE NAME AND LOCATION HERE
       strFile = "C:\Users\A C\Downloads\UNEDITED HOU ENTRIES 2019 0206.txt"
        Open strFile For Input As #1
        Dim i As Integer
        Dim n As Long
        intCount = 0
        n = 0
        i = 100
        Do Until EOF(1)
           ' This counter is just to get to the applicable line before importing
           intCount = intCount + 1
           ' reads the text file line by line
           Line Input #1, strInput
          
           ' starts importing on the 6th line.  Change the number to match which line you
           ' want to start importing from
           If intCount >= 6 Then
               ' creates a single dimension array using the split function
                varSplit = Split(strInput, vbTab, , vbBinaryCompare)
                'MsgBox varSplit(0)
                If Left(varSplit(0), 13) = "Scratch Watch" Then Exit Do
                If varSplit(0) = "pp" Then
                    i = i + 100
                    n = 0
                End If
                If IsNumeric(varSplit(0)) Or IsNumeric(Left(varSplit(0), Len(varSplit(0)) - 1)) Then
                    n = n + 1
                    'MsgBox "found " & n & " horse " & varSplit(1)
               ' adds the record
                    With rs
                         .AddNew
                         ![Import Number] = i + n
                        ![Horse Name] = varSplit(1)
                         .Update
                    End With
                End If
           End If
       Loop
       ' garbage collection
       Close #1
       rs.Close
       Set rs = Nothing
    
    
    MsgBox "Successful file import.", vbInformation
    
     
  5. ImaGrump

    ImaGrump Thread Starter

    Joined:
    Nov 14, 2017
    Messages:
    122
    How do I respond to OB? Your icon was placed here for a response. Does it make a difference to whom I respond, or do I just do it in the provided space?
    Anyway......to OB: Can you lay out the steps for me to do this? I have only used one vbs code in my life, so I am totally ignorant about them; ( that is why I do all my work manually).
    My heartfelt thanks to both of you for your prompt replies and your willingnes to help!!!
     
  6. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    1,046
    First Name:
    Morten
    I did the import and saved the steps. It did run in to some issues as, at the end, it looks like that's a list of Scratched horses. But otherwise just do a "Fixed Width Import" where the first limit is after character 3 and the second after character 19. The rest you just ignore as in checking "Do not Import"
    Check advanced and create Specs which you can use every time. I do suspect that some massaging will have to be done afterwards as the data is still raw. Write a query to not show fields where the name is empty for instance. Hide duplicates, if you so desire etc.

    But maybe OPB's VB solution works for you. In that case that's great.
     
  7. ImaGrump

    ImaGrump Thread Starter

    Joined:
    Nov 14, 2017
    Messages:
    122
    Excuse my ignorance, but I have only a very vague idea of what you are telling me what to do. I have extremely limited knowledge of Excel and considerably less of Access. No local help is available to me so I learn it all on my own. Hence I do what I know how to do, regardless of how " 'round about " it may be.
    I learned what I could about Access about a year and a half ago and started using what I could for what I do, so I am probably using less than 2% of what Access is capable of.
    This forum is my only means of acquiring assistance but my knowledge is so limited that I have to be treated like a first grader.......I don't mind! I thank you with all my heart!!!!
     
  8. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    1,046
    First Name:
    Morten
    It would help if we knew exactly what you're looking for. In the text file you have many horses, many "bib" numbers and some who have been scratched. It's impossible to create anything at all without knowing what you're looking for. Look at the text file, unedited, and you'll see 1'st, 2'nd etc race. But some horses have "2A" etc. So we can't look ignore the 2'nd character. At the end it seems you have horses that have been scratched. Are they to be included? Should horses that appear more than once have both entries included?

    As I said. Without knowing exactly what you want, within whatever parameters, it's impossible to make an import and queries to exclude certain records.

    Sorry. I thought it'd be easy. And, importing everything is easy. But you need to determine which records you need and which you don't.
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Fireflycph, as you say a "simple import" it is not, plus there is the requirement to number the horses for each race, the VBA does all of that.
    Imagrump, the VBA runs from a Button on a Form.
    I have attached a zipped copy of the database so that you can run it and see the result for yourself.
    You can Import the form in to your database and either import and use the table as well or change the name to your table.

    ps let me know if you need more help with the Database design.
     

    Attached Files:

  10. ImaGrump

    ImaGrump Thread Starter

    Joined:
    Nov 14, 2017
    Messages:
    122
    What I am doing is downloading "unedited" as a text file in notepad. I copy this into an OO calc spreadsheet (I realize that I should download directly into the SS, but that's something to work on later). I then delete everything but the PPP numbers and the Horse Names which leaves me with 2 columns only. I copy this back into the original notepad.

    Now, when I say "delete everything", I mean non numbers in the PPP column and all punctuation marks in the Horse column and everything below the last race. "Unedited" has been turned into "HOU".

    Next I open Access, click External Data, browse to "HOU", import it as a table, and query this table and the basic database table to acquire the history of the horses running these races. This I modify and put into a template to complete a "Program" ss.

    This is what I do manually with a lot of cutting/copying and pasting. It is time consuming and I want to accelerate it, if possible.

    THANKS
     
  11. ImaGrump

    ImaGrump Thread Starter

    Joined:
    Nov 14, 2017
    Messages:
    122
    OB
    This is for you. I am attaching a folder that has the only VBS code that I have ever used with a few explanations. Draceplace of this forum provided it to me last year. He had to explain a lot to me because I knew nothing about using vbs. I still know very little other than being able to make this work.

    I believe that I need to make input and output folders and specify their locations to get this going, but as you can tell....
    that's about all I know. How to execute is an unknown.

    Again, I want to thank you and IM for your attempts to help......I really appreciate it!

    One more time I am falling short! I am unable to upload the folder and I've got to leave right now. I will return.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    As I said you should be able to use what I provided as it is, other than changing the location of the import Folder.
    The only time you should need to do anything is if the format of the file you are importing changes.
    You do not need input and output folders, you just need to ensure that the import file is in the same folder each time so that the VBA can find it.
    If necessary I can provide the VBA to find the file.
     
  13. ImaGrump

    ImaGrump Thread Starter

    Joined:
    Nov 14, 2017
    Messages:
    122
    OB:
    YOU still have way too much confidence in my abilities!
    Let us try a new approach. When I go to the Brisnet web page and access the desired entries, I copy the data to Notepad and bring up "save as". I click that and save to my folder as: 'HOU ENTRIES 2019 0201'. From there I do the steps outlined in the previous post.
    Once I am at the web page, what do I do? Do I copy to Notepad and then save someplace or what?
    With the other vba, once I have the data I "save as' (in csv) into the folder containing the vbs code. It saves it in that folder, I run the vbs which converts it and puts it into the output folder, and then I rename it and put it where I need it.
    How do I use your vba code?
    One question occurred to me as I was writing this......he gave me a vbs executable; is yours?
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    No it is not VBS it is Access VBA.
    I do not know what his VBS does, so I can't help with that side of it.
    But what you would with my Access database is copy the Bisnet data in to Notepad and save it as a csv file without making any changes to the data.
    You can save the file in a folder, but my VBA code must know the complete file path to the data.
    Open Access and click the button which will import the data you want as you want it.
    I can add a browser to the code so that you browse to the file to import it.
     
  15. ImaGrump

    ImaGrump Thread Starter

    Joined:
    Nov 14, 2017
    Messages:
    122
    Check this out......it may help.
    Copy, paste, and/or delete if match Thread from last year.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1222754

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice