Eead text file with fixed columns in C#

Question

is there any way to read text files with fixed columns in C # without using regex and substring?

I want to read a file with fixed columns and transfer the column to an excel file (.xlsx)

example 1

                                       POPULACAO 
MUNICIPIO                           UF CENSO 2010 

                                    AC     78.507
                                    AC     15.100
Rio Branco                          AC    336.038
Sena Madureira                      AC     38.029

example 2

                                       POPULACAO 
MUNICIPIO                           UF CENSO 2010 

                                    AC     78.507
Epitaciolândia                      AC     15.100
Rio Branco                          AC    336.038
Sena Madureira                      AC     38.029

remembering that I have a case as in the second example where a column is blank, I can get the columns and the values using regex and / or substring, but if it appears as a file in Example 2, with the regex line of the file is ignored, so does substring.

I'm a little confused...what's happening when you use substring in the second example? — MikeH
– MikeH, Commented Oct 31, 2014 at 18:08
explore and try to understand the basic of programming, it is a very basic thing ... — Ali Adravi
– Ali Adravi, Commented Oct 31, 2014 at 18:10
column formation line 2 is not satisfied, and using substring or regex, it is ignored and the time to write to excel it writes like a white line. — MeuChapeu
– MeuChapeu, Commented Oct 31, 2014 at 18:10
When you say "fixed", do you mean fixed width? As in the training column begins at position 11 and is 15 characters long? or do you mean, I have a file with space delimited columns? — jac
– jac, Commented Oct 31, 2014 at 18:12
The correct approach is probably substring. Please show us some code. We can't tell you what you're doing wrong unless we see it. — MikeH
– MikeH, Commented Oct 31, 2014 at 18:14

furkle · Accepted Answer · 2014-10-31 19:05:41Z

1

Assuming you mean "fixed columns" extremely literally, and every single non-terminal column is exactly the same width, each column is separated by exactly one space, yes, you can get away with using neither regex or substring. If that's the case - and bear in mind that's also suggesting that every single person in the database has a name that's exactly four letters long - then you can just read the file in by lines. Id would be line[0].ToString(), name would be new string(new char[] { line[2], line[3], line[4], line[5]), etc.

Or, for any given value:

var str = new StringBuilder();
for (int i = firstIndex; i < lastIndex; i++)
{
    str.Append(line[i]);
}

But this is basically just performing the exact function of Substring. Substring isn't your problem - handling empty values in the first (city) column is. So, for any given line, you need to check whether the line is empty:

foreach (line in yourLines)
{
    if (line.Substring(cityStartIndex, cityEndIndex).IsNullOrWhitespace) == "")
    {
        continue;
    }
}

Alternately, if you're sure the city name will always be at the very first index of the line:

foreach (line in yourLines)
{
    if (line[0] == ' ') { continue; }
}

And if the value you got from the city cell was valid, you'd store that value and continue on to using Substring with the indices of the rest of the values in the row.

edited Oct 31, 2014 at 19:05

answered Oct 31, 2014 at 18:17

furkle

5,0591 gold badge18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MikeH Over a year ago

I could be that this really is coming from a system that uses fixed width columns. I imagine the real file has more than 4 spaces for the name.

furkle Over a year ago

@MikeH Yeah, maybe I'm assuming too much (namely that the example he posted looks like what he actually wants to deal with), but even so, what if even a single space padding disappears? My feeling is generally that smaller assumptions are better.

MeuChapeu Over a year ago

@furkle, I have not explained myself right, I edited the question by putting another example, in the second example the first line is not satisfied with the city.

furkle Over a year ago

@MeuChapeu I understand better now - I've updated my response to match what you're looking for re: empty city columns.

MeuChapeu Over a year ago

@furkle Thank you . I go testing here !

Justin R. · Accepted Answer · 2014-10-31 18:24:17Z

1

If for whatever reason you don't want to use a regular expression or Substring(), you have a couple of other options:

String.Split, e.g. var columns = line.Split(' ');
String.Chars, using the known widths of each column to build your output;

answered Oct 31, 2014 at 18:24

Justin R.

24.2k27 gold badges113 silver badges159 bronze badges

Comments

Ari D Jordon · Accepted Answer · 2014-10-31 18:35:09Z

Why not just use string.Split()?

Something like:

using (StreamReader stream = new StreamReader(file)) {
  while (!stream.EndOfStream) {
    string line = stream.ReadLine();
    if (string.IsNullOrWhitespace(line))
      continue;
    string[] fields = line.Split((char[])null, StringSplitOptions.RemoveEmptyEntries);
    int ID = -1, age = -1;
    string name = null, training = null;
    ID = int.Parse(fields[0]);
    if (fields.Length > 1)
      name = fields[1];
    if (fields.Length > 2)
      age = int.Parse(fields[2]);
    if (fields.Length > 3)
      training = fields[3];
    // do stuff
  }
}

Only downside to this is that it will allow fields of arbitrary length. And spaces in fields will break the fields.

As for regular expressions being ignored in the last case, try something like:

Match m = Regex.Match(line, @"^(.{2}) (.{4}) (.{2})( +.+?)?$");

jac · Accepted Answer · 2014-10-31 18:45:00Z

1

First - define a variable for each column in the file. Then go through the file line by line and assign each column to the correct variable. Substitute the correct start positions and lengths. This should be enough information to get you started parsing your file.

private string id;
private string name;
private string age;
private string training;

while((line = file.ReadLine()) != null)
{
    id = line.Substring(0, 3)
    name = line.Substring(3, 10)
    age = line.Substring(12, 2)
    training = line.Substring(14, 10)
    ...
    if (string.IsNullOrWhiteSpace(name))
    {
        // ignore this line if the name is blank
    }
    else
    {
        // do something useful
    }
    counter++;
}

answered Oct 31, 2014 at 18:45

jac

9,7542 gold badges39 silver badges64 bronze badges

Collectives™ on Stack Overflow

Eead text file with fixed columns in C#

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related