CSV Reader to edit CSV files

Goldy

Goldy

Goldy

Associate
Joined
30 Mar 2004
Posts
1,148
Location
West Wing
Hi,

Im trying to design and code an application to read a .csv file and take data out and relocate into other csv files.

What currently happens is that each day I receive a CSV file containing around 10 rows, each row containing around 100 values with the first number in that row being a unique reference. I simply want to go through each row and copy that row into its own new .csv file. So each row will eventually have its own file.

I plan on programming this in C# so i understand theres already some handy tools in place for reading and writing CSV files. Can anyone point me in the right direction on how to do this?

Thanks!
 
It's simple file streaming of text files. Quick, dirty and easy method is to read the CSV line by line (usually the first row is column headings), then split the row based on a delimiter (usually CSV is comma seperated) to an array (which will give you a column based array of the row data) and then output and sort the data accordingly to a new file.

If it's .NET then look at the FileStream, string Split and For and While loops.
 
Or skip all of the above and use an existing CsvReader that has been done to death a million times before :p

CSVs can be more complex than just being a comma separated file. Fields could contain commas, for a start, so they will need to be escape, or the fields themselves have encapsulating characters (typically double quotes) which will also need escaping if they are used within the field. :)
 
I would still argue, this is probbaly going to be wayyyy simpler with PowerShell than most of the things you could otherwise use.

Give me an example CSV file, tell me what you want, I will see if I can knock something together for you quickly.
 
Goldy said:
Bit of a noob question, how exactly do I use this parseCSV function?

I've declared the String path as the file location where the file is but not sure how I pass that to the function so it can start running through the rows.

Go to the full function half way down the page:
http://www.switchonthecode.com/tutorials/building-a-simple-csv-parser-in-csharp
Click to expand...

Just something like this:

Code: 
foreach (var line in parseCSV(@"c:\temp\csv.csv"))
{
}


Why not use Lumenworks/CsvHelper that was posted earlier? They're far more robust.
 
Thanks Pho, that sort of worked.

To cut to the chase, here is a sample of the .csv files im working with. This one has 4 rows. I want to go through each row and copy that row to a new file.

Sample file
 
This is not a true Csv file as such, certainly without the header rows, i'd treat it more like a standard text file.

This works for me in PowerShell. I've assumed that the filename to be used is the first value on each row.

Code: 
$CSVFilePath = "C:\Temp\csvExample.csv"
$CSVFile = Get-Content $CSVFilePath

foreach ($Line in $CSVFile)
{
	$FileName = $Line.Split(",")[0]
	$Line | Out-File "C:\Temp\$FileName.csv"
}

Of course this does not consider duplicate file names down the line. If you have a better idea I can see if I can accomodate.
 
Thanks Eulogy. Although I know even less about using Powershell than I do with C#. I will also be including some FTP download and upload and file management functions around this part of the code. Im ok with these bits its just the part that processes the file that I need help with.

If I were to use the StreamReader class in C#, how do i make it read a set number of values before moving onto the next row. My rows will consistently be 101 values. All i can find now are ways of splitting rows by a comma or space which isnt much use.
 
Presumably you're wanting to modify the results rather than just do a straight file copy?

Using CsvReader you can read/write to a CSV file by iterating over each field/row like this (it works for me, anyhow!).

This creates a clone of the original file which I guess does what you need from the above?

Code: 
var csvConfiguration = new CsvConfiguration {HasHeaderRecord = false};

using (var csvWriter = new CsvWriter(new StreamWriter(("csvExample-copy.csv")), csvConfiguration))
{
	using (var csvReader = new CsvReader(new StreamReader(("csvExample.csv")), csvConfiguration))
	{
		while (csvReader.Read())
		{
			for (var fieldIdx = 0; fieldIdx < csvReader.Parser.FieldCount; fieldIdx++)
			{
				var value = csvReader[fieldIdx];
				csvWriter.WriteField(value);
			}

			csvWriter.NextRecord();
		}
	}
}
 
Thanks for the assistance guys. Visibleman hit the nail on the head, it was simply a case of reading the file line by line and delimiting the values by ','. Amazing how quick it can process thousands of lines and append the line to a new file. :)
 
You must log in or register to reply here.
Back
Top Bottom