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

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

Attachments
JZ0001 07092009112401                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
JZ0002 08092009131114                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
JZ0003 09092009092223                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
JZ0004 10092009150705

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

        }
    }
}

Edited 6 Years Ago by Nick Evan: Added code-tags

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.

Edited 3 Years Ago by happygeek: fixed formatting

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.

This question has already been answered. Start a new discussion instead.