automatically transform text file-CodePudding

I'm pretty new to programming and i have to write a short script like i copied pasted under here can someone help me with that, the language has to be in C#

-- from: .asci file

Trashcan1,509103.887 m,5535946.837 m,-1.650 m,PC
Lamp2,509103.407 m,5536024.068 m,-1.771 m,PC
Lamp3,509108.618 m,5536012.905 m,-1.371 m,PC

-- to: .sql or .txt

INSERT INTO geo (fc, created, the_geom) VALUES ('Trashcan', now(), st_setsrid(gb_makepoint(509103.887,5535946.837),25832))
INSERT INTO geo (fc, created, the_geom) VALUES ('Lamp', now(), st_setsrid(gb_makepoint(509103.407,5536024.068),25832))
INSERT INTO geo (fc, created, the_geom) VALUES ('Lamp', now(), st_setsrid(gb_makepoint(509108.618,5536012.905),25832))

-- write a program code, that can automatically transform that

CodePudding user response：

You can use File.ReadLines, WriteAllLines and string methods:

var sqlInserts = System.IO.File.ReadLines(inPath)
    .Select(GetInsertFromLine)
    .Where(s => s != null);
System.IO.File.WriteAllLines(outPath, sqlInserts);

and the method GetInsertFromLine

static string GetInsertFromLine(string line)
{
    string[] fields = line.Split(',');
    if(fields.Length < 3) return null;
    string geom1 = fields[1].Split()[0].Trim();
    string geom2 = fields[2].Split()[0].Trim();
    string geom = $"st_setsrid(gb_makepoint({geom1},{geom2}))";    
    return $"INSERT INTO geo (fc, created, the_geom) VALUES ({fields[0]}, now(), {geom}, 25832)";
}

But if you don't trust the input file you should not parse it like this. Then you should use decimal.TryParse to validate the values and use parameterized queries to insert it into the DB.

CodePudding user response：

Technically, you can use regular expressions to match name and two numbers first and second which we can insert into query with a help of string interpolation:

using System.IO;
using System.Linq;
using System.Text.RegularExpressions;

...

Regex regex = new Regex(
  @"^\s*(?<name>.*?)[0-9] \s*,(?<first>[0-9] (\.[0-9] )?)\s*m,(?<second>[0-9] (\.[0-9] )?)");

var result = File
  .ReadLines(@"c:\myFile.txt")
  .Select(item => regex.Match(item))
  .Where(match => match.Success)
  .Select(match => $"INSERT INTO geo (fc, created, the_geom) VALUES ('{match.Groups["name"].Value}', now(), st_setsrid(gb_makepoint({match.Groups["first"].Value},{match.Groups["first"].Value}),25832))");

File.WriteAllLines(@"C:\MySqlFile.txt", result);