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

Answer 1

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

Answer 2

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

Answer 3

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.

1. Place the cursor in between '' and use the shortcut ALT+C

2. Select the option "Number to Insert", fill initial number (1) and increase by (1)