Re: Reading from excel.. - C# Discussion Boards

Re: Reading from excel.. [modified]

23-Apr-09 10:44

I am having a strange issue with reading from Excel with C#

Here is my code:

string excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", txtExcel.Text);

OleDbConnection con = new OleDbConnection(excelConnectionString);
con.Open();
OleDbDataAdapter da = new OleDbDataAdapter("select * from ["+ txtFY.Text +"$]", con);
fy.Text = txtFY.Text;

DataTable dt = new DataTable();
da.Fill(dt);

con.Close();

dataGridView1.DataSource = dt;
dataGridView1.Refresh();

Now it works.. its just some columns that have information in the "actual" excel sheet doesn't appear in the data grid view..

Example:

Here is a column (Serial Column):
136WFW7552
136WFW7556
136WFW7494
136WFW7553
136WFW7492
70101135
71000640
70202250
70101234
70101230

Now when the numbers go from 136WFW7492 to 70101135, it doesn't put the 70101135 numbers in the datagridview... I've removed formatting from the workboot and everything.. I'm confused

fly90423-Apr-09 11:27

23-Apr-09 11:27

What data/columns are in the raw DataTable? Is it what is to be expected?

Try putting HDR=Yes; in your connections string. I can't remember off-hand what the default is.

Edit:

This article pretty much sums it up. Reading and Writing Excel using OLEDB[^]

hmmm pie

modified on Friday, April 24, 2009 5:18 AM

Jacob Dixon24-Apr-09 13:38

24-Apr-09 13:38

I've tried that document and it doesn't seem to work.. Any other ideas?

fly90424-Apr-09 14:08

24-Apr-09 14:08

There's always the alternative way of Opening the file itself. Have a look at this example[^]

Also have a look at this: Opening and Navigating Excel with C#[^]

It's not ideal but it's a work around.

My failometer is detecting vast quantities of FAIL!

fly90424-Apr-09 14:48

24-Apr-09 14:48

Im not sure if it will work or not but you may be able to select the columns using GetOleDbSchemaTable().

Then use those columns in the select query,

private string[] _GetColumnsFromWorkseet(string sheetname, out string errorMessage)
{
    try
    {
        List<string> ls = new List<string>();

        DataTable dtCols = _oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                new Object[] { null, null, sheetname, null });

        foreach (DataRow dr in dtCols.Select(string.Empty, "ORDINAL_POSITION"))
            ls.Add(dr["COLUMN_NAME"].ToString());

        errorMessage = string.Empty;
        return ls.ToArray();
    }
    catch (Exception ex)
    {
        errorMessage = ex.Message;
        return null;
    }
}

My failometer is detecting vast quantities of FAIL!

modified on Friday, April 24, 2009 10:20 PM

Jacob Dixon24-Apr-09 14:50

24-Apr-09 14:50

I actually got it to work on that article. I changed the reg key, turned HDR to YES, but what I had to do was put in IMEX=1 which I don't think was in that document.

Ashfield23-Apr-09 20:56

Ashfield

23-Apr-09 20:56

Its aknown problem using oledb to read from Excel. It decides the column data type from the first (I think) 8 rows, and any subsequent data that does not match the data type is ignored.

I can see the sense if the column looks like a number and later you get text, but not the other way round. Anyway, thats how it works. It has caught me out before.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Jacob Dixon24-Apr-09 13:38

24-Apr-09 13:38

I tried modifying from that article and it doesn't work.. any other ideas?

how to select mulitiple files for upload

Ashfield25-Apr-09 7:34

Ashfield

25-Apr-09 7:34

I just opened excel from within my app, saved the data as csv and rea dit from there.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

T_Teef23-Apr-09 10:10

T_Teef

23-Apr-09 10:10

hui guyz i am working on an ftp client which is working quite properly but have an issue it only uploads and downloads files one by one, so plz help. Smile | :)

Re: how to select mulitiple files for upload

Christian Graus23-Apr-09 10:21

Christian Graus

23-Apr-09 10:21

The answer is obvious - you need to select multiple files and upload them in a loop. You can't FTP more than one file at once.

Christian Graus

Driven to the arms of OSX by Vista.

"I am new to programming world. I have been learning c# for about past four weeks. I am quite acquainted with the fundamentals of c#. Now I have to work on a project which converts given flat files to XML using the XML serialization method" - SK64 ( but the forums have stuff like this posted every day )

Process.Start is eating spaces

Miszou23-Apr-09 10:06

Miszou

23-Apr-09 10:06

I am trying to launch a Google search for a specific string from with my application, and this is all working perfectly, until the search string contains quotes. If there are quotes in the search string, then all the spaces inside the string are removed.

In the debugger, the string looks like this:

http://www.google.com/search?hl=en&q="Just For Laughs Comedy Tour"

But when Google gets it, the URL looks like this:

http://www.google.com/search?hl=en&q="JustForLaughsComedyTour"

If there are no quotes around the search string, then it works perfectly. Running it from the command line doesn't work at all unless I remove the quotes around the search string and place them around the entire command.

The code I am using is here:

System.Diagnostics.ProcessStartInfo pInfo = new System.Diagnostics.ProcessStartInfo(strCmd, strArgs);<br />
pInfo.Verb = "open";<br />
pInfo.ErrorDialog = true;<br />
pInfo.WorkingDirectory = strDir;<br />
System.Diagnostics.Process.Start(pInfo);

I realize that I could probably UrlEncode the string or something, but I am using a generic application launcher thingie that doesn't know anything about the application it is launching - it just calls start on whatever string you pass it. Actually, it is a version of the Visual Studio "External Tools" manager that I wrote[^] as a .NET control, so I don't want it to have to treat URLs differently to standard commands, and I don't want the command to be "IExplore.exe" either. I want it to launch the users default browser based on a URL that I give it.

Does anyone know how to workaround this quoted search string weirdness, without having to resort to a special case to fix it?

Thanks!

The StartPage Randomizer - The Windows Cheerleader - Twitter

Re: Process.Start is eating spaces

fly90423-Apr-09 12:04

Re: Process.Start is eating spaces

23-Apr-09 12:04

Did you actually try to google that?

Replace ' ' with '+'.

http://www.google.co.uk/search?q="Just+For+Laughs+Comedy+Tour"[^]

hmmm pie

Miszou23-Apr-09 13:23

Miszou

23-Apr-09 13:23

fly904 wrote:
Replace ' ' with '+'.

I can't, because the code that launches the command doesn't know what sort of command it is. In other words, as far as the launcher knows, these two strings are equivalent and should be treated exactly the same:

http://www.google.co.uk/search?q="Just For Laughs Comedy Tour"

notepad "Just For Laughs Comedy Tour"

Interestingly, if you paste the following command into the Run box in Windows XP it doesn't work either, but if you paste it directly into your browser it works just fine. This leads me to think there's something strange going on inside Windows somwehere.

http://www.google.co.uk/search?q="Just For Laughs Comedy Tour"

And just for laughs, try these too:

firefox http://www.google.com/search?hl=en&q="Just For Laughs Comedy Tour"

iexplore http://www.google.com/search?hl=en&q="Just For Laughs Comedy Tour"

They both work as expected.

The StartPage Randomizer - The Windows Cheerleader - Twitter

Re: Process.Start is eating spaces

fly90423-Apr-09 15:23

Cannot connect into SQL Server using C#

23-Apr-09 15:23

I see your point now after testing it.

The only way I found was to 'double escape' the speech marks.

I just tried this and it worked perfectly after opening in my default browser (firefox).

string searchString = "\\\"Just For Laughs Comedy Tour\\\"";
System.Diagnostics.ProcessStartInfo pInfo = new System.Diagnostics.ProcessStartInfo("http://www.google.co.uk/search?q=" +
                                     searchString.Replace(" ", "+"));
pInfo.Verb = "open";
pInfo.ErrorDialog = true;
System.Diagnostics.Process.Start(pInfo);

There's a part of me which hate's seeing spaces in URL's.

hmmm pie

url issue

Zafar Sultan23-Apr-09 7:18

Zafar Sultan

23-Apr-09 7:18

I have a webpage displaying some information based on the querystring.
i.e., http://www.mywebsite.com/userdetails.aspx?userid=15510
The issue is, i want to access the information of the user while giving the url as http://www.mywebsite.com/15510
I don't know how to do that.Can anyone suggest how to achieve it?
TIA.

Re: url issue

Christian Graus23-Apr-09 11:02

Christian Graus

23-Apr-09 11:02

TWo things

1 - what does this have to do with C#
2 - HTTPHandler.

Gindi Bar Yahav23-Apr-09 6:46

Gindi Bar Yahav

23-Apr-09 6:46

Bey guys,
i have a little problem, so if you can help me ill thank you...
i built a Sql server using Visual Stuido 2008 ( it created me a file name my_database.sdf ). now i am tring to connect to the sql server i have created,

i using the code below:

public void Connect()
        {
            this.han = new SqlConnection(@"Data Source=C:\Users\יהב\Documents\Visual Studio 2008\Projects\ADOtutorial\ADOtutorial\my_database.sdf;Persist Security Info=True;Application Name=ConnectDB");
            Console.WriteLine(this.han.ConnectionString);
            try
            {
                this.han.Open();
            }
            catch (NullReferenceException e)
            {
                Console.WriteLine(e);
            }
            catch (SqlException e)
            {
                Console.WriteLine(e);
            }
        }

this is a code in class, the class using this.han to make theconnection, the connection string i have copied from the connection strind i got from VS when i did motify connection -> advance. but for some resons i cant connect

the error i got:

Data Source=C:\Users\יהב\Documents\Visual Studio 2008\Projects\ADOtutorial\ADOtu
torial\my_database.sdf;Persist Security Info=True;Application Name=ConnectDB
System.Data.SqlClient.SqlException: אירעה שגיאה הקשורה לרשת או שגיאה ספציפית למו
פע במהלך יצירת חיבור ל- SQL Server.  השרת לא נמצא או שאינו נגיש. ודא כי שם המופע
 נכון וכי SQL Server מוגדר לאפשר חיבורים מרוחקים. (provider: ממשקי רשת של SQL, e
rror: 26 - שגיאה באיתור שרת/מופע שצוין)
   ב-  System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exceptio
n, Boolean breakConnection)
   ב-  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateOb
ject stateObj)
   ב-  System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInterna
lConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boo
lean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection
 owningObject)
   ב-  System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
 serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire
, SqlConnection owningObject)
   ב-  System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String hos
t, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObjec
t, SqlConnectionString connectionOptions, Int64 timerStart)
   ב-  System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnect
ion owningObject, SqlConnectionString connectionOptions, String newPassword, Boo
lean redirectedUserInstance)
   ב-  System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIden
tity identity, SqlConnectionString connectionOptions, Object providerInfo, Strin
g newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   ב-  System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionO
ptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnectio
n owningConnection)
   ב-  System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbCon
nection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   ב-  System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection ownin
gObject)
   ב-  System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
   ב-  System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owni
ngObject)
   ב-  System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection o
wningConnection)
   ב-  System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection o
uterConnection, DbConnectionFactory connectionFactory)
   ב-  System.Data.SqlClient.SqlConnection.Open()
   ב-  ADOtutorial.Database_Driver.Connect() ב- C:\Users\יהב\Documents\Visual St
udio 2008\Projects\ADOtutorial\ADOtutorial\Database_Driver.cs:שורה 46
Press any key to continue . . .

google translate for the hebrew there:

There was an error related to the network or specific error Lmo <br />
According to the connection during the creation - SQL Server. Server not found or is not accessible. Verify that the instance name <br />
  Correct and that SQL Server is configured to allow remote connections. (provider: Web interface of SQL, e <br />
rror: 26 - Error locating server / instance specified)<br />

hope you can help me, thanks, Yahav Wink | ;)

Gindi Bar Yahav - Web & Software defeloper.

Eddy Vluggen23-Apr-09 6:49

Eddy Vluggen

23-Apr-09 6:49

.sdf files are SQL CE databases, not SQL Server. You have created an "embedded" database. The database that you wanted (SQL Server) has the file-extension ".mdf".

What did you use to create the database?

I are troll Smile | :)

Gindi Bar Yahav23-Apr-09 6:58

Gindi Bar Yahav

23-Apr-09 6:58

thanks for the replay, umm i didnt know that this is not a SQL Server... i open new Console APP (to check this, acualy i am building a program to my website in WIN but now i need to learn it first.. i done it before with MYSQL but...)
so~, i clicked on Add new item (in the sulution explorer) and them local database connection.
umm... and them i saw the database in the database explorer and i could edit tables, add tables etc...

if its not mysql, can you explin me where i can find the way to connect to that type of sql server?

Gindi Bar Yahav - Web & Software defeloper.

Eddy Vluggen23-Apr-09 7:18

Eddy Vluggen

23-Apr-09 7:18

Do you want:
* SQL Server (Serves lots of people at once)
* SQL CE (Embedded database)
* MySQL (...) ?

The SQLConnection is used for SQL Server, not MySQL. SQL Server might be installed by the VS2008 setup, MySQL is a separate download.

Downloads be here[^], tutorials on SQL Server are here[^].

I are troll Smile | :)