Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
C# -- remove comma when reading TXT file ?
Posted on 2011-03-04
The below code reads in my below "John Doe" variance.txt correctly.
NAME,FILE#,SSN,CHECKVIEWPA
"John Doe",229999,9263,12/06/201
"Jane Doe",181,6331,12/06/2010,4
"John Smith",1041,1207,12/06/201
--------------------------
How can I change the code to remove the , or replacing it with
a space or something so it reads in the below "John, Doe"
as one column ?
NAME,FILE#,SSN,CHECKVIEWPA
"John, Doe",229999,9263,12/06/201
"Jane, Doe",181,6331,12/06/2010,4
"John, Smith",1041,1207,12/06/201
--------------------------
private DataSet ReadFile(string filePath)
{
OleDbConnection conn = new OleDbConnection("Provider=
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName
DataSet ds = new DataSet("Temp");
adapter.Fill(ds);
return ds;
}
--------------------------
public bool ImportFileVariance()
{
string filePath = @"C:\import\variance.txt";
DataSet ds = ReadFile(filePath);
return dbAccess.ImportVarianceRec
}
--------------------------
NAME,FILE#,SSN,CHECKVIEWPA
"John Doe",229999,9263,12/06/201
"Jane Doe",181,6331,12/06/2010,4
"John Smith",1041,1207,12/06/201
--------------------------
How can I change the code to remove the , or replacing it with
a space or something so it reads in the below "John, Doe"
as one column ?
NAME,FILE#,SSN,CHECKVIEWPA
"John, Doe",229999,9263,12/06/201
"Jane, Doe",181,6331,12/06/2010,4
"John, Smith",1041,1207,12/06/201
--------------------------
private DataSet ReadFile(string filePath)
{
OleDbConnection conn = new OleDbConnection("Provider=
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName
DataSet ds = new DataSet("Temp");
adapter.Fill(ds);
return ds;
}
--------------------------
public bool ImportFileVariance()
{
string filePath = @"C:\import\variance.txt";
DataSet ds = ReadFile(filePath);
return dbAccess.ImportVarianceRec
}
--------------------------
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
8
3-
2
- +3
17 Comments
Read the text into a string.
string txtFileContents = "";
// execute the code to open the file and serialize it to a string.
//Now use .Replace()
txtFileContents = txtFileContents.Replace(",", "_"); // the underscore can be a space or anything you want.
Active today
Hi finance_teacher;
This code snippet should give you the results you want.
Fernando
This code snippet should give you the results you want.
// Reading data from a text file.
string data = System.IO.File.ReadAllText( "TestData.txt" );
// Remove all commas that are in between quotes
data = System.Text.RegularExpressions.Regex.Replace( data, @"(\x22.*)(,)(.*\x22)", "$1$3" );
Fernando
You could also use a Split() function.
Basically, do something like this:
string[] parts = curLine.Split(',');
That should give you an array of every field. Then you can manipulate your fields the way you want to.
Basically, do something like this:
string[] parts = curLine.Split(',');
That should give you an array of every field. Then you can manipulate your fields the way you want to.
Active 6 days ago
How can I get the below "data" string into my "ds" DataSet ?
public bool ImportFileVariance()
{
string filePath = @"C:\import\variance.txt";
string data = System.IO.File.ReadAllText
//below works, replacing , with space
data = System.Text.RegularExpress
//errors when running below step
DataSet ds = ReadFile(data);
return dbAccess.ImportVarianceRec
}
public bool ImportFileVariance()
{
string filePath = @"C:\import\variance.txt";
string data = System.IO.File.ReadAllText
//below works, replacing , with space
data = System.Text.RegularExpress
//errors when running below step
DataSet ds = ReadFile(data);
return dbAccess.ImportVarianceRec
}
What does your original CSV text file look like (your question is a little unclear):
Doe,John,1234,123-45-6789,
John,Doe,1234,123-45-6789,
John Doe,1234,123-45-6789, etc
"Doe", "John",1234,123-45-6789, etc
"Doe, John",1234,123-45-6789, etc
"John", "Doe",1234,123-45-6789, etc
"John, Doe",1234,123-45-6789, etc
And to be clear you want to "transform" it like below?
"John Doe",1234,123-45-6789, etc
Doe,John,1234,123-45-6789,
John,Doe,1234,123-45-6789,
John Doe,1234,123-45-6789, etc
"Doe", "John",1234,123-45-6789, etc
"Doe, John",1234,123-45-6789, etc
"John", "Doe",1234,123-45-6789, etc
"John, Doe",1234,123-45-6789, etc
And to be clear you want to "transform" it like below?
"John Doe",1234,123-45-6789, etc
Ahh, sorry - didn't notice you were using the OleDb provider to read the source file. Try this ReadFile() method:
static DataSet ReadFile(string filePath)
{
string connectionString = String.Format(
@"Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Extended Properties=""Text;HDR=Yes;FMT=Delimited""",
System.IO.Path.GetDirectoryName(filePath));
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
string command = String.Format(
@"SELECT * FROM {0}", System.IO.Path.GetFileName(filePath));
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command, conn))
{
DataSet ds = new DataSet("Temp");
adapter.Fill(ds);
// Loop through the data table and change names like "Doe,John" to "John Doe"
foreach (DataRow row in ds.Tables[0].Rows)
row[0] = Regex.Replace((string)row[0], @"([^,]*),([^,]*)", "$2 $1");
return ds;
}
}
In my quick test I was using it as a static method, you don't necessarily have to do that in your project - change "static" to "private" to keep it the way you had it (I also missed the closing curly-brace when I copy/pasted).
And I didn't show it, but you'll need a using System.Text.RegularExpress
Active today
Hi finance_teacher;
To your last question. You have a couple of options, 1 save the modified data to the file system and then use your code. 2 create the data table in code as shown in the code snippet below.
Fernando
To your last question. You have a couple of options, 1 save the modified data to the file system and then use your code. 2 create the data table in code as shown in the code snippet below.
private void button1_Click( object sender, EventArgs e )
{
string data = System.IO.File.ReadAllText("TestData.txt" );
data = System.Text.RegularExpressions.Regex.Replace( data, @"(\x22.*)(,)(.*\x22)", "$1$3" );
DataTable dt = CtreateTable( data );
dataGridView1.DataSource = dt;
}
private DataTable CtreateTable( string data )
{
string[] dataRows = data.Split( "\r\n".ToCharArray( ), StringSplitOptions.RemoveEmptyEntries );
DataTable dt = new DataTable( );
// First line has the column headders
foreach( string headder in dataRows[ 0 ].Split( ",".ToCharArray( ) ) )
{
dt.Columns.Add( new DataColumn( headder ) );
}
// Load the data
for( int idx = 1; idx < dataRows.Length; idx++)
{
string[] fields = dataRows[ idx ].Split( ",".ToCharArray( ) );
int column = 0;
DataRow dr = dt.NewRow( );
foreach( string field in fields )
{
dr[ column ] = field.Trim("\"".ToCharArray());
column++;
}
dt.Rows.Add( dr );
}
return dt;
}
Fernando
I think the below line would incorrectly split "Doe,John" into two fields:
If you wanted to go that route I think I would use StreamReader.ReadLine (as opposed to looping through a string[]), and I think you would need to use Regex.Replace(dataRows[idx
string[] fields = dataRows[ idx ].Split( ",".ToCharArray( ) );
If you wanted to go that route I think I would use StreamReader.ReadLine (as opposed to looping through a string[]), and I think you would need to use Regex.Replace(dataRows[idx
Active today
@ tgerbert;
This is the code that tales care of the commas in "Doe, John" field.
this code correctly splits the filed in each line. At this point "Doe, John" is "Doe John" without comma.
Fernando
This is the code that tales care of the commas in "Doe, John" field.
data = System.Text.RegularExpressions.Regex.Replace( data, @"(\x22.*)(,)(.*\x22)", "$1$3" )
this code correctly splits the filed in each line. At this point "Doe, John" is "Doe John" without comma.
string[] fields = dataRows[ idx ].Split( ",".ToCharArray( ) );
Fernando
Ah, I missed that it was in the other method.
I still would use Regex.Replace(data, @"[""]([^""]*),([^""]*)[""
I still would use Regex.Replace(data, @"[""]([^""]*),([^""]*)[""
Following code should help -
public System.Data.DataSet LoadData()
{
string path = @"C:\a.txt";
string fileContents = System.IO.File.ReadAllText(path);
fileContents = System.Text.RegularExpressions.Regex.Replace(fileContents, @"(\x22.*)(,)(.*\x22)", "$1$3");
System.IO.File.WriteAllText(path,fileContents.ToString());
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(path) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
conn.Open();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName(path), conn);
System.Data.DataSet ds = new System.Data.DataSet("Temp");
adapter.Fill(ds);
return ds;
}
That Regex statement above leaves the name as DoeJohn instead of John Doe. That approach also requires a read from disk (line 5), write to disk (line 8) and another read from disk (line 14).
In my comment above http:#35039195 I'm only performing one disk operation, which is to read the file with the adapter.Fill(ds) statement, subsequent operations are done on in-memory data.
I suppose both approaches have their merits, depending on the desired end result - but it's not necessarily uncommon to see text data files up to the tens of thousands of records; and reading, writing then reading a file that big will be noticeably slower than just reading it. With small files, to the hundreds of lines, I would imagine there wouldn't be a perceivable impact on performance one way or the other.
In my comment above http:#35039195 I'm only performing one disk operation, which is to read the file with the adapter.Fill(ds) statement, subsequent operations are done on in-memory data.
I suppose both approaches have their merits, depending on the desired end result - but it's not necessarily uncommon to see text data files up to the tens of thousands of records; and reading, writing then reading a file that big will be noticeably slower than just reading it. With small files, to the hundreds of lines, I would imagine there wouldn't be a perceivable impact on performance one way or the other.
Thats true, it is just sample code. if somebody is loading data from txt file and processing million records then ETL tool is best but not big fat .Net dataset. I assume here the data set is for showing in grid in this context expected to have hundreds of records.
the sample code illustrates solution for the given problem but not the best pattern, there could be tons of patterns to follow depends on the requirement.
the sample code illustrates solution for the given problem but not the best pattern, there could be tons of patterns to follow depends on the requirement.
Like I said, both approaches have their pros and cons, but the regular expression is still wrong regardless. The asker specified "LastName,FirstName" should become "FirstName LastName" (http:#a35039194). Given an input string of "LastName,FirstName" the statement Regex.Replace(fileContents
string sample = @"""Name"",""Street Address""";
Regex.Replace(sample, @"(\x22.*)(,)(.*\x22)", "$1$3"); // "Name""Street Address"
Regex.Replace(sample, @"[""]([^""]*),([^""]*)[""']($|,)", "$2 $1$3"); // "Name","Street Address"
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…
- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 10 hours left to enroll
634 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.