how to divide a table and store csv file

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. my code is here and it doesn't work

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using System.IO;
using System.Data.SqlClient;

namespace table_divide
{
    public partial class MainWindow : Window
    {

        public MainWindow()
        {
            InitializeComponent();
        }
        DataTable dt = new DataTable();
        string file_path = @"D:\Crysis3_25x16_VeryHigh-TotalFile\Crysis3_25x16_VeryHigh_dcc_off_Perf\Crysis3_25x16_VeryHigh_dcc_off_Perf.csv";
        int count = 0;
        int count2=0;
        int row = 0;  
        string[] table_data;
        string sql = string.Empty;
        string table_name;


        private void Button_Click(object sender, RoutedEventArgs e)
        {
           StreamReader readfile = new StreamReader(file_path);

           {
               string line;
               string tabname;
               while((line=readfile.ReadLine())!=null&& count<10)
               {

                   if (count2 == 100)
                       break;
                   table_data = line.Split("\r\n".ToCharArray(),StringSplitOptions.RemoveEmptyEntries );
                   if(count>0)
                   {

                       (from st in table_data.Skip(0) select dt.Rows.Add(st.Split(",".ToCharArray()))).ToList();

                   }
                        

                   else if(count==0)
                   {

                       var col = from cl in table_data[0].Split(",".ToCharArray()) select new DataColumn(cl.Trim().ToUpper().Replace(" ", ""));
                       dt.Columns.AddRange(col.ToArray());
                       create_table(table_name);
                   }
                   count++;
                   count2++;

                   if (count == 10)
                   {
                       insert_data(table_name); 
                       count = 1;
                       dt.Rows.Clear();
                   }

               }

               insert_data(table_name);
               dt.Rows.Clear();

           }

           MessageBox.Show("inserted");

        }

        private void create_table(string table_name)
        {
            try
            {
                SqlCommand cmd = null;
                using (SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\MyPractice\table_divide\table_divide\Database2.mdf;Integrated Security=True"))
                {
                    con.Open();

                    if (count == 0)
                    {
                        sql = "CREATE TABLE " + System.IO.Path.GetFileNameWithoutExtension(file_path) + "1  ( DRAWID INTEGER PRIMARY KEY AUTOINCREMENT,  ";
                        for (int i = 0; i < 1000; i++)
                        {

                            sql += dt.Columns[i].ToString() + " INTEGER,";

                        }

                        sql = sql.Substring(0, sql.Length - 1);
                        sql += ")";
                        cmd = new SqlCommand(sql, con);
                        cmd.ExecuteNonQuery();

                    }

                    sql = null;
                    if (count == 0)
                    {
                        sql = "CREATE TABLE" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "2  ( DRAWID INTEGER PRIMARY KEY AUTOINCREMENT,  ";
                        for (int J = 0; J < 2000; J++)
                        {
                            sql += dt.Columns[J].ToString() + "INTEGER ,";

                        }
                        sql = sql.Substring(0, sql.Length - 1);
                        sql += ")";
                        cmd = new SqlCommand(sql, con);
                        cmd.ExecuteNonQuery();
                    }

                    sql = null;
                    if (count == 0)
                    {

                        sql = "CREATE TABLE" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "3  ( DRAWID INTEGER PRIMARY KEY AUTOINCREMENT,  ";
                        for (int k = 0; k < 2000; k++)
                        {
                            sql += dt.Columns[k].ToString() + "INTEGER,";

                        }
                        sql = sql.Substring(0, sql.Length - 1);
                        sql += ")";
                        cmd = new SqlCommand(sql, con);
                        cmd.ExecuteNonQuery();
                    }

                    count = 1;
                    con.Close();

                }
            }
            catch(Exception e)
            {
            }
        }

        public void insert_data(string table_name)
        {
            SqlCommand cmd;
            string sql = string.Empty;

            using(SqlConnection con=new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\MyPractice\table_divide\table_divide\Database2.mdf;Integrated Security=True"))
            {
                con.Open();

            foreach(DataRow dr in dt.Rows)
            {

                sql = "INSERT INTO" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "1" + "VALUES(" + row + ",";
                for(int i=0;i<1000;i++)
                {
                    sql += dr[i] + ",";
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += ")";
                cmd = new SqlCommand(sql,con);
                cmd.ExecuteNonQuery();


                //second table

                sql = "INSERT INTO" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "2" + "VALUES(" + row + ",";
                for (int i = 1000; i < 2000; i++)
                {
                    sql += dr[i] + ",";
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += ")";
                cmd = new SqlCommand(sql, con);
                cmd.ExecuteNonQuery();


                //3rd
                sql = "INSERT INTO" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "3" + "VALUES(" + row + ",";
                for (int i = 2000; i < 2710; i++)
                {
                    sql += dr[i] + ",";
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += ")";
                cmd = new SqlCommand(sql, con);
                cmd.ExecuteNonQuery();
                row++;
                con.Close();

            }
        }
    }
    }
}

Posted 25-May-15 23:56pm

Manidas Payyoli

Updated 26-May-15 1:25am

CHill60

v2

Add a Solution

Comments

vinayvraman 26-May-15 6:12am

Just curious! Why not convert the data into a XML file and save the file as a single column data in the table? Or on second thought I suggest you to put the most important fields in CSV as columns and the rest of them as a single column XML data.

Sinisa Hajnal 26-May-15 6:47am

Create fully functional table shema with relations, real types etc...then spend time parsing your file into corresponding structures and save them. Don't save such file directly into single big table. The fact that such file even exists shows bad design. Don't copy it into your database.

[no name] 26-May-15 7:26am

{ //Creating object of datatable
DataTable tblcsv = new DataTable();
//creating columns
tblcsv.Columns.Add("Name");
tblcsv.Columns.Add("City");
tblcsv.Columns.Add("Address");
tblcsv.Columns.Add("Designation");
//getting full file path of Uploaded file
string CSVFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
//Reading All text
string ReadCSV = File.ReadAllText(CSVFilePath);
//spliting row after new line
foreach (string csvRow in ReadCSV.Split('\n'))
{
if (!string.IsNullOrEmpty(csvRow))
{
//Adding each row into datatable
tblcsv.Rows.Add();
int count = 0;
foreach (string FileRec in csvRow.Split(','))
{
tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;
count++;
}
}

}
//Calling insert Functions
InsertCSVRecords(tblcsv);
}

CHill60 26-May-15 7:37am

How does this help the OP? You are only creating 1 table with 4 columns - they have 2000 columns!

CHill60 26-May-15 7:32am

Instead of saying "it doesn't work" explain what doesn't work.

1 solution

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)

senthil .Net Developer · Accepted Answer · 2015-05-26T01:26:00

{

C#

//Creating object of datatable
       DataTable tblcsv = new DataTable();
       //creating columns
       tblcsv.Columns.Add("Name");
       tblcsv.Columns.Add("City");
       tblcsv.Columns.Add("Address");
       tblcsv.Columns.Add("Designation");
        //getting full file path of Uploaded file
       string CSVFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
       //Reading All text
       string ReadCSV = File.ReadAllText(CSVFilePath);
       //spliting row after new line
       foreach (string csvRow in ReadCSV.Split('\n'))
       {
           if (!string.IsNullOrEmpty(csvRow))
           {
               //Adding each row into datatable
               tblcsv.Rows.Add();
               int count = 0;
               foreach (string FileRec in csvRow.Split(','))
               {
                   tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;
                   count++;
               }
           }


       }
       //Calling insert Functions
       InsertCSVRecords(tblcsv);
   }