Import Data from CSV File to Data table

Using this Function it will read the csv file and it returns the data table

static DataTable ImportCSVoDatatable(string FilePath)
    {
        DataTable dt = new DataTable();
            string[] Lines = File.ReadAllLines(FilePath);
            string[] Fields;
            Fields = Lines[0].Split(new char[] { ',' });
            int Cols = Fields.GetLength(0);
         
            //1st row must be column names; force lower case to ensure matching later on.
            for (int i = 0; i < Cols; i++)
                dt.Columns.Add(Fields[i].ToLower(), typeof(string));
            DataRow Row;
            for (int i = 1; i < Lines.GetLength(0); i++)
            {
                Fields = Lines[i].Split(new char[] { ',' });
                Row = dt.NewRow();
                for (int f = 0; f < Cols; f++)
                    Row[f] = Fields[f];
                dt.Rows.Add(Row);
            }
return dt;
}

CSV File to SQL using Stored procedure

create procedure [dbo].[SP_Export](@FilePath nvarchar(500))
as
begin
DECLARE @filepath1 nvarchar(500)
--SET @filepath1 = ''+@FilePath+''
--select @FilePath1
DECLARE @bulkinsert NVARCHAR(2000)
SET @bulkinsert = 'bulk insert [dbo].[vw_bulk_insert_test] FROM '''+@filepath+''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'
--print @bulkinsert
EXEC sp_executesql @bulkinsert
end
--exec SP_Export 'D:\Pass sheets\SYNDOD26.02.14.csv'

1 Use a View
Create a view that has only required columns

1.create view vw_bulk_insert_test

2.as

3.select first_name,last_name from bulk_insert_test

Now use BULK INSERT using this view

1.BULK INSERT vw_bulk_insert_test

2.FROM 'g:\test.txt'

3.WITH

4.(

5.FIELDTERMINATOR =',',

6.ROWTERMINATOR = '\n'

7.)

2 Create Format file

1.8.0

2.2

3.1  SQLCHAR  0  30 ","        2  first_name                SQL_Latin1_General_Cp437_BIN

4.2  SQLCHAR  0  30 ","        3  last_name                 SQL_Latin1_General_Cp437_BIN

Name it as format.txt Now use BULK INSERT using this format file

1.BULK INSERT bulk_insert_test

2.FROM 'g:\test.txt'

3.with (formatfile = 'g:\format.txt')

3 Use OPENROWSET

1.INSERT INTO bulk_insert_test(first_name,last_name)

2.SELECT

3.*

4.FROM

5.OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=g:\', test#txt)

As you see method 3 doesn't require any extra work if the number of columns are different

Tags: t-sql, sql_server, bulk_insert,

Convert Comma-Separated Strings to String Arrays

String.Split Method

The Split method requires a parameter containing a character array. Each item in the array is a potential delimiting character. Handily, the parameter is a Parameter Array. This allows multiple characters to be included directly in the method without initialising the array beforehand.

The following code splits a comma-separated list:

string fruit = "Apple,Banana,Orange,Strawberry";

string[] split = fruit.Split(',');

foreach (string item in split)

{

    Console.WriteLine(item);

}

     

/* OUTPUT

Apple

Banana

Orange

Strawberry

*/

The following example is similar but this time the delimiting characters are the bar (|) and the comma. Both cause the generation of an additional result in the array.

string fruit = "Apple|Banana,Orange|Strawberry";

string[] split = fruit.Split('|', ',');

foreach (string item in split)

{

    Console.WriteLine(item);

}

/* OUTPUT

Apple

Banana

Orange

Strawberry

*/

How to add Values from Data Grid view to Combo box

How to add Values from Data Grid view to Combo box

 DataTable dt =new DataTable();

            //DataTable dt = new DataTable();
            dt.Columns.Add("ExiseINVNo", typeof(String));
            //dt.Columns.Add("Money", typeof(String));
            dt.Rows.Add(new object[] { "      <--Select-->   "});
            //dt.Rows.Add(new object[] { "Ki", 30 });


            foreach (DataGridViewRow _dgr in dgvSalesInvItems.Rows)
            {
                if (_dgr.Cells["ExciseInvoice_No"].Value != null)
                {
                    string val = _dgr.Cells["ExciseInvoice_No"].Value.ToString();

                    dt.Rows.Add(new object[] { val});
                }

            }
            //DataTable Dt1 = Masters.RemoveDuplicateRows(dt, "ExiseINVNo");
            //DataRow dr = dt.NewRow();
            //dr["Exise"] = "< -- Select -- >";
            //DSItems.Tables[0].Rows.InsertAt(dr, 0);
            cmbExciseInvNo.DataSource = Dt1;
            cmbExciseInvNo.DisplayMember = "ExiseINVNo";

            cmbExciseInvNo.SelectedIndex = 0;

Best way to remove duplicate entries from a data table

public DataTable RemoveDuplicateRows(DataTable dTable, string colName)
{
   Hashtable hTable = new Hashtable();
   ArrayList duplicateList = new ArrayList();

   //Add list of all the unique item value to hashtable, which stores combination of key, value pair.
   //And add duplicate item value in arraylist.
   foreach (DataRow drow in dTable.Rows)
   {
      if (hTable.Contains(drow[colName]))
         duplicateList.Add(drow);
      else
         hTable.Add(drow[colName], string.Empty); 
   }

   //Removing a list of duplicate items from datatable.
   foreach (DataRow dRow in duplicateList)
      dTable.Rows.Remove(dRow);

   //Datatable which contains unique records will be return as output.
      return dTable;
}

Convert Indian Currency Format in SQL server

Convert Indian Currency Format in SQL server

Use this Function It will convert and returns the Indian currency format

-- local settings may produce this for you
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
alter FUNCTION dbo.IndianDecimalFormat
(
@Currency decimal(11,2)
)
RETURNS varchar(15)
AS
BEGIN
DECLARE @tCurr varchar(12)
,@RetVal varchar(16)

SELECT @tCurr = CAST(@Currency AS varchar(12))
,@RetVal = ''

;WITH IndianCurrency (pos, ln, ord)
AS
(
SELECT 1, 2, 5
UNION ALL SELECT 4, 3, 4
UNION ALL SELECT 7, 2, 3
UNION ALL SELECT 9, 2, 2
UNION ALL SELECT 11, 2, 1
), Split (Number, ord)
AS
(
SELECT REVERSE(SUBSTRING(REVERSE(@tCurr), pos, ln))
,ord
FROM IndianCurrency
)
SELECT @RetVal = @RetVal + Number + CASE WHEN ord = 4 THEN '.' ELSE ',' END
FROM Split
WHERE LEN(Number) > 0
ORDER BY ord

RETURN LEFT(@RetVal, LEN(@RetVal) - 1)
END
GO

--select dbo.IndianDecimalFormat(454121)

O/P: 4,54,121.00

Export Excel From Data Table

protected void ExportExcel(DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0) return;

            var xlApp = new Excel.Application();
            //Is this used?
            CultureInfo CurrentCI = Thread.CurrentThread.CurrentCulture;

            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

            Excel.Workbooks workbooks = xlApp.Workbooks;

            Excel.Range range;

            Excel.Workbook workbook = workbooks.Add();
            Excel.Worksheet worksheet = workbook.Worksheets[1];

            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;

            //xlApp.worksheet.Properties.Title = @"This co";
            // worksheet.Cells[1, 3] = "BANAVATHY POWER SYSTEMS PVT. LTD.";
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;

                range = worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
                range.Borders.Color = Color.Green;




            }

            for (var r = 0; r < dt.Rows.Count; r++)
            {

                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                }

                rowRead++;

                //is this used?
                percent = ((float)(100 * rowRead)) / totalCount;
            }

            Microsoft.Office.Interop.Excel.Range columns = worksheet.UsedRange.Columns;
            columns.AutoFit();
            worksheet.Activate();
            worksheet.Application.ActiveWindow.SplitRow = 1;
            worksheet.Application.ActiveWindow.FreezePanes = true;
            // Now apply autofilter
            Excel.Range firstRow = (Excel.Range)worksheet.Rows[1];
            firstRow.Activate();
            firstRow.Select();
            //firstRow.AutoFilter(1,
            //                    Type.Missing,
            //                    Excel.XlAutoFilterOperator.xlAnd,
            //                    Type.Missing,
            //                    true);
            //worksheet.Activate();
            //worksheet.Application.ActiveWindow.SplitColumn = 1;
            //worksheet.Application.ActiveWindow.FreezePanes = true;

            //worksheet.Rows[1].Insert();
            //Excel.Range newRow = worksheet.Rows[1];
            //Excel.Range newCell = newRow.Cells[9];
            //newCell.MergeCells = true;

            //newCell.Value2 = "BANAVATHY POWER SYSTEMS PVT. LTD:Sales Invoice";
            //newCell.Value2="Item Wise Sales Report";
           // newCell.Font.Bold = true;
          //  newCell.Font.Color = Color.Green;
            //newCell.Value = DateTime.Now.ToString("yyyy-MM-dd");

            dt.Clear();
            xlApp.Visible = true;
        }