Reading a delimited text file?

Question

I am trying to read from a delimited text file, but everything returns in one row and one column.

My connection string

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.GetDirectoryName(@textBox1txtPath.Text) + ";" + "Extended Properties=\"text;HDR=YES;IMEX=1;Format=Delimited(|)\""); 

And my text file reads:

 ItemNumber|ProductStatus|UPC 0000012|closed|2525 

Please, help

Answer 1

I don’t know exactly what you need, but you can do this:

if you have string str with all the text in it, you can do

 string[] lines = str.Split('\n');// split it to lines; 

and then for each line you can do

 string[] cells = line.Split('|');// split a line to cells 

if we go to the next level we can do:

 public class line { public int ItemNumber { get; set; } public string ProductStatus { get; set; } public int UPC { get; set; } public line(string currLine) { string[] cells = currLine.Split('|'); int item; if(int.TryParse(cells[0], out item)) { ItemNumber = item; } ProductStatus = cells[1]; int upc; if (int.TryParse(cells[2], out upc)) { UPC = upc; } } } 

and then:

 string[] lines = str.Substring(str.IndexOf("\n")).Split('\n');// split it to lines; List<line> tblLines = new List<line>(); foreach(string curr in lines) { tblLines.Add(new line(curr); } 

Answer 2

Good, so one option would be to use a different approach. Consider the following code:

 // read the entire file and store each line // as a new element in a string[] var lines = File.ReadAllLines(pathToFile); // we can skip the first line because it's // just headings - if you need the headings // just grab them off the 0 index for (int i = 1; i < lines.Length; i++) { var vals = lines[i].Split('|'); // do something with the vals because // they are now in a zero-based array } 

This eliminates the enormity of the connection string, eliminates the overhead of the Odbc driver, and greatly improves code readability.

Answer 3

This is correct within the framework - TextFieldParser . Don’t worry about the namespace, this was originally a gasket for people migrating from VB6, but it is very useful. Here is SSCCE, which demonstrates its use for several different delimiters:

 class Program { static void Main(string[] args) { var comma = @"one,""two, yo"",three"; var tab = "one\ttwo, yo\tthee"; var random = @"onelol""two, yo""lolthree"; var parser = CreateParser(comma, ","); Console.WriteLine("Parsing " + comma); Dump(parser); Console.WriteLine(); parser = CreateParser(tab, "\t"); Console.WriteLine("Parsing " + tab); Dump(parser); Console.WriteLine(); parser = CreateParser(random, "lol"); Console.WriteLine("Parsing " + random); Dump(parser); Console.WriteLine(); Console.ReadLine(); } private static TextFieldParser CreateParser(string value, params string[] delims) { var parser = new Microsoft.VisualBasic.FileIO.TextFieldParser(ToStream(value)); parser.Delimiters = delims; return parser; } private static void Dump(TextFieldParser parser) { while (!parser.EndOfData) foreach (var field in parser.ReadFields()) Console.WriteLine(field); } static Stream ToStream(string value) { return new MemoryStream(Encoding.Default.GetBytes(value)); } }