How to read the data from the notepad and save the value in database?

Preetham Thangamma

Good Morning,

Please do let me know how to read the values from the notepad where the values are seperated by semicolons and those semicolon seperated values must be saved in the each coloumn in the database!!

For ex:

000004004635;20200622;1000;000000000090001601;HOOK CLAMP K116951-BURKLE;NOS;1.000 ;1.000

;00000000;1000;000000000090001611;PU Straight Union 4 X 6;NOS;1.000 ;1.000

;00000000;1000;000000000090001616;PU Straight Union 6 X 10;NOS;1.000 ;1.000

Here 000004004635 is saved as ordernumber in Database and 20200622 is saved as material number in Database and again 00000000 in the second row must be saved as ordernumber in Database and so on!!

Thank you!!

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Aman Gupta6y ago
https://www.red-gate.com/simple-talk/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

Refer this it will work for your problem
Preetham Thangamma6y ago
Okay Sir...Thank you!!
Jenith Thakkar6y ago
Hi
try this

bulk insert [dbo].[CoursesTemp]
from 'C:\Users\Public\Downloads\Courses.txt'
with (fieldterminator = ';', rowterminator = '\n')
go
Preetham Thangamma6y ago
Good Morning Sir,

Thank you for the answer but i have to save the data in the database from the notepad....can you please let me know how to do that!!

Thank you
Jenith Thakkar6y ago
using System;
using System.IO;

namespace ReadATextFile {
class Program {
  // Default folder
  static readonly string rootFolder = @ "C:\Temp\Data\";
  //Default file. MAKE SURE TO CHANGE THIS LOCATION AND FILE PATH TO YOUR FILE
  static readonly string textFile = @ "C:\Temp\Data\Authors.txt";

  static void Main(string[] args) {
   if (File.Exists(textFile)) {
    // Read entire text file content in one string
    string text = File.ReadAllText(textFile);
    Console.WriteLine(text);
   }

   if (File.Exists(textFile)) {
    // Read a text file line by line.
    string[] lines = File.ReadAllLines(textFile);
    foreach(string line in lines){
       var data = line.split(';');
       //here data[0]= '000004004635';
       //here data[1]= '20200622';
       // just run the loop here and pass data
       Console.WriteLine(line);
    }
   }

   if (File.Exists(textFile)) {
    // Read file using StreamReader. Reads file line by line
    using(StreamReader file = new StreamReader(textFile)) {
     int counter = 0;
     string ln;

     while ((ln = file.ReadLine()) != null) {
      Console.WriteLine(ln);
      counter++;
     }
     file.Close();
     Console.WriteLine($ "File has {counter} lines.");
    }
   }

   Console.ReadKey();
  }
}
}
You can do like above code