Community Database Project

B

billyc666

New member
RL Member
lol I thought the username in the directory name would of been a problem
I got this FileDir := "C:\Users\"A_UserName "\Google Drive\Csv Files" ; path of the folder containing the files
which works perfect .
does google drive have a revert changes like GitHub or any way I could prevent malicious inputting in the files?
 
djvj

djvj

Administrator
Staff member
Developer
Git can revert as far back as you want

Sent from my SM-N910V using Tapatalk
 
B

billyc666

New member
RL Member
is it possible to send files to a git folder on your computer and it automatically syncs to GitHub.. ive not quite got the hang of it yet lol
 
djvj

djvj

Administrator
Staff member
Developer
Not automatic, you want to use a syncing app for automation. Git is version control, so you have to perform a push when ready.
 
soft-bug

soft-bug

New member
RL Member
Hi Billyc666,

I think the tool available in this forum : HyperFreeSoft, could help you to grab existing informations from Game Info files. It automaticaly convets to Tsv file with the fields you want !

Best regards from Paris
 
B

billyc666

New member
RL Member
Thanks Soft-bug, I already got them tsv files from someone who used this app I think.

on a side note Capn Odin as made the scrape part for me , just a couple tweaks I need to do to suit the csv files, works like dustind's did download sitemap files which populates the second listview which then is used to scrape the information for the game,
also added a google search button on gui 1 which when clicked searches system name + game name

 
Last edited:
B

billyc666

New member
RL Member
This is the final ahk script, if anyone wants to make any improvements feel free to do so as I'm no coder
some test csv files can be grabbed from the first post of this thread, put the ahk inside a folder before using
run it then press F5 to download the moby scraper files wait a few minutes then run it again and they should show up in the right hand side lists.

seems to work well but I noticed mobygames sometime throws leavepage/stay on page box which throws an error that you need to click yes on script error to continue

Code: 
#NoEnv
SetBatchLines, -1
OnExit("KillIE")
 
; Dialog Box
Gui, Dialog:New, -SysMenu +AlwaysOnTop, Wrong Link
Gui, Dialog:Add, Combobox, vGameLink
Gui, Dialog:Add, Button, Default gDialogProceed, Continue
 
; Predefined values ----------------------------------------------------------------------------------------------------------------
GuiTitle := "Game Info Editor"         ; main Gui title
FileDir := A_ScriptDir . "\CSVFILES"   ; path of the folder containing the files
;FileDir := "C:\Users\"A_UserName "\Google Drive\Csv Files"   ; path of the folder containing the files for google drive
DataDir := ".\Data"
FileEncoding, UTF-8
Separator := ">"                       ; field separator
NumOfFields := 10                      ; number of fields in the game records
FieldNames  := ["Rom Name"             ; array of field names for the game record, adjust them to your needs
              , "Game Name"
              , "Year"
              , "Rating"
              , "Publisher"
              , "Developer"
              , "Genre"
              , "Score"
              , "Number Of Players"
              , "Description"]
FieldOptions := ["Disabled"            ; special Gui options for the edit fields, adjust them to your needs
               , ""
               , ""
               , ""
               , ""
               , ""
               , ""
               , ""
               , ""
               , "r10"
               , ""
               , ""]
 
LVW := 300                       ; width of the ListView controls
EDW := 500                       ; width of the Edit controls
 
CurrentFields := [], GameArray := {}
 
; Get the height of a single-line edit control -------------------------------------------------------------------------------------
Gui, New
Gui, Add, Edit, vDummy, Dummy
GuiControlGet, Dummy, Pos
EDH := DummyH
Gui, Destroy
 
; GUI ------------------------------------------------------------------------------------------------------------------------------
Gui, New, , %GuiTitle%
Gui, font, s13, Arial
Gui, Margin, 20, 10
 
; Create and populate the FileLV
Gui, Add, ListView, w%LVW% r10 Sort -Multi vFileLV gSubFileLV, File Name
Loop, Files, %FileDir%\*.csv
	LV_Add("", A_LoopFileName)
 
; Create the GameLV
Gui, Add, ListView, w%LVW% h300 r10 Sort -Multi vGameLV gSubGameLV AltSubmit, % FieldNames[1]
 
; Add the text and edit fields for the game records
TXY := "ym"
Loop, %NumOfFields% {
	Gui, Add, Text, %TXY% h%EDH% +0x200, % FieldNames[A_Index]
	Options := FieldOptions[A_Index] ; special field options
	Gui, Add, Edit, Section xp y+0 w%EDW% %Options% vED%A_Index% gSubEditChanged +ReadOnly
	TXY := "y+2"
}
 
; Create and populate the MobySysLV
Gui, Add, ListView, ym w%LVW% r10 Sort -Multi vMobySysLV gSubMobySysLV, System Name
Loop, Files, %DataDir%\*.xml
	LV_Add("", A_LoopFileName)
 
; Create the MobyGameLV
Gui, Add, ListView, w%LVW% r10 Sort -Multi vMobyGameLV gSubMobyGameLV AltSubmit, % FieldNames[1]
 
; Add the action buttons
BC := 4 ; button count
BW := 100 ; button width
DX := (EDW - (BW * BC)) // (BC - 1) ; space between butons
Gui, Add, Button, xs w%BW% vEditBtn gSubEditBtn, Edit
Gui, Add, Button, x+%DX% yp wp Disabled vSaveBtn gSubSaveBtn, Save
Gui, Add, Button, x+%DX% yp wp Disabled vCancelBtn gSubCancelBtn, Cancel
Gui, Add, Button, x+%DX% yp wp gSubSearch , Google
 
 
;------add background image here---------------------------------------------------------
; Adjust the height of GameLV
GuiControlGet, L, Pos, MobyGameLV
GuiControlGet, L, Pos, GameLV
GuiControlGet, B, Pos, CancelBtn
LVH := BH + BY - LY
GuiControl, Move, GameLV, h%LVH%
GuiControl, Move, MobyGameLV, h%LVH%
; Show the GUI
Gui, Show
Editing := False
 
Gui, Dialog:+Owner
 
Return
 
F5::Goto, SubScrapeBtn
 
GuiClose:
	ExitApp
 
 
; File LV label --------------------------------------------------------------------------------------------------------------------
SubFileLV:
	Gui, ListView, FileLV ; set the default ListView
	if(A_GuiEvent = "DoubleClick") && (A_EventInfo) && (LV_GetNext() = A_EventInfo)  { ; double-click on an item
		LV_GetText(CurrentFile, A_EventInfo)
 
		PopulateGameLV(CurrentFile)
 
		GuiControl, Focus, GameLV
		;LV_Modify(1, "Select")
	}
Return
 
PopulateFileLV(){
	Global
	Gui, ListView, FileLV
	LV_Delete()
	GuiControl, -ReDraw, FileLV ; stop redrawing
 
	Loop, Files, %FileDir%\*.csv
		LV_Add("", A_LoopFileName)
 
	Loop, 2 { ; adjust the column width
		LV_ModifyCol(A_Index, "AutoHdr")
	}
	GuiControl, +Redraw, FileLV ; redraw the ListView
}
 
PopulateGameLV(FileName){
	Global
	Gui, ListView, GameLV ; change the default ListView
	LV_Delete()
	GuiControl, -ReDraw, GameLV ; stop redrawing
	FileRead, CurrentContent, %FileDir%\%FileName%
	GameArray := []
	Loop, Parse, CurrentContent, `n, `r
	{
		If (A_LoopField) {
			Fields := StrSplit(A_LoopField, Separator)   ; creates a real array of fields (AHK arrays start with index 1)
			GameID := Fields[2]                          ; use the first field as the unique ID for the game
			LV_Add("", GameID)                           ; add GameID
			GameArray[GameID] := Fields                  ; use GameID as key for GameArray
		}
	}
	Loop, 2 { ; adjust the column width
		LV_ModifyCol(A_Index, "AutoHdr")
	}
	GuiControl, +Redraw, GameLV ; redraw the ListView
}
 
 
; Game LV label --------------------------------------------------------------------------------------------------------------------
SubGameLV:
	Gui, ListView, GameLV ; set the default ListView
	if(A_GuiEvent == "I")&& Instr(ErrorLevel, "S", 1) { ; a new item has been selected
		LV_GetText(CurrentGame, A_EventInfo)
		CurrentFields := GameArray[CurrentGame].Clone() ; retrieve the fields of the selected game from GameArray
		Loop, %NumOfFields% { ; update the edit fields
			GuiControl, , ED%A_Index%, % RegExReplace(CurrentFields[A_Index], "¤", "`n")
		}
	}
Return
 
 
SubMobySysLV:
	Gui, ListView, MobySysLV ; set the default ListView
	if(A_GuiEvent = "DoubleClick") && (A_EventInfo) { ; double-click on an item
		LV_GetText(MobyFileName, A_EventInfo)
 
		;SplitPath, MobyFileName, , , , FileName
 
		;PopulateGameLV(MobyFileName ".csv")
 
		Gui, ListView, MobyGameLV ; change the default ListView
		LV_Delete()
		GuiControl, -ReDraw, MobyGameLV ; stop redrawing
		FileRead, CurrentContent, %DataDir%\%MobyFileName%
		MobyArray := [] ; initialize a real array to hold the lines
 
		Loop, Parse, CurrentContent, `n, `r
		{
			RegExMatch(A_LoopField, "(?<=<loc>).*?(?=</loc>)", URL)
			if(URL) {
				SplitPath, URL, GameID
				GameID := Format("{:T}", RegExReplace(GameID, "(_|-)+", " "))
				LV_Add("", GameID)                          ; add GameID
				MobyArray[GameID] := URL                  	; use GameID as key for MobyArray
			}
	   }
	   Loop, 2 ; adjust the column width
			LV_ModifyCol(A_Index, "AutoHdr")
	   GuiControl, +Redraw, MobyGameLV ; redraw the ListView
	   GuiControl, Focus, MobyGameLV
	   ;LV_Modify(1, "Select")
	}
Return
 
 
; Game LV label --------------------------------------------------------------------------------------------------------------------
SubMobyGameLV:
	Gui, ListView, MobyGameLV ; set the default ListView
	if(A_GuiEvent = "DoubleClick") && (A_EventInfo) {  ; a new item has been selected
		LV_GetText(CurrentMobyGame, A_EventInfo)
 
		Navigate(MobyArray[CurrentMobyGame])
 
		Title := WB.document.getElementsByClassName("niceHeaderTitle")[0].getElementsByTagName("a")[0].innerText
 
		coreGameRelease := RegExReplace(WB.document.getElementById("coreGameRelease").innerText, "(`n|`r)+", "`n")
		coreGameGenre := RegExReplace(WB.document.getElementById("coreGameGenre").innerText, "(`n|`r)+", "`n")
		coreGameRank := RegExReplace(WB.document.getElementById("coreGameRank").innerText, "(`n|`r)+", "`n")
 
		RegExMatch(coreGameRelease, "(?<=Published by`n)[^`n]*", Publisher)
		RegExMatch(coreGameRelease, "(?<=Developed by`n)[^`n]*", Developer)
		RegExMatch(coreGameRelease, "(?<=Released`n)[^`n]*", Released)
 
		RegExMatch(coreGameGenre, "(?<=ESRB Rating`n)[^`n]*", Rating)
		RegExMatch(coreGameGenre, "(?<=Genre`n)[^`n]*", Genre)
 
		RegExMatch(coreGameRank, "\d*(?=`nCritic Score)", Critic)
		RegExMatch(coreGameRank, "(\d|\.)*(?=`nUser Score)", User)
 
		RegExMatch(WB.document.getElementsByClassName("col-md-8 col-lg-8")[0].innerText, "s)(?<=Description`r`n).*?(?=\s+\[edit description)", Description)
 
		Navigate(MobyArray[CurrentMobyGame] "/techinfo")
 
		techInfo := RegExReplace(WB.document.getElementsByClassName("techInfo")[0].innerText, "(`n|`r)+", "`n")
 
		RegExMatch(techInfo, "(?<=Number of Players Supported)[^`n`r]*", Players)
 
		GuiControl, , ED2, % Title ? Title : "..."
		GuiControl, , ED3, % Released ? Released : "..."
		GuiControl, , ED4, % Rating ? Rating : "Rating Pending"
		GuiControl, , ED5, % Publisher ? Publisher : "..."
		GuiControl, , ED6, % Developer ? Developer : "..."
		GuiControl, , ED7, % Genre ? Genre : "..."
		GuiControl, , ED8, % User ? User : "..."
		GuiControl, , ED9, % Players ? Players : "..."
		GuiControl, , ED10, % Description ? Description : "..."
		if(Title){
			;PopulateGameLV(MobyFileName ".csv")
			;CurrentFile := MobyFileName
			Goto, SubEditBtn
			Goto, SubEditChanged
		}
	}
Return
 
 
Navigate(url){
	Global WB
	if(!WB){
		WB := ComObjCreate("InternetExplorer.Application")
		WB.Visible := True
		;Sleep 100
		WB.Visible := False
	}
	WB.Navigate(url)
	While, WB.ReadyState != 4 ;Wait for page to load
		Continue
	if(WB.document.Title = "{gameTitle} for {gamePlatform} ({gameYear}) - MobyGames"){
		ComboDialog(url)
	}
}
 
ComboDialog(url){
	Global
	links := WB.document.getElementById("main").getElementsByTagName("a")
	list := ""
 
	loop, % links.length
	{
		RegExMatch(links[A_Index - 1].getAttribute("href"), "[^/]*$", game)
		list .= (A_Index = 2 ? "||" : "|") game
	}
 
	Gui, +Disabled
 
	GuiControl, Dialog:, GameLink, %list%
	Gui, Dialog:Show, AutoSize
 
	loop {
		GuiControlGet, Visible, Dialog:Visible, GameLink
		if(!Visible){
			Break
		}
		Sleep, 50
	}
 
	Gui, -Disabled
 
	SplitPath, url, , link
	MobyArray[CurrentMobyGame] := link "/" GameLink
	Navigate(link "/" GameLink)
}
 
DialogProceed:
	Gui, Dialog:Submit
	Gui, -Disabled
return
 
SubScrapeBtn:
	IfNotExist, %dataDir%
		FileCreateDir, %dataDir%
 
	UrlDownloadToFile, http://www.mobygames.com/images/sitemap/sitemap_index.xml, .\Data\sitemap.txt
 
	Loop, Read, .\Data\sitemap.txt
	{
		RegExMatch(A_LoopReadLine, "(?<=<loc>).*?(?=</loc>)", URL)
		SplitPath, URL, name
		name := RegExReplace(name, "(sitemap_|_games)")
		UrlDownloadToFile, %URL%, .\Data\%name%
	}
return
 
;Google Search button label --------------------------------------------------------------------------------------------------------
SubSearch:
	StringTrimRight, NameNoExt, CurrentFile, 4
	googleterms := NameNoExt . "+" . CurrentFields[2]
	run, http://www.google.com/search?q=%googleterms%
Return ; important to end the label!!!
 
; Edit button label ----------------------------------------------------------------------------------------------------------------
SubEditBtn:
	;GuiControl, Disable, FileLV
	GuiControl, Disable, GameLV
	GuiControl, Disable, EditBtn
	Loop, %NumOfFields%
		GuiControl, -ReadOnly, ED%A_Index%
	GuiControl, Enable, CancelBtn
	GuiControl, Focus, ED2
	Modified := False
	Editing := True
Return
 
; Save button label ----------------------------------------------------------------------------------------------------------------
SubSaveBtn:
	Gui, +OwnDialogs
	Editing := False
	SplitPath, CurrentFile, , , , FileName
	MsgBox, 4, %GuiTitle%, Save the changes to file %FileName%.csv ?
	IfMsgBox, Yes
	{
		Gui, Submit, NoHide
		Loop, %NumOfFields% { ; update the game record
			GuiControlGet, Value, , ED%A_Index%
			CurrentFields[A_Index] := RegExReplace(Value, "`n", "¤")
		}
 
		GameArray[CurrentGame ? CurrentGame : CurrentFields[2]] := CurrentFields.Clone()
 
		; To-do: save the file!
		NewContent := ""
		For Each, GameRecord In GameArray {
			Loop, %NumOfFields% {
				NewContent .= GameRecord[A_Index] . (A_Index = NumOfFields ? "`r`n" : Separator)
			}
		}
 
		if(HFILE := FileOpen(FileDir . "\" . FileName . ".csv", "w")) {
			HFILE.Write(NewContent)
			HFILE.Close()
 
		} else {
			MsgBox, 16, %GuiTitle%, Couldn't update file %FileName%.csv!`n`nError: %A_LastError%
		}
		PopulateFileLV()
		PopulateGameLV(FileName ".csv")
		CurrentGame := False
		NewContent := ""
	}
 
; Fall through to SubCancelBtn to do the rest
 
; Cancel button label --------------------------------------------------------------------------------------------------------------
SubCancelBtn:
	Editing := False
	GuiControl, Disable, CancelBtn
	GuiControl, Disable, SaveBtn
	Loop, %NumOfFields% {
		GuiControl, +ReadOnly, ED%A_Index%
	}
	GuiControl, Enable, EditBtn
	GuiControl, Enable, GameLV
	GuiControl, Enable, FileLV
	GuiControl, Focus, GameLV
	Gui, ListView, GameLV
	LV_Modify(LV_GetNext(), "Select") ; refill the game record fields
Return
 
; Edits label ----------------------------------------------------------------------------------------------------------------------
SubEditChanged:
	if(!Editing) { ; not in edit mode
		Return
	}
	if(!Modified) { ; first call while editing
		GuiControl, Enable, SaveBtn
	}
	Modified := True ; something in some of the edit control has been changed
Return
 
; ----------------------------------------------------------------------------------------------------------------------------------
CtlColorStatic(W, L) { ; changes the background of ReadOnly and disabled edits to white for better readability
	Static Init := OnMessage(0x0138, "CtlColorStatic") ; WM_CTLCOLORSTATIC = 0x0138
	Static DCBrush := DllCall("GetStockObject", "UInt", 18, "UPtr") ; DC_BRUSH = 18
	GuiControlGet, ControlName, Name, %L%
	If (SubStr(ControlName, 1, 2) = "ED") {
		DllCall("SetDCBrushColor", "Ptr", W, "UInt", 0xFFFFFF)
		Return DCBrush
	}
}
 
KillIE(){
	Global WB
	WB.Quit()
}
 
B

billyc666

New member
RL Member
Does anyone know how I can add a checkbox to these so I can disable them when I don't want the scraper to fill the fields
Code: 
		GuiControl, , ED2, % Title ? Title : "..."
		GuiControl, , ED3, % Released ? Released : "..."
		GuiControl, , ED4, % Rating ? Rating : "Rating Pending"
		GuiControl, , ED5, % Publisher ? Publisher : "..."
		GuiControl, , ED6, % Developer ? Developer : "..."
		GuiControl, , ED7, % Genre ? Genre : "..."
		GuiControl, , ED8, % User ? User : "..."
		GuiControl, , ED9, % Players ? Players : "..."
		GuiControl, , ED10, % Description ? Description : "..."
 
T

trnzaddict

Member
RL Member
Does anyone know how to do this bash script to convert the csv file to a hypertext xml file? I'm trying to work on Wonderswan and Wonderswan color.

So say if I have Bandai WonderSwan.csv

here is the bash script:

Code: 
#!/bin/bash
#csv xml function
convcsv2xml()
{
file_in="$1.tmp"
file_out="${1%%.*}.xml"
echo "System:""${1%%.*}"
file_in_csv="$1.tmp.csv"

cp "$1" "$file_in"
cp "$1" "$file_in_csv"
echo clean csv
#clean uo csv
#echo 4stars
#sed -i 's~\*\*\*\*~ ~g' "$file_in_csv"
# ** with space
#echo 2stars
#sed -i 's~\*\*~ ~g' "$file_in_csv"
# trailing spaces
echo trailing space
sed -i 's~ >~>~g' "$file_in_csv"
echo trailing space
sed -i 's~ >~>~g' "$file_in_csv"

echo clean xml
# & with &
echo amp
sed -i 's~\&~\&~g' "$file_in"
# ' with &apos;
echo apos
sed -i "s~'~\&apos;~g" "$file_in"
#  with 
echo quotes
sed -i 's~"~~g' "$file_in"
# <br><br> with space
#echo br br
#sed -i 's~\<br\>\<br\>~ ~g' "$file_in"
# <br> with space
#echo br
#sed -i 's~\<br\>~ ~g' "$file_in"
# **** with space
echo 4stars
sed -i 's~\*\*\*\*~ ~g' "$file_in"
# ** with space
echo 2stars
sed -i 's~\*\*~ ~g' "$file_in"
# trailing spaces
echo trailing space
sed -i 's~ >~>~g' "$file_in"
echo trailing space

echo convert csv to xml

echo '<?xml version="1.0"?>' > "$file_out"
echo '<menu>' >> "$file_out"
echo '  <header>' >> "$file_out"
echo '    <listname>'${1%%.*}'</listname>' >> "$file_out"
echo '    <lastlistupdate></lastlistupdate>' >> "$file_out"
echo '    <listversion>test</listversion>' >> "$file_out"
echo '    <exporterversion></exporterversion>' >> "$file_out"
echo '  </header>' >> "$file_out"

while IFS=$'>' read -r -a arry
do
  echo '  <game name="'${arry[0]}'">' >> "$file_out"
  echo '    <description>'${arry[1]}'</description>' >> "$file_out"
  echo '    <year>'${arry[2]}'</year>' >> "$file_out"
  echo '    <rating>'${arry[3]}'</rating>' >> "$file_out"
  echo '    <manufacturer>'${arry[4]}'</manufacturer>' >> "$file_out"
  echo '    <dev>'${arry[5]}'</dev>' >> "$file_out"
  echo '    <genre>'${arry[6]}'</genre>' >> "$file_out"
  echo '    <score>'${arry[7]}'</score>' >> "$file_out"
  echo '    <player>'${arry[8]}'</player>' >> "$file_out"
  echo '    <story>'${arry[9]}'</story>' >> "$file_out"
  echo '    <enabled>Yes</enabled>' >> "$file_out" #'${arry[10]}' just set to 'Yes'
  echo '    <crc>'${arry[11]}'</crc>' >> "$file_out"
  echo '    <cloneof>'${arry[12]}'</cloneof>' >> "$file_out"
  echo '  </game>' >> "$file_out"
done < "$file_in"
echo '</menu>' >> "$file_out"

rm "$file_in"
}

if [[ "$1" ]] ; then
    convcsv2xml "$1"
else
    for file in *.csv ; do
        convcsv2xml "$file"
    done
fi

It say to pass the filename of the csv to the script.....where exactly do I do that? Also how exactly do you execute this? I copied this into a text file then renamed it .bat and put it into the folder where the csv file is but it's not working. The instructions given on the Community Database Project website is very vague for this process.
 
You must log in or register to reply here.
Top