Web CSV Search Methods

[Scarpozzi]

I'd like to setup a web page either through SharePoint or a local Linux server that can search a CSV file with about 30,000 rows.

Basically the file contains 2 columns of values. I need to search on one column to return the other value on the matching row. Simple, right?

What's the best way to make it happen without hitting java or security issues in the future?

 

[Fallen Kell]

I guess it depends on what you are attempting to accomplish. Are you performing multiple searches over the same CSV, different discrete searches on multiple CSVs, searching for partial results/patterns on a CSV, and/or expecting to return multiple matches? Will multiple people be searching different things on the same file at the same time, or would they be searching on their own separate files for different items? Do you want the computational burden for the search to occur on the end user's device/PC, or will the server be handling it?

Is there a reason it needs to be through a web interface, as that alone will be the root cause of security issues now and in the future....

 

[Scarpozzi]

I guess it depends on what you are attempting to accomplish. Are you performing multiple searches over the same CSV, different discrete searches on multiple CSVs, searching for partial results/patterns on a CSV, and/or expecting to return multiple matches? Will multiple people be searching different things on the same file at the same time, or would they be searching on their own separate files for different items? Do you want the computational burden for the search to occur on the end user's device/PC, or will the server be handling it?

Is there a reason it needs to be through a web interface, as that alone will be the root cause of security issues now and in the f

Maybe 1000 people might use the search tool....but it may be pretty sparse on usage, realistically. It's to lookup financial chart values compared to a 5 or 6 digit shortcut value on a legacy system. The data will mostly be static with potential for me to update the dataset by replacing the CSV file. The search results will be a 1 to 1.

I don't know how much processing power it will take or what kind of load it would be if more than one person searches at a time. 30,000 records may be a very high number since many of the shortcut values may be inactive and not required...I just counted rows in the table to get that estimate without validating the status. I could always have it loaded in a SQL server and go that route with php. I was just trying to keep it simple if someone has a way to do it.

 

[Ken g6]

This sounds like a job for vlookup in Google Sheets.

You really want it in Sharepoint? That might work too.

What do you mean by "security issues"? Are there security considerations around this data? Like data should only be output for a matching input? If so you'd likely need more security measures to prevent somebody brute-forcing the inputs.

 

[Scarpozzi]

This sounds like a job for vlookup in Google Sheets.

You really want it in Sharepoint? That might work too.

What do you mean by "security issues"? Are there security considerations around this data? Like data should only be output for a matching input? If so you'd likely need more security measures to prevent somebody brute-forcing the inputs.

I was trying to keep it all on the webpage without showing what's behind the search bar. Security was just a thought because it was reading a flat file and I'd prefer the data source be masked. SQL and php may be the way to go...I just don't have a SQL server for this, so it would take asking favors. (Which would take longer to get it running)

 

[Ken g6]

SQL and php may be the way to go...I just don't have a SQL server for this, so it would take asking favors. (Which would take longer to get it running)

SQL Server is overkill for this. SQLite (in which PHP would read from a file with no dedicated DB server) seems like overkill for this.

Assuming an uncomplicated CSV file, I feel like a CGI Bash script running grep would do nicely. But perhaps PHP is safer.

 

[igor_kavinski]

Extract data from CSV

Example of how to use the "Papa Parse" library to parse a CSV document in JavaScript and return the content of a particular column.

txtfiddle.com

I modified the code to return just the email address for the fella named Knox:

// Iterate over the parsed rows and collect the value from the "Email address" row
for (let row of parseResult.data) {
    if(row["Last name"] == "Knox")
        output.push(row["Email address"]);
}

And it works!

 

[igor_kavinski]

I was trying to keep it all on the webpage without showing what's behind the search bar.

You could modify the above code to run the entire thing from a web page without any PHP or DB requirements. The only consideration would be to prevent the web server from letting the users download the local CSV file if they try to be smart and point the URL towards the CSV file.

 

[lantis3]

https://lucaong.github.io/minisearch/ Javascript
https://github.com/teamtnt/tntsearch PHP
https://solr.apache.org/features.html JAVA