Import .csv data into webpage

Import .csv data into webpage

6
NewbieNewbie
6

    Aug 12, 2008#1

    Hi,

    I have a .csv file with tons of data in it. And i need to get all that data into my static webpages.

    Is it possible to create variables which I put into my .html file and then be able to get UE to insert the data from the CSV file into those variables on the page?

    For example, lets say there's a column called "Name" and another called "Age"

    I want to be able to create two variables called $Name and $Age and put them into my webpage page template. Then i want to be able to select a row in my CSV file, press play, and have UltraEdit pull the data from the Name field into the $Name variable and pull the data from the Age field into the $Age variable in my webpage. Then i can save the webpage.

    6,686585
    Grand MasterGrand Master
    6,686585

      Aug 12, 2008#2

      Yes, with a small macro or script it is no problem to get hundreds or thousands of static HTML files all based on the same template with variables (placeholders) for data which are replaced by the macro/script with data from a CSV file before saving the HTML file with an maybe also automatically created file name. Well, the macro/script could do also only what you want. But I think, it would be better that the macro opens the template file, the CSV file and then creates automatically for every data line in the CSV file a HTML file based on the template with a file name based also on the data in the CSV or an automatically increased number.
      Best regards from an UC/UE/UES for Windows user from Austria

      6
      NewbieNewbie
      6

        Aug 13, 2008#3

        Mofi wrote:But I think, it would be better that the macro opens the template file, the CSV file and then creates automatically for every data line in the CSV file a HTML file based on the template with a file name based also on the data in the CSV or an automatically increased number.
        Yes this is what i want to do, if it can be done.

        Here are the files. (These examples are exactly the same as the real files but i have removed some data from the .csv.)

        The first row of the .csv contains headings. You may remove these if you wish.

        Open the .php file and you will see a placeholder which corresponds with each heading in the .csv file. For example: the "File name" heading has the $filename placeholder in the .php file.

        Like you already know i want to replace the data in the .csv file with the placeholders i have put into the PHP file.

        Like you suggested it should open the csv file then open the template PHP file and create the webpage and save it with the filename from the CSV file then insert all the data.

        Thanks Mofi for looking.
        files.zip (1.6 KiB)   381

        6,686585
        Grand MasterGrand Master
        6,686585

          Aug 13, 2008#4

          I'm better in writing macros than scripts, so here is the macro solution. You have to adapt the 3 paths according to your local requirements before you can use the macro. Short description:

          The macro first opens the template file, copies whole content into user clipboard 9 and closes it immediately.

          Next it opens the CSV file, verifies the existence of a line termination at end of last line and sets the cursor to start of line 2 in the file - header line is ignored.

          The loop runs now until the end of the CSV file is reached.

          First the current line in the CSV file is selected, copied into user clipboard 8 and unselected.

          Second a new file is opened and the line from the CSV is pasted. Next the template file content is pasted from user clipboard 9 below the line from the CSV.

          Now the macro replaces the variables with the data from the CSV line. It uses always the same method: select with a simple regular expression the next data and replace all occurences of the next variable with the selected text. It is very important that the number of data per line in the CSV file is always correct. The macro makes no checks here.

          After replacing all variables with the data, the first CSV column - the file name - is reselected again and copied to clipboard 8. Then the line from the CSV file is deleted.

          Last the new file is saved with that file name in the directory you have to specify and with the file extension PHP. That's it.

          InsertMode
          ColumnModeOff
          HexOff
          UnixReOff
          Open "path\_breed-template.php"
          SelectAll
          Clipboard 9
          Copy
          CloseFile NoSave
          Open "path\database.csv"
          Bottom
          IfColNumGt 1
          InsertLine
          EndIf
          Top
          Key DOWN ARROW
          Clipboard 8
          Loop
          IfEof
          ExitLoop
          EndIf
          SelectLine
          Copy
          EndSelect
          Key HOME
          NewFile
          Paste
          Clipboard 9
          Paste
          Top
          Find RegExp "[~,]+"
          Find MatchCase "$filename"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$breed"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$size"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$lifeexp"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$popularity"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$akcgroup"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$shedding"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$grooming"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$training"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$energy"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$affection"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$playfulness"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$watchdog"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~,]+"
          Find MatchCase "$guarddog"
          Replace All "^s"
          EndSelect
          Key RIGHT ARROW
          Find RegExp "[~^r^n]+"
          Find MatchCase "$sociability"
          Replace All "^s"
          EndSelect
          Top
          Clipboard 8
          Find RegExp "[~,]+"
          Copy
          DeleteLine
          SaveAs "path\^c.php"
          CloseFile NoSave
          EndLoop
          ClearClipboard
          Clipboard 9
          ClearClipboard
          Clipboard 0

          Add UnixReOn or PerlReOn (v12+ of UE) at the end of the macro if you do not use UltraEdit style regular expressions by default - see search configuration. Macro command UnixReOff sets the regular expression option to UltraEdit style.
          Best regards from an UC/UE/UES for Windows user from Austria

          6
          NewbieNewbie
          6

            Aug 13, 2008#5

            Thanks very much Mofi!!! Are you paid to contribute to this forum?

            But I actually wrote my own macro while i was waiting for a reply. It will be interesting to compare my macro code to yours. And mine doesn't use regular expressions. I'm a complete noob (I've never programmed or written a macro before) but I managed to do it. If you see anything wrong with my code please let me know:

            It works perfectly but it is different to yours. Is that okay? Is it just that there are many ways to write a macro? And we both just wrote it differently? Is your code more efficient?

            Code: Select all

            InsertMode
            ColumnModeOff
            HexOff
            UnixReOff
            Key Ctrl+HOME
            Loop 5
            Open "_breed-template.php"
            PreviousDocument
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$filename"
            Replace All "^c"
            SaveAs "^c.php"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$breedname"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$facts"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$size"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$lifeexp"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$popularity"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$akcgroup"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$shedding"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$grooming"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$easeoftrain"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$energy"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$affection"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$playful"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$watchdog"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            NextDocument
            Find MatchWord "$guarddog"
            Replace All "^c"
            PreviousDocument
            Key RIGHT ARROW
            StartSelect
            Find Select ","
            Key LEFT ARROW
            Copy 
            EndSelect
            Key HOME
            Key DOWN ARROW
            NextDocument
            Find MatchWord "$sociability"
            Replace All "^c"
            CloseFile Save
            EndLoop
            ExitLoop
            
            What do you think of my code? Any obvious bugs in it? It works for me....

            6,686585
            Grand MasterGrand Master
            6,686585

              Aug 13, 2008#6

              Your macro version is quite okay. I have only doubts that it works for the last data $sociability because

              StartSelect
              Find Select ","
              Key LEFT ARROW
              Copy
              EndSelect
              Key HOME
              Key DOWN ARROW
              NextDocument
              Find MatchWord "$sociability"


              selects from current cursor position to next comma, but there is no comma at the end of a CSV line, at least in the version you have uploaded. So this last find will select till first comma in the next line, if there is a further line and so $sociability is replaced with the wrong string. That's the reason why I have slightly changed in my macro the last regex find to find all characters except CR and LF instead of all characters except a comma as in the previous finds.

              Using the find select method instead of a regular expression to select the next data is quite good. The regular expression has just the advantage to need 1 cursor movement fewer than the find select method which increases speed a little. But maybe the simple find for a comma makes your find faster than my regex find.
              Horrible for the macro speed is in your macro the document switching because every switch forces a total UE window redraw. That's the reason why I avoid document/window switching during macro execution as much as possible.
              Also in my macro I use the selected text as replace string where you must copy the text to the clipboard for usage in the replace. That makes the macro also a little bit slower because a clipboard data replacement needs a little bit more time than just loading a string into a variable.
              But your macro is really quite good for a beginner.

              Note: If you decide now to use my macro, you have to adapt it a little bit because I see on your macro code that you have some variable names changed (like $breed - $breedname) and additional variables (like $facts).
              Best regards from an UC/UE/UES for Windows user from Austria

              6
              NewbieNewbie
              6

                Aug 13, 2008#7

                Thanks for explaining the differences Mofi, it helps me understand your code better. And I might need to use regular expressions in a future macro so I can refer to your code.

                You're correct about the comma not being added on the end. But what I did is I opened the CSV file and copy and pasted the text "end" onto the end of each row. This forces a comma onto the end of every row. It was quicker for me to do this then figure out how to write macro code to handle it - like you did.  :D But it still works fine for me nonetheless.

                Thanks again! And I LOVE UltraEdit. I never knew it was so powerful when I purchased it. But UltraEdit is one of the best purchases I have ever made. This macro will save me HOURS of time!