importing csv to datagrid, adding column data

JMO

I can import a csv file with no problem. I can also add columns to the
datagrid upon import. I want to be able to start importing at the 3rd
row. This will pick up the headers necessary for the datagrid. Once I
can get to that point I need some way to be able to add new data only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use DSN
less connection. The DBQ attribute sets the path of directory which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #1

Robson Siqueira

JMO,

Isn't easier for you to import everything and then remove the 1st three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...

>I can import a csv file with no problem. I can also add columns to the
datagrid upon import. I want to be able to start importing at the 3rd
row. This will pick up the headers necessary for the datagrid. Once I
can get to that point I need some way to be able to add new data only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use DSN
less connection. The DBQ attribute sets the path of directory which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #2

JMO

Robson,

How would I do that?

JMO

Robson Siqueira wrote:

JMO,

Isn't easier for you to import everything and then remove the 1st three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...

I can import a csv file with no problem. I can also add columns to the
datagrid upon import. I want to be able to start importing at the 3rd
row. This will pick up the headers necessary for the datagrid. Once I
can get to that point I need some way to be able to add new data only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use DSN
less connection. The DBQ attribute sets the path of directory which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #3

JMO

Robson,

How would I do that?

JMO

Robson Siqueira wrote:

JMO,

Isn't easier for you to import everything and then remove the 1st three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...

I can import a csv file with no problem. I can also add columns to the
datagrid upon import. I want to be able to start importing at the 3rd
row. This will pick up the headers necessary for the datagrid. Once I
can get to that point I need some way to be able to add new data only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use DSN
less connection. The DBQ attribute sets the path of directory which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #4

Robson Siqueira

You could use this after fill the dataset:
ds.Tables["Stocks"].Rows[0].Delete();

ds.Tables["Stocks"].Rows[1].Delete();

ds.Tables["Stocks"].Rows[2].Delete();
--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegro ups.com...

Robson,

How would I do that?

JMO

Robson Siqueira wrote:

>JMO,

Isn't easier for you to import everything and then remove the 1st three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegr oups.com...

>I can import a csv file with no problem. I can also add columns to the
datagrid upon import. I want to be able to start importing at the 3rd
row. This will pick up the headers necessary for the datagrid. Once I
can get to that point I need some way to be able to add new data only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use DSN
less connection. The DBQ attribute sets the path of directory which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #5

JMO

Its removes the rows but its still using the first row as the header,
how can i get it to use the 3rd row as the header?

JMO
Robson Siqueira wrote:

You could use this after fill the dataset:
ds.Tables["Stocks"].Rows[0].Delete();

ds.Tables["Stocks"].Rows[1].Delete();

ds.Tables["Stocks"].Rows[2].Delete();
--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegro ups.com...

Robson,

How would I do that?

JMO

Robson Siqueira wrote:

JMO,

Isn't easier for you to import everything and then remove the 1st three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
I can import a csv file with no problem. I can also add columns to the
datagrid upon import. I want to be able to start importing at the 3rd
row. This will pick up the headers necessary for the datagrid. Once I
can get to that point I need some way to be able to add new data only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use DSN
less connection. The DBQ attribute sets the path of directory which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #6

Robson Siqueira

Ahhhhh, I think I understood it now.

Probably your file has blank lines on the beginning or something else you
dont' want to use. The code you're using came from
http://www.codeproject.com/cs/databa...lCSVReader.asp. I've read the
article. There is a way to customize certain aspects when you deal with
these files thru ODBC using the schema.ini file
(http://msdn.microsoft.com/library/de..._ini_file.asp).
Therefore, there is no place where you specify where the header starts. Are
you files too big? If they're not, you could do this:

StreamReader sr = new StreamReader("c:\\file.txt");

StreamWriter sw = new StreamWriter("c:\\mynewfile.txt");

for (int i = 1; i < 3; i++)

sr.ReadLine();

while (!sr.EndOfStream)

{

sw.WriteLine(sr.ReadLine());

}

sw.Flush();

sr.Close();

sw.Close();

And then use the new file created. If your file is bigger, you should try
something else.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...

Its removes the rows but its still using the first row as the header,
how can i get it to use the 3rd row as the header?

JMO
Robson Siqueira wrote:

>You could use this after fill the dataset:
ds.Tables["Stocks"].Rows[0].Delete();

ds.Tables["Stocks"].Rows[1].Delete();

ds.Tables["Stocks"].Rows[2].Delete();
--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegr oups.com...

Robson,

How would I do that?

JMO

Robson Siqueira wrote:
JMO,

Isn't easier for you to import everything and then remove the 1st
three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegr oups.com...
I can import a csv file with no problem. I can also add columns to
the
datagrid upon import. I want to be able to start importing at the
3rd
row. This will pick up the headers necessary for the datagrid.
Once I
can get to that point I need some way to be able to add new data
only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use DSN
less connection. The DBQ attribute sets the path of directory which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #7

JMO

Would I put this at the beginning of the "Function For Importing Data

>From CSV File"?

Robson Siqueira wrote:

Ahhhhh, I think I understood it now.

Probably your file has blank lines on the beginning or something else you
dont' want to use. The code you're using came from
http://www.codeproject.com/cs/databa...lCSVReader.asp. I've read the
article. There is a way to customize certain aspects when you deal with
these files thru ODBC using the schema.ini file
(http://msdn.microsoft.com/library/de..._ini_file.asp).
Therefore, there is no place where you specify where the header starts. Are
you files too big? If they're not, you could do this:

StreamReader sr = new StreamReader("c:\\file.txt");

StreamWriter sw = new StreamWriter("c:\\mynewfile.txt");

for (int i = 1; i < 3; i++)

sr.ReadLine();

while (!sr.EndOfStream)

{

sw.WriteLine(sr.ReadLine());

}

sw.Flush();

sr.Close();

sw.Close();

And then use the new file created. If your file is bigger, you should try
something else.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...

Its removes the rows but its still using the first row as the header,
how can i get it to use the 3rd row as the header?

JMO
Robson Siqueira wrote:

You could use this after fill the dataset:
ds.Tables["Stocks"].Rows[0].Delete();

ds.Tables["Stocks"].Rows[1].Delete();

ds.Tables["Stocks"].Rows[2].Delete();
--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegro ups.com...
Robson,

How would I do that?

JMO

Robson Siqueira wrote:
JMO,

Isn't easier for you to import everything and then remove the 1st
three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
I can import a csv file with no problem. I can also add columns to
the
datagrid upon import. I want to be able to start importing at the
3rd
row. This will pick up the headers necessary for the datagrid.
Once I
can get to that point I need some way to be able to add new data
only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use DSN
less connection. The DBQ attribute sets the path of directory which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #8

JMO

What about adding data to specific columns from say another sql
statement based on the 2nd column of data?
JMO wrote:

Would I put this at the beginning of the "Function For Importing Data

From CSV File"?

Robson Siqueira wrote:

Ahhhhh, I think I understood it now.

Probably your file has blank lines on the beginning or something else you
dont' want to use. The code you're using came from
http://www.codeproject.com/cs/databa...lCSVReader.asp. I've read the
article. There is a way to customize certain aspects when you deal with
these files thru ODBC using the schema.ini file
(http://msdn.microsoft.com/library/de..._ini_file.asp).
Therefore, there is no place where you specify where the header starts. Are
you files too big? If they're not, you could do this:

StreamReader sr = new StreamReader("c:\\file.txt");

StreamWriter sw = new StreamWriter("c:\\mynewfile.txt");

for (int i = 1; i < 3; i++)

sr.ReadLine();

while (!sr.EndOfStream)

{

sw.WriteLine(sr.ReadLine());

}

sw.Flush();

sr.Close();

sw.Close();

And then use the new file created. If your file is bigger, you should try
something else.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...

Its removes the rows but its still using the first row as the header,
how can i get it to use the 3rd row as the header?
>
JMO
>
>
Robson Siqueira wrote:
>You could use this after fill the dataset:
>ds.Tables["Stocks"].Rows[0].Delete();
>>
>ds.Tables["Stocks"].Rows[1].Delete();
>>
>ds.Tables["Stocks"].Rows[2].Delete();
>>
>>
>--
>Regards,
>Robson Siqueira
>Enterprise Architect
>"JMO" <j.**********@gmail.comwrote in message
>news:11*********************@v45g2000cwv.googlegr oups.com...
Robson,
>
How would I do that?
>
JMO
>
Robson Siqueira wrote:
>JMO,
>>
>Isn't easier for you to import everything and then remove the 1st
>three
>rows?
>>
>--
>Regards,
>Robson Siqueira
>Enterprise Architect
>"JMO" <j.**********@gmail.comwrote in message
>news:11**********************@38g2000cwa.googlegr oups.com...
>I can import a csv file with no problem. I can also add columns to
>the
datagrid upon import. I want to be able to start importing at the
3rd
row. This will pick up the headers necessary for the datagrid.
Once I
can get to that point I need some way to be able to add new data
only
to the new columns that were added.
>
Here is some of my code:
>
//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string
>
// Create a connection string as below, if you want to use DSN
less connection. The DBQ attribute sets the path of directory which
contains CSV files
>
string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;
>
//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());
>
// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");
>
//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";
>
obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");
>
ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");
>
//Set the datagrid properties
>
dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";
>
//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}
>
>
>

Jan 18 '07 #9

Robson Siqueira

Yes, before you open the ODBC connection.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@38g2000cwa.googlegrou ps.com...

Would I put this at the beginning of the "Function For Importing Data

>>From CSV File"?

Robson Siqueira wrote:

>Ahhhhh, I think I understood it now.

Probably your file has blank lines on the beginning or something else you
dont' want to use. The code you're using came from
http://www.codeproject.com/cs/databa...lCSVReader.asp. I've read the
article. There is a way to customize certain aspects when you deal with
these files thru ODBC using the schema.ini file
(http://msdn.microsoft.com/library/de..._ini_file.asp).
Therefore, there is no place where you specify where the header starts.
Are
you files too big? If they're not, you could do this:

StreamReader sr = new StreamReader("c:\\file.txt");

StreamWriter sw = new StreamWriter("c:\\mynewfile.txt");

for (int i = 1; i < 3; i++)

sr.ReadLine();

while (!sr.EndOfStream)

{

sw.WriteLine(sr.ReadLine());

}

sw.Flush();

sr.Close();

sw.Close();

And then use the new file created. If your file is bigger, you should try
something else.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googleg roups.com...

Its removes the rows but its still using the first row as the header,
how can i get it to use the 3rd row as the header?

JMO
Robson Siqueira wrote:
You could use this after fill the dataset:
ds.Tables["Stocks"].Rows[0].Delete();

ds.Tables["Stocks"].Rows[1].Delete();

ds.Tables["Stocks"].Rows[2].Delete();
--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegr oups.com...
Robson,

How would I do that?

JMO

Robson Siqueira wrote:
JMO,

Isn't easier for you to import everything and then remove the 1st
three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegr oups.com...
I can import a csv file with no problem. I can also add columns
to
the
datagrid upon import. I want to be able to start importing at
the
3rd
row. This will pick up the headers necessary for the datagrid.
Once I
can get to that point I need some way to be able to add new data
only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use
DSN
less connection. The DBQ attribute sets the path of directory
which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #10

JMO

Do you knowing about adding Data to specific columns?
Robson Siqueira wrote:

Yes, before you open the ODBC connection.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@38g2000cwa.googlegrou ps.com...

Would I put this at the beginning of the "Function For Importing Data

>From CSV File"?

Robson Siqueira wrote:

Ahhhhh, I think I understood it now.

Probably your file has blank lines on the beginning or something else you
dont' want to use. The code you're using came from
http://www.codeproject.com/cs/databa...lCSVReader.asp. I've read the
article. There is a way to customize certain aspects when you deal with
these files thru ODBC using the schema.ini file
(http://msdn.microsoft.com/library/de..._ini_file.asp).
Therefore, there is no place where you specify where the header starts.
Are
you files too big? If they're not, you could do this:

StreamReader sr = new StreamReader("c:\\file.txt");

StreamWriter sw = new StreamWriter("c:\\mynewfile.txt");

for (int i = 1; i < 3; i++)

sr.ReadLine();

while (!sr.EndOfStream)

{

sw.WriteLine(sr.ReadLine());

}

sw.Flush();

sr.Close();

sw.Close();

And then use the new file created. If your file is bigger, you should try
something else.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...
Its removes the rows but its still using the first row as the header,
how can i get it to use the 3rd row as the header?

JMO
Robson Siqueira wrote:
You could use this after fill the dataset:
ds.Tables["Stocks"].Rows[0].Delete();

ds.Tables["Stocks"].Rows[1].Delete();

ds.Tables["Stocks"].Rows[2].Delete();
--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegro ups.com...
Robson,

How would I do that?

JMO

Robson Siqueira wrote:
JMO,

Isn't easier for you to import everything and then remove the 1st
three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
I can import a csv file with no problem. I can also add columns
to
the
datagrid upon import. I want to be able to start importing at
the
3rd
row. This will pick up the headers necessary for the datagrid.
Once I
can get to that point I need some way to be able to add new data
only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use
DSN
less connection. The DBQ attribute sets the path of directory
which
contains CSV files

string strConnString="Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 18 '07 #11

Robson Siqueira

I know. It depends on what you want to do.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...

Do you knowing about adding Data to specific columns?
Robson Siqueira wrote:

>Yes, before you open the ODBC connection.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@38g2000cwa.googlegro ups.com...

Would I put this at the beginning of the "Function For Importing Data
From CSV File"?

Robson Siqueira wrote:
Ahhhhh, I think I understood it now.

Probably your file has blank lines on the beginning or something else
you
dont' want to use. The code you're using came from
http://www.codeproject.com/cs/databa...lCSVReader.asp. I've read
the
article. There is a way to customize certain aspects when you deal
with
these files thru ODBC using the schema.ini file
(http://msdn.microsoft.com/library/de..._ini_file.asp).
Therefore, there is no place where you specify where the header
starts.
Are
you files too big? If they're not, you could do this:

StreamReader sr = new StreamReader("c:\\file.txt");

StreamWriter sw = new StreamWriter("c:\\mynewfile.txt");

for (int i = 1; i < 3; i++)

sr.ReadLine();

while (!sr.EndOfStream)

{

sw.WriteLine(sr.ReadLine());

}

sw.Flush();

sr.Close();

sw.Close();

And then use the new file created. If your file is bigger, you should
try
something else.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googleg roups.com...
Its removes the rows but its still using the first row as the
header,
how can i get it to use the 3rd row as the header?

JMO
Robson Siqueira wrote:
You could use this after fill the dataset:
ds.Tables["Stocks"].Rows[0].Delete();

ds.Tables["Stocks"].Rows[1].Delete();

ds.Tables["Stocks"].Rows[2].Delete();
--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegr oups.com...
Robson,

How would I do that?

JMO

Robson Siqueira wrote:
JMO,

Isn't easier for you to import everything and then remove the
1st
three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegr oups.com...
I can import a csv file with no problem. I can also add
columns
to
the
datagrid upon import. I want to be able to start importing at
the
3rd
row. This will pick up the headers necessary for the
datagrid.
Once I
can get to that point I need some way to be able to add new
data
only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use
DSN
less connection. The DBQ attribute sets the path of directory
which
contains CSV files

string strConnString="Driver={Microsoft Text Driver
(*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new
System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 20 '07 #12

JMO

I have a CSV file with 15 columns in it. I know the sample project was
setup for just 2 columns. I am only getting 3 columns of data showing.
If I edit the 14 in this line...for (int j = 1; j <=
da.Tables["Stocks"].Columns.Count - 14; j++) to 13 I gain an additional
column of data but the rows duplicate once. As I decrease the 14 one by
one, each number I decrease by is how many duplicates of the rows I
get. Can someone help me out on this?

// Now we will collect data from data table and insert it into database
one by one
// Initially there will be no data in database so we will insert data
in first two columns
// and after that we will update data in same row for remaining columns
// The logic is simple. 'i' represents rows while 'j' represents
columns

cmd.Connection = con1;
cmd.CommandType = CommandType.Text;
cmd1.Connection = con1;
cmd1.CommandType = CommandType.Text;

con1.Open();
for (int i = 0; i <= da.Tables["Stocks"].Rows.Count - 1; i++)
{

for (int j = 1; j <= da.Tables["Stocks"].Columns.Count - 14; j++)
{

cmd.CommandText = "Insert into Test (srno, " +
da.Tables["Stocks"].Columns[0].ColumnName.Trim() + ") values(" + (i +
1) + ",'" + da.Tables["Stocks"].Rows[i].ItemArray.GetValue(0) + "')";

// For UPDATE statement, in where clause you need some unique row
//identifier. We are using 'srno' in WHERE clause.
cmd1.CommandText = "Update Test set " +
da.Tables["Stocks"].Columns[j].ColumnName.Trim() + " = '" +
da.Tables["Stocks"].Rows[i].ItemArray.GetValue(j) + "' where srno =" +
(i + 1);
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();

}
}

Thanks,
JMO

Robson Siqueira wrote:

I know. It depends on what you want to do.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...

Do you knowing about adding Data to specific columns?
Robson Siqueira wrote:

Yes, before you open the ODBC connection.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@38g2000cwa.googlegrou ps.com...
Would I put this at the beginning of the "Function For Importing Data
From CSV File"?

Robson Siqueira wrote:
Ahhhhh, I think I understood it now.

Probably your file has blank lines on the beginning or something else
you
dont' want to use. The code you're using came from
http://www.codeproject.com/cs/databa...lCSVReader.asp. I've read
the
article. There is a way to customize certain aspects when you deal
with
these files thru ODBC using the schema.ini file
(http://msdn.microsoft.com/library/de..._ini_file.asp).
Therefore, there is no place where you specify where the header
starts.
Are
you files too big? If they're not, you could do this:

StreamReader sr = new StreamReader("c:\\file.txt");

StreamWriter sw = new StreamWriter("c:\\mynewfile.txt");

for (int i = 1; i < 3; i++)

sr.ReadLine();

while (!sr.EndOfStream)

{

sw.WriteLine(sr.ReadLine());

}

sw.Flush();

sr.Close();

sw.Close();

And then use the new file created. If your file is bigger, you should
try
something else.

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...
Its removes the rows but its still using the first row as the
header,
how can i get it to use the 3rd row as the header?

JMO
Robson Siqueira wrote:
You could use this after fill the dataset:
ds.Tables["Stocks"].Rows[0].Delete();

ds.Tables["Stocks"].Rows[1].Delete();

ds.Tables["Stocks"].Rows[2].Delete();
--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegro ups.com...
Robson,

How would I do that?

JMO

Robson Siqueira wrote:
JMO,

Isn't easier for you to import everything and then remove the
1st
three
rows?

--
Regards,
Robson Siqueira
Enterprise Architect
"JMO" <j.**********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
I can import a csv file with no problem. I can also add
columns
to
the
datagrid upon import. I want to be able to start importing at
the
3rd
row. This will pick up the headers necessary for the
datagrid.
Once I
can get to that point I need some way to be able to add new
data
only
to the new columns that were added.

Here is some of my code:

//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or
connection string

// Create a connection string as below, if you want to use
DSN
less connection. The DBQ attribute sets the path of directory
which
contains CSV files

string strConnString="Driver={Microsoft Text Driver
(*.txt;
*.csv)};Dbq="+txtCSVFolderPath.Text.Trim()+";Exten sions=asc,csv,tab,txt;Persist
Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;

//Create connection to CSV file
conn = new
System.Data.Odbc.OdbcConnection(strConnString.Trim ());

// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");

//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from ["+ filetable +"]";

obj_oledb_da = new
System.Data.Odbc.OdbcDataAdapter(sql_select,conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds,"Stocks");
ds.Tables["Stocks"].Columns.Add("Name");

ds.Tables["Stocks"].Columns.Add("Description");
ds.Tables["Stocks"].Columns.Add("CUSIP");
ds.Tables["Stocks"].Columns.Add("SEDOL1");
ds.Tables["Stocks"].Columns.Add("ISIN");

//Set the datagrid properties

dGridCSVdata.DataSource=ds;
dGridCSVdata.DataMember="Stocks";

//Close Connection to CSV file
conn.Close();
}
catch(Exception e) //Error
{
MessageBox.Show(e.Message);
}
return ds;
}

Jan 22 '07 #13