0

I'm struggling to figure out how to convert a CSV file into a database. I've tried a few methods here but I can't wrap my head around it. I have a CSV file with thousands of rows and I need to convert that into a SQLite database using C#. Any help is appreciated!

MeRC
  • 1
  • There's no correct answer for this one. Though I would recommend creating your database manually by looking at your CSV and the types of data it contains. Once that's done, write some code to load in the CSV and insert the data into the database. – KingOfArrows Apr 13 '20 at 04:22
  • @KingOfArrows That would be an easy solution, perhaps instead of fully converting the databse I could just ask the user to input the number of columns and the names of the columns, properties etc and then just load the data from the CSV? – MeRC Apr 13 '20 at 04:25
  • I can't give advice on whether you should get this info from the user or get the user to create the database for you. That is something for you to decide. – KingOfArrows Apr 13 '20 at 04:31
  • your welcome! please read this : https://stackoverflow.com/help/how-to-ask and get tour here : https://stackoverflow.com/tour then edit your question for give good answers and feedback – Amirhossein Apr 13 '20 at 05:37

1 Answers1

0

You can leverage MS Excel, open the CSV file and specify your character separator as needed (I believe it will default to tab limited). You can now save your thousands of rows as an Excel spreadsheet versus the character separated file (CSV) format.

Then you can leverage the open source OpenXML libraries to open the Excel document and work with it using object model. In an object oriented fashion, you can programatically create your new database using SQL statements.

Query for the spreadsheet headers to be used as your column names. Of course you'll need to ensure that your source CSV had provided appropriate headers. These can easily be added to the top of the large file if not.

E.g.

https://learn.microsoft.com/en-us/office/open-xml/how-to-get-a-column-heading-in-a-spreadsheet

Next, you simply iterate the rows and construct your SQL statement to insert the rows. You can review the Open XML docs, Microsoft docs, or existing StackOverflow docs for sample code on how this is easily done. How to read xslx with open XML SDK based on columns in each row in C#?

Warren Rox
  • 655
  • 2
  • 8
  • 23