Importing CSV File - Text is losing its value

[Guest]

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=Microsoft.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());
}

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

If you know for sure it will be csv use the provider from opennetcf.org

 

[Steve Barnett]

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

 

[Guest]

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.

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

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=Microsoft.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());
}

 

[Ignacio Machin \( .NET/ C# MVP \)]

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

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.

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

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=Microsoft.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());
}

 

[sloan]

..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??