locked
Microsoft OLEDB connection string for csv file RRS feed

  • Question

  • User-1590737996 posted

    I am trying to display excel files in the three formats: .xls, .xlsx and .csv. I've managed to get the first two working but not csv:

                    if (ext.Trim() == ".xls")
                    {
                        //connection string for that file which extantion is .xls  
                        ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    }
                    else if (ext.Trim() == ".xlsx")     
                    {
                        //connection string for that file which extantion is .xlsx  
                        ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }
                    else if (ext.Trim() == ".csv")
                    {
                        ConStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + path + ";Extended Properties = \"text;HDR=Yes;FMT=Delimited";
                    }

    What is the correct csv connection string?

    Wednesday, November 15, 2017 2:06 AM

Answers

  • User-1716253493 posted

    try this

    ConStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + path + ";Extended Properties = \"text;HDR=Yes;FMT=Delimited\"";

    or copy xls conn str, then some modification, seem like the last one deferent then previous, compare all, see what lost

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 15, 2017 2:38 AM
  • User-821857111 posted

    Rather than using JET, you might want to consider using the TextFieldParser library to read text-based files: https://www.mikesdotnetting.com/article/279/reading-text-based-files-in-asp-net

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 22, 2017 8:13 AM

All replies

  • User-1716253493 posted

    try this

    ConStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + path + ";Extended Properties = \"text;HDR=Yes;FMT=Delimited\"";

    or copy xls conn str, then some modification, seem like the last one deferent then previous, compare all, see what lost

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 15, 2017 2:38 AM
  • User-821857111 posted

    Rather than using JET, you might want to consider using the TextFieldParser library to read text-based files: https://www.mikesdotnetting.com/article/279/reading-text-based-files-in-asp-net

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 22, 2017 8:13 AM
  • User304177116 posted

    DataSet ds = new DataSet();
    if (FileUpload1.HasFile)
    {
    string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string FilePath = Server.MapPath("../Files/" + FileName);
    FileUpload1.SaveAs(FilePath);

    var fileName = string.Format("{0}{1}", AppDomain.CurrentDomain.BaseDirectory, "FolderName\\");
    string connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=""text;HDR=YES;FMT=Delimited""", fileName);
    OleDbConnection conn = new OleDbConnection(connectionString);
    conn.Open();
    var cmd = new OleDbCommand("SELECT * FROM [" + FileName + "]", conn);

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    da.Fill(ds);
    conn.Close();

    Thursday, August 23, 2018 4:25 AM