How Can I Divide Large Excel Csv File And Stored In A Database Table?

Question

0.00/5 (No votes)

See more:

I have large excel csv file(about 2000 columns)I want to store these data into my Local service based data table. But local dB support only 1024 columns.so I have to divide that csv into several parts and stored in to the database. How can I divide and stored into multiple table? and I've to show the data together in db. How can I do all these things through c# codes?? please help me.

Posted 25-May-15 2:00am

Manidas Payyoli

Add a Solution

Comments

Sreekanth Mothukuru 25-May-15 9:12am

Try to store all your columns in Xml data type in Sql server instead of splitting into multiple tables.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Answer 1 · 2015-05-25T07:38:00

Solution 1

Follow below steps

1) Create two tables in SQL database.
2) Name them as "Tbl_Part_1" and "Tbl_Part_2"
Tbl_Part_1 will have 1023 columns as there in CSV and one extra column to store RowKey
Tbl_Part_2 will have 977 columns as there in CSV and one extra column to store RowKey
3) Read CSV file with all 2000 columns
4) Generate one Unicode key in C# and store it in some variable
5) pick up first 1023 columns and store it in Tbl_Part_1 with this unicode key
6) Store remaining 977 columns in second table and use the same unicode key

I hope this will help or else let me know if you need specific code

--
RDBurmon
Hope this helps if yes then please accept the answer and vote

Posted 25-May-15 7:38am

RDBurmon

Comments

[no name] 26-May-15 5:10am

could u suggest d coding pls?

RDBurmon 26-May-15 14:11pm

I have created the small project to demonstrate my suggestion. There is no option to attach file here so I have uploaded my project on this site -
Download the project and implement the same with your project. I have only considered 8 columns but you can implement the same with 2000 columns
Link 1 :
http://www.filedropper.com/994781how-can-i-divide-large-excel-csv-file-and-stored

If doesn't work then try this -
Link 2
http://s000.tinyupload.com/?file_id=75711810551485871114

[no name] 27-May-15 1:41am

oh thank you!!!!

RDBurmon 27-May-15 12:37pm

If this does work then accept the my answer as solution so that your case can be marked as closed

Jörgen Andersson · Answer 2 · 2015-05-25T09:02:00

There is another solution called "Wide Tables".

But it come with a couple of gotchas.
First the good new, you can have up to 30000 columns in a table.
But the bad news is that you're limited to 8019 bytes per row, which means that you need to nulls in many of the columns to be able to squeeze in all the data.
More info on msdn[^].

If this doesn't work for you, you need to use solution one.