How can I read and replace Strings line by line in a Text File?

Question

I have a text file that reads this:

INSERT INTO `shops` VALUES ('', '3', '1000000', '0');
INSERT INTO `shops` VALUES ('', '3', '1000010', '0');
INSERT INTO `shops` VALUES ('', '3', '1000020', '0');
INSERT INTO `shops` VALUES ('', '3', '1000030', '0');
INSERT INTO `shops` VALUES ('', '3', '1001000', '0');

Notice for each line the first key is ''. For each line, I want to find that '', and replace it with a number (starting with 1), then add 1 to it as it goes to the next line, Like so:

INSERT INTO `shops` VALUES ('1', '3', '1000000', '0');
INSERT INTO `shops` VALUES ('2', '3', '1000010', '0');
INSERT INTO `shops` VALUES ('3', '3', '1000020', '0');
INSERT INTO `shops` VALUES ('4', '3', '1000030', '0');
INSERT INTO `shops` VALUES ('5', '3', '1001000', '0');

I've been trying to do this for a couple of hours but I'm failing.

Here is what I've been thinking of (I know this is far from right, but I'm not that savvy in c#, so maybe one of you can help me come up with the right code):

string text = File.ReadAllText("C:\\Users\\Donavon\\Desktop\\old.sql");

int i = 0;
text = text.Replace("('',", "('" + i + "',");
i++;
File.WriteAllText("C:\\Users\\Donavon\\Desktop\\new.sql", text);

Thanks for any help, It's greatly appreciated

Why do you have a file like that in the first place? – juharr Aug 28 '15 at 15:27 — juharr, Aug 28 '15 at 15:27

score 9 · Answer 1 · edited Aug 28 '15 at 15:27

9

You will want to do something along these lines:

var lineNumber = 0;

using (var newFile = File.AppendText(@"c:\temp\new.sql"))
{
    foreach (var line in File.ReadLines(@"c:\temp\old.sql"))
    {
        lineNumber++;

        var updatedLine = line.Replace("('',", "('" + lineNumber.ToString() + "',");

        newFile.WriteLine(updatedLine);
    }
}

Use File.ReadLines to enumerate the lines so you don't get memory exceptions with big files

edited Aug 28 '15 at 15:27

Daniel

12,772
2
35
59

answered Aug 28 '15 at 15:25

Trevor Pilley

15,778
5
43
59

ProTip: Use this method. File streams are your friend when dealing with many large files. – slumtrimpet Jun 19 '19 at 06:23

Neal · Accepted Answer · 2018-07-30T10:21:17.563

6

You can read the lines in individually:

string text = "";
using (StreamReader sr = new StreamReader("C:\\Users\\Donavon\\Desktop\\old.sql"))
{
    int i = 0;
    do
    {
        i++;
        string line = sr.ReadLine();
        if (line != "")
        {
            line = line.Replace("('',", "('" + i + "',");
            text = text + line + Environment.NewLine;
        }
    } while (sr.EndOfStream == false);
}
File.WriteAllText("C:\\Users\\Donavon\\Desktop\\new.sql", text);

edited Jul 30 '18 at 10:21

answered Aug 28 '15 at 15:34

Neal

782
1
8
21

Thanks buddy. Thanks everyone else too, I've learned from this. I appreciate it guys – Donavon Aug 28 '15 at 15:39
@Donavon - That's a horrible way of doing it with all that string concatenation! See my answer for the proper way to do it – Trevor Pilley Aug 28 '15 at 15:42
2

@TrevorPilley: There are millions of ways to do it. I could argue your way is simply another method; Calling is "proper" is certainly a stretch given all the unknowns. – Brad Christie Aug 28 '15 at 16:28

score 3 · Answer 3 · answered Aug 28 '15 at 15:34

Not a code solution here, but if I had to do such thing and I knew the position of the character would always be the same (like your example), I would opt to use Notepad++ for a quick edit and don't bother learning programming languages.

Place the cursor in between '' and use the shortcut ALT+C
Select the option "Number to Insert", fill initial number (1) and increase by (1)

score 2 · Answer 4 · answered Aug 28 '15 at 15:31

2

   var lines = File.ReadAllLines(@"D:\temp\old.sql");

    for (int i = 0; i < lines.Count(); ++i)
         lines[i] = lines[i].Replace("\'\'", string.Format("\'{0}\'", i + 1));

    File.WriteAllLines(@"D:\temp\new.sql", lines);

answered Aug 28 '15 at 15:31

Andrey

150
1
9

score 0 · Answer 5 · answered Aug 28 '15 at 15:32

0

I think this will work. Got most of it from MSDN.

  int counter = 1;
        string line;

        // Read the file and display it line by line.
        System.IO.StreamReader file = 
           new System.IO.StreamReader("C:\\Users\\Donavon\\Desktop\\old.sql");

        while((line = file.ReadLine()) != null)
        {
           line.Replace("('',", "('" + counter.ToString() + "',");;
           counter++;
        }

answered Aug 28 '15 at 15:32

Aaron

1,321
1
13
30

How does that write the value back to a new file? – Trevor Pilley Aug 28 '15 at 15:32

score 0 · Answer 6 · answered Aug 28 '15 at 15:36

string text = File.ReadAllText("C:\Users\person\Desktop\old.sql"); System.Text.StringBuilder strBuilder = new StringBuilder();

        int i = 0;

        var theSplotStr = text.Split('\n');

        foreach (var item in theSplotStr)
        {
            System.Console.WriteLine(item);
            string revisedString = item.Replace("''", "'" + ++i + "'");
            strBuilder.Append(revisedString+"\n");

        }

        File.WriteAllText("C:\\Users\\person\\Desktop\\new.sql", strBuilder.ToString());

displayName · Answer 7 · 2015-08-28T17:55:16.863

Here is a hammer for you to push your thumb-pin in the board...

If you are interested, you can perform this operation faster by doing it in parallel. Start one task to read the lines from old file, multiple processor tasks to sanitize the lines read by reader task, and one writer task to write the results back to disk.

On my 8-core machine I am able to process a 124MB file, using ~100% CPU, in less than 3 seconds.

Attached below is a fully commented code.

using System;
using System.Collections.Concurrent;
using System.IO;
using System.Threading.Tasks;

namespace ConsoleApplication
{
    public static class Test
    {
        //The paths to read and write
        const string OldFilePath = @"C:\Users\Donavon\Desktop\old.sql";
        const string NewFilePath = @"C:\Users\Donavon\Desktop\new.sql";

        //The maximum number of lines we can read for parallel processing
        //given the memory restrictions etc. Please set this to a number 
        //that is optimum for you.
        static readonly int ExpectedMaxLines = (int)Math.Pow(2, 10);

        //The data structures to hold the old and new lines
        private static readonly BlockingCollection<string> DirtyLines = new BlockingCollection<string>(ExpectedMaxLines);
        private static readonly BlockingCollection<string> CleanLines = new BlockingCollection<string>(ExpectedMaxLines);

        //A common factory. Since all tasks are long running, this is enough.
        private static readonly TaskFactory TaskFactory = new TaskFactory(TaskCreationOptions.LongRunning, TaskContinuationOptions.None);

        public static void Main()
        {
            //Need to start one reader task which will read one line at a time and
            //put that line in the BlockingCollection for parallel processing.

            BeginReader();

            BeginParallelProcessing();

            //We have started 1 reader task and multiple processor tasks
            //Now we need to start a writer task that will write the cleaned lines to disk
            var finalTask = BeginWriter();

            //Since writer task is the task which will signify the end of the entire 
            //exercise of reading, processing and writing, we will wait till the 
            //writer task has finished too.
            Task.WaitAll(new[] {finalTask});

            Console.WriteLine("All text lines cleaned and written to disk.");
        }

        private static void BeginReader()
        {
            TaskFactory.StartNew(() =>
            {
                Console.WriteLine("Reader task initiated.");
                using (var reader = new StreamReader(OldFilePath))
                {
                    string line;
                    while ((line = reader.ReadLine()) != null)
                    {
                        DirtyLines.TryAdd(line);
                    }
                    DirtyLines.CompleteAdding();
                }
            });
        }

        private static void BeginParallelProcessing()
        {
            //Starting as many processor tasks as there are number of processors available
            //on this machine. These tasks will return when there are no more lines to process

            //Globally defined id, and a lock, for adding in the required lines.
            var globalId = 1;
            var idLock = new object();

            for (var taskIndex = 0; taskIndex < Environment.ProcessorCount; taskIndex++)
            {
                TaskFactory.StartNew(() =>
                {
                    while (!DirtyLines.IsCompleted)
                    {
                        string line, updatedLine;
                        if (!DirtyLines.TryTake(out line)) continue;
                        if (line.Contains("(''"))
                        {
                            int nextGlobalId;
                            lock (idLock)
                            {
                                nextGlobalId = globalId++;
                            }
                            updatedLine = line.Replace("('',", "('" + nextGlobalId + "',");
                        }
                        else
                        {
                            updatedLine = line;
                        }
                        CleanLines.Add(updatedLine);
                    }
                    //Adding a delay of 10 seconds to allow all processing tasks to finish
                    Task.Delay(10*1000);
                    if (!CleanLines.IsAddingCompleted)
                    {
                        CleanLines.CompleteAdding();
                    }
                });
            }
        }

        private static Task BeginWriter()
        {
            var finalTask = TaskFactory.StartNew(() =>
            {
                Console.WriteLine("Writer task initiated.");
                using (var writer = new StreamWriter(NewFilePath))
                {
                    while (!CleanLines.IsCompleted)
                    {
                        string cleanLine;
                        if (!CleanLines.TryTake(out cleanLine)) continue;
                        writer.WriteLine(cleanLine);
                    }
                }
            });
            return finalTask;
        }
    }
}

Brad Christie · Answer 8 · 2015-08-28T15:30:02.250

-1

// Read file in by line (give us an array to work with)
var file = File.ReadAllLines("old.sql");

// Write the lines back (after we've modified it through LINQ)
File.WriteAllLines("new.sql", file.Select((line,index) => {
  // Use the overload of `.Select()` which includes the index

  // Simple string replace at this point, inserting our index.
  return line.Replace("('',", String.Format("('{0}',", index));
}));

edited Aug 28 '15 at 15:30

answered Aug 28 '15 at 15:25

Brad Christie

98,427
16
148
198

That doesn't even compile into C# and ReadAllLines will make you sad if you open a big file! – Trevor Pilley Aug 28 '15 at 15:28
Forgot a `.Select`. And yes, but was going for an alternative method. – Brad Christie Aug 28 '15 at 15:29
'old.sql' won't work either, you'll be wanting "old.sql" – Trevor Pilley Aug 28 '15 at 15:29
D'oh. Funny, had it fine in LINQPad, but then lost something in the translation. Think I've been doing too much JS/Angular these last few days. ;-\ – Brad Christie Aug 28 '15 at 15:30

score -1 · Answer 9 · answered Aug 28 '15 at 15:28

-1

string text = File.ReadAllText("old.sql");
text = text.Replace("some text", "new value");
File.WriteAllText("old.sql", text);

answered Aug 28 '15 at 15:28

Pavel

11
2

How can I read and replace Strings line by line in a Text File?

9 Answers9

Linked