0

How can we upload a CSV file through web(ASP.NET MVC C#) and mapped the column in CSV table to our tables in database

Example:

CSV File:
Username,Address,Roles,Date

How to add all the value in 'Username' column to User Table, Name Column ?

Value in 'Address' column to AddrDet Table, Address Column?

Value in 'Roles' column to RolesDet Table, Roles Column?

AND choose the CSV column to be added to database? (So not all column in CSV will be taken)

using ASP.NET MVC C#

Because all I know is when the CSV uploaded, it will create DataTable specially for CSV and all the column in CSV will be uploaded

Thank You

I'm using MVC and EF DB FIRST

newbs
  • 19
  • 1
  • 8
  • This topic is kind of broad, there are several steps: Create page for load the file into | Parse the CSV file into classes | Create/update database/tables based on the parsed data. – Tatranskymedved Apr 04 '17 at 09:38
  • Can you show the documentation about this topic ? – newbs Apr 04 '17 at 09:40
  • Possible Duplicate: http://stackoverflow.com/questions/20759302/upload-csv-file-to-sql-server – Dave Becker Apr 04 '17 at 09:43
  • http://stackoverflow.com/questions/5193842/file-upload-asp-net-mvc-3-0 | http://stackoverflow.com/questions/7702573/importing-csv-data-into-c-sharp-classes | communication with SQL server is whole topic for itself, try google: "Entity framework" or "C# communication with SQL server" – Tatranskymedved Apr 04 '17 at 09:43
  • 1
    Possible duplicate of [Upload CSV file to SQL server](http://stackoverflow.com/questions/20759302/upload-csv-file-to-sql-server) – Tetsuya Yamamoto Apr 04 '17 at 09:45
  • You have to realized, that programs normally work in this way: Database already exists, got some schema (this and that table with these columns) and You just work with data. You can create/read/update/delete (CRUD) data. Import is normally done just once during database creation and the CSV is directly inserted via SQL manager studio (for MS-SQL database) – Tatranskymedved Apr 04 '17 at 09:45

1 Answers1

3

This questions is being marked as duplicate of Upload CSV file to SQL server

I don't feel (& don't think) that the question is related to or has completely same topic, so I'm answering this. I have myself marked question as too broad, as there is too much to explain.

Also I will add some links to the question, however they are not here to fill the answer, only to give OP an idea what question/topics to look for himself.

Short explanation:

Usually when You want to import data (CSV file) into database, You already got structure & schema of data (and Database). There is existing TableA and TableB, where exist some columns inside. If You want to dynamically create new columns/update schema of DB based on CSV file, this is an uneasy work (normally is not happening).

C#/ASP/.NET application is working in a way where You give it an input (from users' clicks, data load, task scheduler passed some time checkpoint) and the APP do the work.

Typical job looks like: "We got data in this format, APP have to convert them to the inner representation (classes) and then insert them to the server". So You have to write an ASP page, where You allow user to paste/load the file. E.g.: File Upload ASP.NET MVC 3.0

Once You have loaded the file, You need to convert the CSV format (format of stored data) into Your internal representation. Which means create Your own class, with some properties and convert (Transform) CSV into the classes. E.g.: Importing CSV data into C# classes

Since You have this data inside classes (objects - instances of classes), You can work with them and carry out some internal work. This time we are looking for CRUD (Create/Read/Update/Delete) operations against SQL database. First You need to connect to SQL server, choose database and then run the queries. E.g.: https://www.codeproject.com/Articles/837599/Using-Csharp-to-connect-to-and-query-from-a-SQL-da

Plenty of developers are too lazy to write the queries themselves and they like more Object-Oriented access to this sort of problem. They are using ORM - Object-relation mapping, which allows users to have same class/object schema in Database and in the Application. One example for all is Entity-Framework (EF). E.g.: http://www.entityframeworktutorial.net/

As You can see this topic is not so easy and requires knowledge in several parts of programming.

learner_01
  • 21
  • 10
Tatranskymedved
  • 4,194
  • 3
  • 21
  • 47
  • Thank you Sir! I already know how to connect ASP to DB, File Upload, I just dont understand how to mapped csv column to column in my DB and not use all the column in CSV Haha :D – newbs Apr 05 '17 at 01:59
  • @newbs In that case Your question should be more specific -> "How to parse CSV file excluding some columns?" I got `column1;column2;column3` and I want to have `Column1` in `classA`. =) – Tatranskymedved Apr 05 '17 at 06:02
  • Yes something like that haha – newbs Apr 05 '17 at 06:25