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

Recommended Answers

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?

Jump to Post

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

Jump to Post

All 8 Replies

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?

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

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

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)
        {

        }
    }
}

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.

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

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?

Thanks you Sknake
That example really helped a lot.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.