No announcement yet.

CSV - How to grab data based on a variable.

  • Filter
  • Time
  • Show
Clear All
new posts

  • CSV - How to grab data based on a variable.

    Good Day;

    I'm trying to design a small application for myself to keep track of maintenance for my trucks and other details regards to them.

    I'd like to enter a truck number, and then hit submit(xbutton) then have input boxes autofill with data that is stored in a CSV file.
    -- Gets the truck number from the input
    datecompleted = Table.Concat("DB\\TRUCKNUMBER.txt", ",", 1, 1);
    -- Grab data from csv file, however I don't know how to choose the .txt file based on the truck number.
    -- IF i entered 22, it should compute out to 22.txt
    Input.SetText("mileagecheck_date_completed_text", datecompleted);
    -- Set the text based on the data in the CSV file that was found earlier.
    How can I resolve this issue?


  • #2
    Hi welcome to forum
    It would be much easier if you posted a sample apz with csv file included. Is the csv file generated from an Microsoft excel database / file?
    From your small bit of script you have NO paths to get data from - You MUST read the manual on how to retrieve information from a file!!
    CVS file is not same as text file one is .cvs the other .txt
    Try to post your code in tags see pics
    Click image for larger version

Name:	adv.png
Views:	233
Size:	2.9 KB
ID:	299980 Click image for larger version

Name:	tags.png
Views:	188
Size:	2.6 KB
ID:	299979


    • #3
      TRUCKNUMBER = Input.GetText("Input1");
      -- Puts the truck number entered into a variable
      -- What I would like it to do next is to basically read the multiple text files depending on where they go.
      -- For Instance, AutoPlay\\TRUCKNUMBER\\OUT-RM.csv, Would like it to grab the date and input it to the box accordingly
      DATECOMPLETED = Table.Concat("AutoPlay\\TRACTOR\\TRUCKNUMBER\\OUT-RM.csv", ",", 6, 6);
      Input.SetText("Input2", DATECOMPLETED);
      MILESOMPLETED = Table.Concat("AutoPlay\\TRACTOR\\TRUCKNUMBER\\OUT-RM.csv", ",", 7, 7);
      Input.SetText("Input3", MILESCOMPLETED);
      DESCRIPTIONOMPLETED = Table.Concat("AutoPlay\\TRACTOR\\TRUCKNUMBER\\OUT-RM.csv", ",", 8, 8);
      Input.SetText("Input4", DESCRIPTIONCOMPLETED);
      COMPANYOMPLETED = Table.Concat("AutoPlay\\TRACTOR\\TRUCKNUMBER\\OUT-RM.csv", ",", 8, 8);
      Input.SetText("Input5", COMPANYOMPLETED);

      Thanks for the speedy response.
      Attached Files


      • #4
        Outside R&M (Non-Warranty),
        12/10,"3,500",3 axle alignement,commercial tire,225.00,
        I forgot the CSV file that Excel created.



        • #5
          Hi again,
          Two main questions -
          1) Are you inputting the data for trucks into an excel spreadsheet manually into cells or with a VB script userform?

          2) If you require an external app with AMS could you input all data into your app or do you have to import?

          If you are using excel then the whole process can be done by using VBA and using a spreadsheet as the database
          If you only want to use AMS I would suggest using SQLite /3 as a database to maintain your records

          You could also convert cvs to sqlite and use AMS, but just reading from cvs to table then entering data into input boxes
          is a inefficient way of getting correct and updated data
          If you want to design an application in AMS for keeping track of trucks then use an input page - save records to data base -
          use buttons to retrieve from database to view / edit etc
          It is better to use one application for this ie: MS excel or AMS


          • #6
            1. I'm actually trying to set up AMS to be the only application that I use, I just have to import the records
            I currently have with Excel. I wouldn't mind starting fresh and inputting all the records manually. As long, as
            it works great.
            2. I don't use anything other apps.

            I figure CSV would be the best as I could at the end of the year print out the records that are stored.



            • #7
              It should be said here that what you're asking is not as simple as it may seem, and that you need to read up on the use of Tables. That being said, here's an example of how to import a CSV file via table into an Input Object and then export that table back into an Excel file.

              Note: The 'export to Excel' function requires the Luacom Plugin.
              Credit: Modded from S0mbre's CSV Parser at:

              Nb. Follow up by reading the thread at above link and studying the examples. S0mbre's GDP-World.APZ is excellent. He developed these in v7 so all code using 'for loops' has now been deprecated and will need to be changed to the new syntax if using in v8.
              Attached Files