importing csv to datagrid, adding column data

J

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()+";Extensions=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;
}
 
R

Robson Siqueira

JMO,

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

JMO

Robson,

How would I do that?

JMO

Robson said:
JMO,

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

--
Regards,
Robson Siqueira
Enterprise Architect
JMO said:
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()+";Extensions=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;
}
Click to expand...
Click to expand...
 
J

JMO

Robson,

How would I do that?

JMO

Robson said:
JMO,

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

--
Regards,
Robson Siqueira
Enterprise Architect
JMO said:
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()+";Extensions=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;
}
Click to expand...
Click to expand...
 
R

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 said:
Robson,

How would I do that?

JMO

Robson said:
JMO,

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

--
Regards,
Robson Siqueira
Enterprise Architect
JMO said:
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()+";Extensions=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;
}
Click to expand...
Click to expand...
Click to expand...
 
J

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 said:
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 said:
Robson,

How would I do that?

JMO

Robson said:
JMO,

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

--
Regards,
Robson Siqueira
Enterprise Architect
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()+";Extensions=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;
}
Click to expand...
Click to expand...
Click to expand...
 
R

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/database/FinalCSVReader.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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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 said:
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 said:
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 said:
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
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()+";Extensions=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;
}
Click to expand...
Click to expand...
Click to expand...
 
J

JMO

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

Robson said:
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/database/FinalCSVReader.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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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 said:
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 said:
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
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
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()+";Extensions=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;
}
Click to expand...
Click to expand...
Click to expand...
 
J

JMO

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

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

Robson said:
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/database/FinalCSVReader.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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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 said:
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
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
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()+";Extensions=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;
}
Click to expand...
Click to expand...
Click to expand...
 
R

Robson Siqueira

Yes, before you open the ODBC connection.

--
Regards,
Robson Siqueira
Enterprise Architect
JMO said:
Would I put this at the beginning of the "Function For Importing Data
From CSV File"?
Click to expand...

Robson said:
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/database/FinalCSVReader.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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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 said:
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
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
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()+";Extensions=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;
}
Click to expand...
Click to expand...
Click to expand...
 
J

JMO

Do you knowing about adding Data to specific columns?


Robson said:
Yes, before you open the ODBC connection.

--
Regards,
Robson Siqueira
Enterprise Architect
JMO said:
Would I put this at the beginning of the "Function For Importing Data
From CSV File"?
Click to expand...

Robson said:
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/database/FinalCSVReader.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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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
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
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
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()+";Extensions=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;
}
Click to expand...
Click to expand...
Click to expand...
 
R

Robson Siqueira

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

--
Regards,
Robson Siqueira
Enterprise Architect
JMO said:
Do you knowing about adding Data to specific columns?


Robson said:
Yes, before you open the ODBC connection.

--
Regards,
Robson Siqueira
Enterprise Architect
JMO said:
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/database/FinalCSVReader.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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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
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
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
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()+";Extensions=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;
}
Click to expand...
Click to expand...
Click to expand...
 
J

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.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.ItemArray.GetValue(j) + "' where srno =" +
(i + 1);
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();

}
}

Thanks,
JMO



Robson said:
I know. It depends on what you want to do.

--
Regards,
Robson Siqueira
Enterprise Architect
JMO said:
Do you knowing about adding Data to specific columns?


Robson said:
Yes, before you open the ODBC connection.

--
Regards,
Robson Siqueira
Enterprise Architect
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/database/FinalCSVReader.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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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
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
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
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()+";Extensions=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;
}
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing CSV to Dataset Problem 1
Error importing xml file 5
load .csv data to a datagrid 2
Parsing CSV data into XML 1
Problem while filling the dataset where the data source is a .CSV file 2
adding controls at runtime 2
Hide datagrid column 6
seeking solution for DataGrid, help, help, help, help 8

Top