How do I insert a flat txt file into a SQL server table

[Jarq]

Hi
I need to insert a flat text file into a SQL Server table using C#.

The text file lines are 500 characters long and I will need to break it up into columns.

Column 1 – 6chars, Column 2 – 1chars, Column 3 – 8chars

I’ve managed to read the lines, but I can’t get it into the database.

Please help

[sknake]

Are you trying to store the text file as a BLOB in the database or parse the file and store values in corresponding MSSQL columns? Can you post a sample of the text file and your SQL table structure?

[Jarq]

I'm trying to store the values in corresponding MSSQL columns.
The table records should be as follows:
|Record ID |Spacer |Date |Time
|6 characters |1 Character |8 Characters |6 Characters

I've attached the flat txt file

[sknake]

That should be simple enough. Post the code you have so far to read the lines and we'll go from there.

[Jarq]

using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TextImport
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void txtImport_Click(object sender, EventArgs e)
        {
            string filePath = @"C:\TextImport\Header.txt";

            string line;
            //Interface Header
            string RecordID;
            string Spacer;
            string Date;
            string Time;

            if (File.Exists(filePath))
            {
                StreamReader file = null;

                try
                {
                    file = new StreamReader(filePath);
                    while ((line = file.ReadLine()) != null)
                    {
                        //Interface header = 39 chars rest trailing space
                        if (line.Trim().Length < 500)
                        {
                            RecordID = line.Substring(0, 6);
                            Spacer = line.Substring(6, 1);
                            Date = line.Substring(7, 8);
                            Time = line.Substring(15, 6);

                            MessageBox.Show(RecordID);
                        }
                    }
                }
                finally
                {
                    if (file != null)
                        file.Close();
                }
            }
        }
        private void txtxDisplay_TextChanged(object sender, EventArgs e)
        {

        }
    }
}

[sknake]

Please use code tags when posting code on daniweb:

Here is an example of inserting data in an SQL database:

private void button3_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string query = "Insert Into UserTable (UserName, DateHired) Values (@UserName, @DateHired)";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar)).Value = "sknake";
          cmd.Parameters.Add(new SqlParameter("@DateHired", SqlDbType.DateTime)).Value = DateTime.Today;
          cmd.ExecuteNonQuery();
        }
        conn.Close();
      }
    }

See if you can merge the logic in to your code.

[Jarq]

Thanks, but how do I make the connection to the text file

[sknake]

Thanks, but how do I make the connection to the text file

You already posted the code using a StreamReader to read lines from a file. Isn't that what you want?

[Jarq]

Thanks you Sknake
That example really helped a lot.