CSV - Import file to a Sql database through code

Question

I need to load data from text file/csv file to a SQL Server database. I used the code shown below to load data and is loading to the database the problem is the data in second column may contain space but I use space for separate the column data.

i.e.

200007 XXXX Check XXXX yyy 50
200013 YYYY Check ZZZZ yyy 50
200022 nnnn 25Mg 30 Tabs
200042 mmmm 30 Mg 30 Tabs

I need to store the first ID number in the first column and the remaining text in second column:

string str = Properties.Settings.Default.con;

SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand();

try
{
    cmd.Connection = con;
    con.Open();

    cmd.CommandText = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='TEMP_AUTO' AND xtype='U')" +
                      "CREATE TABLE TEMP_AUTO (" +
                      "ID varChar(10) NULL," +
                      "NAME varChar(50) NULL," +
                      "DATE TIMESTAMP NULL," +
                       ")";

    cmd.ExecuteNonQuery();

    string query1 = "INSERT INTO [dbo].[TEMP_AUTO]([ID],[NAME]) VALUES (@num1, @num2)";
    cmd.CommandText = query1;

    string[] allLines = File.ReadAllLines(txtFilePath.Text);

    for (int i = 0; i < allLines.Length; i++)
    {
        cmd.Parameters.Clear();
        string[] items = allLines[i].Split(new char[] { ' ' });
        cmd.Parameters.AddWithValue("@num1", items[0]);
        cmd.Parameters.AddWithValue("@num2", items[1]);

        cmd.ExecuteNonQuery();
    }

    MessageBox.Show("Successfully saved your data");
}
finally
{
    cmd.Dispose();
    con.Close();
}

If it is a CSV file, shouldn't the values be separated by commas ? Of is it just a custom text file as the file extension suggests in your code(.text or .txt)? — Vishnu Prasad V
– Vishnu Prasad V, Commented Jan 17, 2016 at 6:39
The solution is to change the process that creates the text file to use a another seperator (eg tab), or to delimit text with double quotes. — TT.
– TT., Commented Jan 17, 2016 at 7:57
Agree with @TT. - your underlying problem is that your data contains your separator character, without it being escaped. Barring some very constrained cases, this makes it ambiguous - it's going to be wrong on import (and you probably can't detect it, which is worse). Fix your generation process first. You should be able to trigger the bulk insert from C# - you shouldn't need to write this yourself. — Clockwork-Muse
– Clockwork-Muse, Commented Jan 17, 2016 at 8:45

shadow · Accepted Answer · 2016-01-17 08:37:41Z

A possible solution might be this:

string[] allLines = {
                                    "200007 XXXX Check XXXX yyy 50", 
                                    "200013 YYYY Check ZZZZ yyy 50", 
                                    "200015 ",
                                    "2541111"
                                };

            for (int i = 0; i < allLines.Length; i++)
            {
                string param1 = null;
                string param2 = null;
                int spaceIndex = allLines[i].IndexOf(' ');

                if (spaceIndex > 0)
                {
                    param1 = allLines[i].Substring(0, spaceIndex);

                    if (spaceIndex < allLines[i].Length - 1)
                    {
                        param2 = allLines[i].Substring(spaceIndex + 1, allLines[i].Length-1 - spaceIndex);
                    }                    
                }
                else
                {
                    param1 = allLines[i];
                }

                Console.WriteLine("param1:{0} param2:{1}", param1, param2);
            }

Bassam Gamal · Accepted Answer · 2016-01-17 06:57:24Z

0

Use SSIS to map this file as long as it has a standard structure to SQL Table.

answered Jan 17, 2016 at 6:57

Bassam Gamal

7012 gold badges8 silver badges24 bronze badges

Comments

Jay C · Accepted Answer · 2016-01-17 07:06:09Z

0

Is this a one time thing? Have you tried getting the data organized in Excel then using the SSMS import tool to bring it in? If you right click on the Database then Tasks > Import Data the wizard will appear when given the option to choose the source, choose Excel. Flat files is an option but if you can format it in Excel first that tends to work better.

As you click through you can adjust the column types and where the breaks are, much like importing into Excel itself.

answered Jan 17, 2016 at 7:06

Jay C

8827 gold badges18 silver badges40 bronze badges

1 Comment

Jobin Over a year ago

This is not a onetime thing, need this code to integrate with one working application for data comparison....

Community · Accepted Answer · 2017-05-23 11:52:01Z

0

Use the String.Split(Char[], Int32) method to split on the first occurrence of ' ' only. Eg

string[] items = allLines[i].Split(new char[] { ' ' }, 2);

Refs: MSDN and previous relevant question

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Jan 17, 2016 at 8:29

Liesel

2,9792 gold badges14 silver badges18 bronze badges

Comments

fractalwrench · Accepted Answer · 2016-01-17 10:55:01Z

0

Use the below code.

using (StreamReader sr = File.OpenText("txtFile.txt")) // Mention the path,if the file is not in application folder.

   {
            string str = String.Empty;<br/>
            while ((str = sr.ReadLine()) != null)
            {
                string[] item = str.Split(' ');
                SqlConnection con = new SqlConnection(str);
                SqlCommand cmd = new SqlCommand();
                string query1 = "INSERT INTO [dbo].[TEMP_AUTO]([ID],[NAME]) VALUES ('" + item[0] + "', '" + item[1]  + "')";
                // Do remain part<br/>
            }
        }

edited Jan 17, 2016 at 10:55

$fractalwrench's user avatar$

fractalwrench

4,0867 gold badges35 silver badges49 bronze badges

answered Jan 17, 2016 at 10:40

Tinku krishnan

11 bronze badge

Collectives™ on Stack Overflow

CSV - Import file to a Sql database through code

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related