Import .csv data into web page

[jer456]

Hi,

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

Is it possible to create variables which i plonk 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 web page 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 web page. Then i can save the web page.

[Mofi]

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.

[jer456]

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.

[Mofi]

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.

[jer456]

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 (ive 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....

[Mofi]

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).

[jer456]

Thanks for explaining the differences Mofi, it helps me understand your code better. And i might need to use RegExs 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. 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!