Import .csv file using C#

Question

empyrean 0 Light Poster

14 Years Ago

helo all

i wanted to import .csv file to my application

private DataSet ReadExcelData(string sFilePath)
        {
           
            DataSet dsOutput = new DataSet();
            string sConnection = string.Empty;
            try
            {
            if (TypeExcel == "Excel 12.0 Xml")
            {

                sConnection = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFilePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
            }
            else if (TypeExcel == "Excel 8.0") 
            {
             sConnection = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            }
                
            else
            {
                [B]sConnection = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath + ";Extended Properties=\"ext;HDR=YES;FMT=CSVDelimited\"";[/B]
             }
            OleDbConnection dbCon = new OleDbConnection(sConnection);
                dbCon.Open();
                // Get All Sheets Name
            
                DataTable dtSheetName = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            
        

            // Retrive the Data by Sheetwise

            
                for (int nCount = 0; nCount < 1; nCount++)
                {

                    string sSheetName =   dtSheetName.Rows[nCount]["TABLE_NAME"].ToString();

                    string sQuery = "Select * From [" + sSheetName + "]";

                    OleDbCommand dbCmd = new OleDbCommand(sQuery, dbCon);

                    OleDbDataAdapter dbDa = new OleDbDataAdapter(dbCmd);
                    dbDa.Fill(dtData);
                    dsOutput.Tables.Add(dtData.Copy());
       }
       dbCon.Close();
             return dsOutput;
  }

i am getting the following error while using the above code while importing .csv file.. the bold line is the connection string where the error is throwing..

base {System.Data.Common.DbException} = {"'C:\\ABCD Calif, CA.CSV' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides."}

c#

1 Contributor
1 Reply
2K Views
6 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by empyrean

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

empyrean 0 Light Poster · Answer 1 · 2010-10-05T01:21:30+00:00

used this..

string full = System.IO.Path.GetFullPath(sFilePath);
                string file = System.IO.Path.GetFileName(full);
                string dir = System.IO.Path.GetDirectoryName(full);
                sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
                              + "Data Source=\"" + dir + "\\\";"
                              + "Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
                string query = "SELECT * FROM [" + file+"]";
                
                //create an OleDbDataAdapter to execute the query
                OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, sConnection);
                dAdapter.Fill(dtData);
               
                dAdapter.Dispose();
                dsOutput.Tables.Add(dtData.Copy());
                return dsOutput;