I am trying to convert a fixed width text file into a CSV (with double quotes and commas). I have managed to get the file to list the correct data but I am having some trouble with a few things:
I need to add header fields at the top of the output csv file ("company", "policy", etc). I tried to declare them as string as add them to the output file but didn't work.
I need to remove the whitespaces between the fields and have double quotes and commas put between each field.
Any suggestions or help on how to do this would be appreciated.
Here is what I have:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
namespace parsetxt
{
class Progam
{
public static void Main(string[] args)
{
string csvfilePath = @"C:\Users\Desktop\C#\parsetxt\output.csv";
string[] lines = File.ReadAllLines(@"C:\Users\Desktop\C#\parsetxt\fprawd.txt");
List<Customer> customers = new List<Customer>();
foreach (string line in lines)
{
Customer c = new Customer();
c.Company = line.Substring(0, 1).Trim();
c.Policynum = line.Substring(1, 9).Trim();
c.source = line.Substring(10, 39).Trim();
c.grosswd = line.Substring(40, 17).Trim();
c.wdcharg = line.Substring(58, 14).Trim();
c.netwd = line.Substring(73, 15).Trim();
c.amtwithhld = line.Substring(85, 15).Trim();
c.taxpenalty = line.Substring(101, 18).Trim();
customers.Add(c);
File.WriteAllLines(csvfilePath, lines);
//string headers = "Company", "policy", "source", "grosswd", "wdcharg", "netwd", "amtwithhld", "taxpenalty";
}
}
public class Customer
{
public string? Company { get; set; }
public string? Policynum { get; set; }
public string? source { get; set; }
public string? grosswd { get; set; }
public string? wdcharg { get; set; }
public string? netwd { get; set; }
public string? amtwithhld { get; set; }
public string? taxpenalty { get; set; }
}
}
}
Here is my output:
FT0123371 TOTAL FD 10,801.90 0.00 9,969.21 832.69 0.000000
FT0553372 TOTAL FD 26,097.47 808.75 25,288.72 0.00 0.000000
FT0443373 TOTAL FD 27,065.31 1,257.38 25,807.93 0.00 0.000000
FD0443374 TOTAL FD 15,606.22 0.00 15,606.22 0.00 0.000000
Thanks in advance!
CodePudding user response：
public static void Main(string[] args)
{
string csvfilePath = @"C:\Users\Desktop\C#\parsetxt\output.csv";
string fixedWithFilePath = @"C:\Users\Desktop\C#\parsetxt\fprawd.txt";
string headers = "\"Company\", \"policy\", \"source\", \"grosswd\", \"wdcharg\", \"netwd\", \"amtwithhld\", \"taxpenalty\"";
var customers = File.ReadLines(@"C:\Users\Desktop\C#\parsetxt\fprawd.txt").
Select(Customer.FromFixedWidthString);
using (var sw = new StreamWriter(csvfilePath))
{
sw.WriteLine(headers);
foreach (var customer in customers)
{
sw.WriteLine(customer.ToCSVLine());
}
}
}
public class Customer
{
public string? Company { get; set; }
public string? Policynum { get; set; }
public string? source { get; set; }
public string? grosswd { get; set; }
public string? wdcharg { get; set; }
public string? netwd { get; set; }
public string? amtwithhld { get; set; }
public string? taxpenalty { get; set; }
public string ToCSVLine()
{
return $"\"{Company}\",\"{Policynum}\",\"{source}\",\"{grosswd}\",\"{wdcharg}\",\"{netwd}\",\"{amtwithhld}\",\"{taxpenalty}\"";
}
public static Customer FromFixedWidthString(string line)
{
return new Customer() {
Company = line.Substring( 0, 1).Trim(),
Policynum = line.Substring( 1, 9).Trim(),
source = line.Substring( 10, 39).Trim(),
grosswd = line.Substring( 40, 17).Trim(),
wdcharg = line.Substring( 58, 14).Trim(),
netwd = line.Substring( 73, 15).Trim(),
amtwithhld = line.Substring( 85, 15).Trim(),
taxpenalty = line.Substring(101, 18).Trim()
};
}
}
CodePudding user response：
Before we start, it probably isn't a good idea to include quotations in your CSV data, as it makes for an annoyance removing them when opening the data in other programs. Also, including commas in your number values makes CSV formats break a little, which can be a pain. Please don't do that, it's easy to avoid. (if you really must, just add a backslash before the double quotes in the code, it should work fine that way)
Unfortunately, to my knowledge there are no easy to use standard libraries in terms of parsing and writing CSV files, but this problem should be easily solved if you're just looking to write this data. The first thing I would recommend to do when building this program further is to add a ToString() Override method to the Customer class.
(using a class here isn't really necessary either, i'd recommend a struct as it's more readable as a data storage medium) (also, the question marks next to the string identifiers aren't really necessary as strings are already nullable, just letting you know)
public struct Customer
{
public string Company;
public string Policynum;
public string source;
public string grosswd;
public string wdcharg;
public string netwd;
public string amtwithhld;
public string taxpenalty;
public override string ToString()
{
return $"{Company},{Policynum},{source},{grosswd},{wdcharg},{netwd},{amtwithhld},{taxpenalty}";
}
}
After declaring this method, adding the customer information to the file will be a lot easier, call the method on the struct itself when you're appending a line to the file and it'll be a lot easier to manage.
Your method of writing to files is a little archaic, opening and closing the file every time you need to write to it. A simpler way of doing things would be to create a using statement, like so, to automatically close the file when writing is finished.
- I need to add header fields at the top of the output csv file ("company", "policy", etc). I tried to declare them as string as add them to the output file but didn't work.
I'm thinking your problem specified here is caused by the file's permissions disagreeing with you, maybe because it hasn't been opened or closed properly.
The using bracket would end up looking something like this, using a StreamWriter to increase ease of writing to the file.
using (StreamWriter writer = new StreamWriter(csvfilePath))
{
//Write the headers to the file
writer.WriteLine("Company,policy,source,grosswd,wdcharg,netwd,amtwithhld,taxpenalty");
//Write the customer information
foreach (string line in lines)
{
Customer c = new Customer();
c.Company = line.Substring(0, 1).Trim();
c.Policynum = line.Substring(1, 9).Trim();
c.source = line.Substring(10, 39).Trim();
c.grosswd = line.Substring(40, 17).Trim();
c.wdcharg = line.Substring(58, 14).Trim();
c.netwd = line.Substring(73, 15).Trim();
c.amtwithhld = line.Substring(85, 15).Trim();
c.taxpenalty = line.Substring(101, 18).Trim();
customers.Add(c);
writer.WriteLine(c.ToString());
}
}
Hope you find the solution you're looking for.