How to Store txt information in database SQL Server

Work on VS08 C# window. I have txt file . After read the file, I need to store the file information in database. My SQL Server database table structure is below:

CREATE TABLE [dbo].[StoreTxtValues]
(
  [PortCode] [int] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  1)[Vessel] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  2)[Voyage] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  3)[Sailed] [datetime] NOT NULL,
  4)[Carrier] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NO开发者_Python百科T NULL,  
  5)[LoadingContainer] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,  
  6)[DischargeSeal] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  7)[rowValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

)

Please see those numbers in the picture. I need help to store the data as I describe on it. Any suggestion will be accepted. Thanks in advance.

Parse the file line by line to get the information you need. Use System.IO.FileStream or StreamReader to load the data, and then you can process it as needed. Save it to the database.

Here's a quick example for using StreamReader from here.

using (StreamReader sr = new StreamReader(path))   
{  
    while (sr.Peek() >= 0)   
    {  
        string lineFromFile = sr.ReadLine();  
        //now parse the string lineFromFile and get the information you need  
    }  
} 

I might add that it might be helpful to create objects to represent the various pieces of data you need to store. That object can then make it easier to deal with the data, saving it to a database etc.

Parsing the string (this assumes the files are all the same exact format, i.e. same lines of information).

Keep track of what line you are on.

string[] myFile = File.ReadAllLines(@"C:\file.txt");

var lineCount = myFile.Length;

     for(i=0; i<lineCount; i++)  
     {
          switch(i)
          {
               case 0:
                   //parse line 0
                   break;
               case 1:
                   //parse line 1
                   break;
               case 2:
                   //parse line 2
                   break;
               //and so on until you get to the line that begins the regular "records" part of the file
          }

          if(i >= 10) //assumes line 10 is where the regular "records" start
          {
              //parse the record like so
              string[] columns = myFile[i].Split('\t'); //this assumes your columns are separated by a tab. If it's a space you would use myFile[i].Split(' '), etc. 
              //now you have an array with all your columns
              foreach(string column in columns)
              {
                   //now do what you want with the information

              }
          }
     }
}

Following on Richards answer,

It seems to me that you need multiple tables.

Table 1, might be called ManifestHeader and contain columns 1-6.

Table 2, might be called ManifestDetails and contain columns 7 (which could be broken down into further fields, such as LoadingContainer, DischargeSeal, Dest and so forth).

Your data is a mixture of fixed width and comma delimited (column 6) and you can use String.Substring and String.Split to separate your lines columns.

e.g.

using System;
using System.Collections.Generic;
using System.IO;
using System.Collections;

public class MyClass
{
    public static void ProcessFile()
    {
        TextReader textReader = new StreamReader(@"C:\DEV\DATA\MyDataFile.txt");
        string s = textReader.ReadLine();  
        string col1 = s.Substring(9, 29).Trim();  

        s = textReader.ReadLine();  
        string col2 = s.Substring(9, 29).Trim();  

        s = textReader.ReadLine();  
        string col3 = s.Substring(9, 29).Trim();  

        s = textReader.ReadLine();  
        string col4 = s.Substring(9, 29).Trim();


        s = textReader.ReadLine();
        s = textReader.ReadLine();
        s = textReader.ReadLine();
        s = textReader.ReadLine();
        s = textReader.ReadLine();
        s = textReader.ReadLine();

        s = textReader.ReadLine();  
        string col5 = s.Split(",")[0].Trim();
        string col6 = s.Split(",")[1].Trim();

        while ((s = textReader.ReadLine()).Trim() != "")
        {
            string LoadingContainer = s.Substring(0, 10).Trim();  
            string DischargeSeal = s.Substring(13, 23).Trim();  
            string Dest  = s.Substring(25, 25).Trim();
            // Parse further colums here...
            // Insert record into the database
        }
    }

    public static void Main()
    {
        try
        {
            ProcessFile();
        }
        catch (Exception e)
        {
            string error = string.Format("---\nThe following error occurred while executing the program:\n{0}\n---", e.ToString());
            Console.WriteLine(error);
        }
        finally
        {
            Console.Write("Press any key to continue...");
            Console.ReadKey();
        }
    }

}