469,625 Members | 1,086 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

Importing CSV File - Text is losing its value

Hello All:

I am importing an Excel / CSV file. The problem I am having is: the
columns are being defined for me as int32 (able to determine by using the
..GetFieldType method). As a result: when a row has a character, we are
losing this data, because it is being interprested as "". How would I go
about making the columns always text? Below is my code that I am using:

System.Data.OleDb.OleDbConnection con = new
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
Source=" + System.IO.Path.GetDirectoryName(fileName) + ";Extended
Properties=\"text;HDR=Yes;IMEX=1;FMT=Delimited\"") ;

System.Data.OleDb.OleDbCommand command = new
System.Data.OleDb.OleDbCommand("Select * from " +
System.IO.Path.GetFileName(fileName), con);
con.Open();
System.Data.IDataReader dr = command.ExecuteReader();

while (dr.Read())
{
if(dr[0].ToString()!="")
if(!CSVvalues.ContainsKey(dr[0].ToString()))
CSVvalues.Add(dr[0].ToString(), dr[1].ToString());
}

Aug 23 '06 #1
5 5722
Hi,

If you know for sure it will be csv use the provider from opennetcf.org
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Andy" <An**@discussions.microsoft.comwrote in message
news:A2**********************************@microsof t.com...
Hello All:

I am importing an Excel / CSV file. The problem I am having is: the
columns are being defined for me as int32 (able to determine by using the
.GetFieldType method). As a result: when a row has a character, we are
losing this data, because it is being interprested as "". How would I go
about making the columns always text? Below is my code that I am using:

System.Data.OleDb.OleDbConnection con = new
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
Source=" + System.IO.Path.GetDirectoryName(fileName) + ";Extended
Properties=\"text;HDR=Yes;IMEX=1;FMT=Delimited\"") ;

System.Data.OleDb.OleDbCommand command = new
System.Data.OleDb.OleDbCommand("Select * from " +
System.IO.Path.GetFileName(fileName), con);
con.Open();
System.Data.IDataReader dr = command.ExecuteReader();

while (dr.Read())
{
if(dr[0].ToString()!="")
if(!CSVvalues.ContainsKey(dr[0].ToString()))
CSVvalues.Add(dr[0].ToString(), dr[1].ToString());
}

Aug 23 '06 #2
No solution, I'm afraid, but you could save yourself a lot of grief and have
a look at:
http://www.codeproject.com/cs/database/CsvReader.asp

You get the CSV records back as string arrays, so you can do whatever you
need to in order to process them. It's also VERY fast.

Steve
"Andy" <An**@discussions.microsoft.comwrote in message
news:A2**********************************@microsof t.com...
Hello All:

I am importing an Excel / CSV file. The problem I am having is: the
columns are being defined for me as int32 (able to determine by using the
.GetFieldType method). As a result: when a row has a character, we are
losing this data, because it is being interprested as "". How would I go
about making the columns always text? Below is my code that I am using:

System.Data.OleDb.OleDbConnection con = new
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
Source=" + System.IO.Path.GetDirectoryName(fileName) + ";Extended
Properties=\"text;HDR=Yes;IMEX=1;FMT=Delimited\"") ;

System.Data.OleDb.OleDbCommand command = new
System.Data.OleDb.OleDbCommand("Select * from " +
System.IO.Path.GetFileName(fileName), con);
con.Open();
System.Data.IDataReader dr = command.ExecuteReader();

while (dr.Read())
{
if(dr[0].ToString()!="")
if(!CSVvalues.ContainsKey(dr[0].ToString()))
CSVvalues.Add(dr[0].ToString(), dr[1].ToString());
}

Aug 23 '06 #3
We would prefer to not have to use a third-party tool when the functionality
has to be there in .Net. We just need to know how to do it as opposed to
having to maintain a third party component for something as simple as reading
a .csv file.

Thanks though for your help.

"Ignacio Machin ( .NET/ C# MVP )" wrote:
Hi,

If you know for sure it will be csv use the provider from opennetcf.org
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Andy" <An**@discussions.microsoft.comwrote in message
news:A2**********************************@microsof t.com...
Hello All:

I am importing an Excel / CSV file. The problem I am having is: the
columns are being defined for me as int32 (able to determine by using the
.GetFieldType method). As a result: when a row has a character, we are
losing this data, because it is being interprested as "". How would I go
about making the columns always text? Below is my code that I am using:

System.Data.OleDb.OleDbConnection con = new
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
Source=" + System.IO.Path.GetDirectoryName(fileName) + ";Extended
Properties=\"text;HDR=Yes;IMEX=1;FMT=Delimited\"") ;

System.Data.OleDb.OleDbCommand command = new
System.Data.OleDb.OleDbCommand("Select * from " +
System.IO.Path.GetFileName(fileName), con);
con.Open();
System.Data.IDataReader dr = command.ExecuteReader();

while (dr.Read())
{
if(dr[0].ToString()!="")
if(!CSVvalues.ContainsKey(dr[0].ToString()))
CSVvalues.Add(dr[0].ToString(), dr[1].ToString());
}


Aug 23 '06 #4
Hi,

You can have the source code and just customize it to your need.

It will be a couple of methods at the most to just read the csv and have a
string[]

IIRC the problem with using the OleDB is that it looks at the top X rows to
decide the type of the columns

The solution I offfered you just return a string[] that you can convert to
your need.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Andy" <An**@discussions.microsoft.comwrote in message
news:FA**********************************@microsof t.com...
We would prefer to not have to use a third-party tool when the
functionality
has to be there in .Net. We just need to know how to do it as opposed to
having to maintain a third party component for something as simple as
reading
a .csv file.

Thanks though for your help.

"Ignacio Machin ( .NET/ C# MVP )" wrote:
>Hi,

If you know for sure it will be csv use the provider from opennetcf.org
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Andy" <An**@discussions.microsoft.comwrote in message
news:A2**********************************@microso ft.com...
Hello All:

I am importing an Excel / CSV file. The problem I am having is: the
columns are being defined for me as int32 (able to determine by using
the
.GetFieldType method). As a result: when a row has a character, we
are
losing this data, because it is being interprested as "". How would I
go
about making the columns always text? Below is my code that I am
using:

System.Data.OleDb.OleDbConnection con = new
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
Source=" + System.IO.Path.GetDirectoryName(fileName) + ";Extended
Properties=\"text;HDR=Yes;IMEX=1;FMT=Delimited\"") ;

System.Data.OleDb.OleDbCommand command = new
System.Data.OleDb.OleDbCommand("Select * from " +
System.IO.Path.GetFileName(fileName), con);
con.Open();
System.Data.IDataReader dr = command.ExecuteReader();

while (dr.Read())
{
if(dr[0].ToString()!="")
if(!CSVvalues.ContainsKey(dr[0].ToString()))
CSVvalues.Add(dr[0].ToString(),
dr[1].ToString());
}



Aug 23 '06 #5

..GetValue retrieves an object.
You could then cast as a string
object o = dataReader.GetValue();

string s = Convert.ToString(o); // or .ToString() method.

...

Or does that not work , because something is happening on the front side of
it??

"Andy" <An**@discussions.microsoft.comwrote in message
news:A2**********************************@microsof t.com...
Hello All:

I am importing an Excel / CSV file. The problem I am having is: the
columns are being defined for me as int32 (able to determine by using the
.GetFieldType method). As a result: when a row has a character, we are
losing this data, because it is being interprested as "". How would I go
about making the columns always text? Below is my code that I am using:

System.Data.OleDb.OleDbConnection con = new
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
Source=" + System.IO.Path.GetDirectoryName(fileName) + ";Extended
Properties=\"text;HDR=Yes;IMEX=1;FMT=Delimited\"") ;

System.Data.OleDb.OleDbCommand command = new
System.Data.OleDb.OleDbCommand("Select * from " +
System.IO.Path.GetFileName(fileName), con);
con.Open();
System.Data.IDataReader dr = command.ExecuteReader();

while (dr.Read())
{
if(dr[0].ToString()!="")
if(!CSVvalues.ContainsKey(dr[0].ToString()))
CSVvalues.Add(dr[0].ToString(), dr[1].ToString());
}

Aug 23 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by expect | last post: by
11 posts views Thread by Grim Reaper | last post: by
reply views Thread by Kucho | last post: by
2 posts views Thread by Debbiedo | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.