Hello everyone and thanks for your help in advance. I am developing a web application that extracts a CSV file from an IOT device. The device has no built-in function to push data when a change to the CSV file occurs. So my thinking is to use some type
of timer function to poll the device periodically and pull the CSV file from the device. Not the most efficient, but the only game available. However, I need to develop a strategy to parse the CSV file to add the new data to a SQL database and I'm relly
not sure of the most efficient way to handle this. Is there some way to compare the previous and current CSV or do I have to pull everything into the database and go from there? Any insight would be appreciated.
It's difficult to answer this question as we have no idea how to determine new records or what fields make a record. I generally use a work table to load bulk data then use SQL to move the data to the final table.
There are literally dozens of packages written by people that you can choose from. Don't write your own, it is very unlikely that you will handle delimiters correctly. Make use of the hard work that someonelse has done.
As far as comparing between versions of the CSV file - I find it easiest to just keep the previous file on disk and compare the new one to it. Simple and effective. Read the new file into a string, read the saved file into a string. Use == to see if they
are the same...
Boy, is this a great answer! Thanks so much for the help. I didn't know about these libraries, but they look like a great solution. Love the solution of comparing the two files. Great idea. Thanks again.
Member
321 Points
1714 Posts
Working with CSV File for Web Application
Feb 20, 2019 10:57 PM|kmcnet|LINK
Hello everyone and thanks for your help in advance. I am developing a web application that extracts a CSV file from an IOT device. The device has no built-in function to push data when a change to the CSV file occurs. So my thinking is to use some type of timer function to poll the device periodically and pull the CSV file from the device. Not the most efficient, but the only game available. However, I need to develop a strategy to parse the CSV file to add the new data to a SQL database and I'm relly not sure of the most efficient way to handle this. Is there some way to compare the previous and current CSV or do I have to pull everything into the database and go from there? Any insight would be appreciated.
All-Star
53641 Points
23992 Posts
Re: Working with CSV File for Web Application
Feb 20, 2019 11:30 PM|mgebhard|LINK
It's difficult to answer this question as we have no idea how to determine new records or what fields make a record. I generally use a work table to load bulk data then use SQL to move the data to the final table.
Participant
1660 Points
952 Posts
Re: Working with CSV File for Web Application
Feb 21, 2019 09:55 PM|PaulTheSmith|LINK
I find that https://www.codeproject.com/Articles/25133/LINQ-to-CSV-library is an easy to use library that handles all my csv reading requirements.
CSVHelper on Nuget also seems to be very popular. https://www.nuget.org/packages/CsvHelper/ Get Started is at https://joshclose.github.io/CsvHelper/getting-started
There are literally dozens of packages written by people that you can choose from. Don't write your own, it is very unlikely that you will handle delimiters correctly. Make use of the hard work that someonelse has done.
As far as comparing between versions of the CSV file - I find it easiest to just keep the previous file on disk and compare the new one to it. Simple and effective. Read the new file into a string, read the saved file into a string. Use == to see if they are the same...
Member
321 Points
1714 Posts
Re: Working with CSV File for Web Application
Feb 21, 2019 11:32 PM|kmcnet|LINK
Boy, is this a great answer! Thanks so much for the help. I didn't know about these libraries, but they look like a great solution. Love the solution of comparing the two files. Great idea. Thanks again.