tdf#160582 Preserve settings saving in csv import dialog

Also, improve detection algorithm by replacing the limit
of 20 lines with a time limit of 500ms.

Change-Id: Iac519b6ebe675b91ce84b900646d9d320ea9ddc1
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/165905
Reviewed-by: Andras Timar <andras.timar@collabora.com>
Tested-by: Jenkins

28 files changed, 346 insertions, 244 deletions

diff --git a/include/rtl/textenc.h b/include/rtl/textenc.h
index af4a16e5c422..10d69e734a4f 100644
--- a/include/rtl/textenc.h
+++ b/include/rtl/textenc.h
@@ -141,6 +141,7 @@ typedef sal_uInt16 rtl_TextEncoding;
  */
 
 #define RTL_TEXTENCODING_USER_START             (RTL_TEXTENC_CAST( 0x8000 ))
+#define RTL_TEXTENCODING_USER_DETECTED          (RTL_TEXTENCODING_USER_START + 0)
 #define RTL_TEXTENCODING_USER_END               (RTL_TEXTENC_CAST( 0xEFFF ))
 
 #define RTL_TEXTENCODING_UCS4                   (RTL_TEXTENC_CAST( 0xFFFE ))
diff --git a/officecfg/registry/schema/org/openoffice/Office/Calc.xcs b/officecfg/registry/schema/org/openoffice/Office/Calc.xcs
index cb557431362b..770b31e7a5fd 100644
--- a/officecfg/registry/schema/org/openoffice/Office/Calc.xcs
+++ b/officecfg/registry/schema/org/openoffice/Office/Calc.xcs
@@ -1012,6 +1012,13 @@
           </info>
           <value>false</value>
         </prop>
+        <prop oor:name="SeparatorType" oor:type="xs:short" oor:nillable="false">
+          <info>
+            <desc>Fixed width, separator or detected separator</desc>
+            <label>SeparatorType</label>
+          </info>
+          <value>2</value>
+        </prop>
         <prop oor:name="QuotedFieldAsText" oor:type="xs:boolean" oor:nillable="false">
           <info>
             <desc>If true, quoted field is always imported as text with no exception.</desc>
diff --git a/sc/inc/miscuno.hxx b/sc/inc/miscuno.hxx
index 6ac2a925090b..71cd9390de49 100644
--- a/sc/inc/miscuno.hxx
+++ b/sc/inc/miscuno.hxx
@@ -152,8 +152,8 @@ public:
         const css::uno::Reference<css::beans::XPropertySet>& xProp,
         const OUString& rName, const OUString& rDefault );
 
-    SC_DLLPUBLIC static bool GetBoolFromAny( const css::uno::Any& aAny );
-    static sal_Int16        GetInt16FromAny( const css::uno::Any& aAny );
+    SC_DLLPUBLIC static bool        GetBoolFromAny( const css::uno::Any& aAny );
+    SC_DLLPUBLIC static sal_Int16   GetInt16FromAny( const css::uno::Any& aAny );
     static sal_Int32        GetInt32FromAny( const css::uno::Any& aAny );
     static sal_Int32        GetEnumFromAny( const css::uno::Any& aAny );
 
diff --git a/sc/inc/scabstdlg.hxx b/sc/inc/scabstdlg.hxx
index 158501225a58..957c9605a116 100644
--- a/sc/inc/scabstdlg.hxx
+++ b/sc/inc/scabstdlg.hxx
@@ -419,8 +419,7 @@ public:
     virtual     VclPtr<AbstractScImportAsciiDlg> CreateScImportAsciiDlg(weld::Window* pParent,
                                                                     const OUString& aDatName,
                                                                     SvStream* pInStream,
-                                                                    ScImportAsciiCall eCall,
-                                                                    ScAsciiOptions* aOptions = nullptr) = 0;
+                                                                    ScImportAsciiCall eCall) = 0;
 
     virtual     VclPtr<AbstractScTextImportOptionsDlg> CreateScTextImportOptionsDlg(weld::Window* pParent) = 0;
 
diff --git a/sc/inc/strings.hrc b/sc/inc/strings.hrc
index 3e23a800974e..faaa5e64998b 100644
--- a/sc/inc/strings.hrc
+++ b/sc/inc/strings.hrc
@@ -85,6 +85,8 @@
 #define SCSTR_FIELDSEP_SPACE                        NC_("SCSTR_FIELDSEP_SPACE", "space")
 #define SCSTR_UNDO_GRAFFILTER                       NC_("SCSTR_UNDO_GRAFFILTER", "Image Filter")
 #define STR_CAPTION_DEFAULT_TEXT                    NC_("STR_CAPTION_DEFAULT_TEXT", "Text")
+#define SCSTR_DETECTED                              NC_("SCSTR_DETECTED", "Detected (%1)")
+#define SCSTR_AUTOMATIC                             NC_("SCSTR_AUTOMATIC", "Automatic")
 // Select tables dialog title
 #define STR_DLG_SELECTTABLES_TITLE                  NC_("STR_DLG_SELECTTABLES_TITLE", "Select Sheets")
 #define STR_DLG_SELECTTABLE_TITLE                   NC_("STR_DLG_SELECTTABLE_TITLE", "Go to Sheet")
diff --git a/sc/qa/uitest/csv_dialog/tdf117868.py b/sc/qa/uitest/csv_dialog/tdf117868.py
index d5306117e372..405a18f12acc 100644
--- a/sc/qa/uitest/csv_dialog/tdf117868.py
+++ b/sc/qa/uitest/csv_dialog/tdf117868.py
@@ -17,21 +17,12 @@ class Td117868(UITestCase):
 
         with load_csv_file(self, "tdf117868.csv", False) as xDialog:
             # Set text delimiter in case it's changed by another test
-            xSeparatedBy = xDialog.getChild("toseparatedby")
-            xSeparatedBy.executeAction("CLICK", tuple())
-
-            # Without the fix in place, this test would have failed with
-            # AssertionError: 'true' != 'false'
-            self.assertEqual('true', get_state_as_dict(xDialog.getChild("other"))['Selected'])
-            self.assertEqual('false', get_state_as_dict(xDialog.getChild("tab"))['Selected'])
-            self.assertEqual('false', get_state_as_dict(xDialog.getChild("comma"))['Selected'])
-            self.assertEqual('false', get_state_as_dict(xDialog.getChild("semicolon"))['Selected'])
+            self.assertEqual('Detected (|)', get_state_as_dict(xDialog.getChild("todetectseparator"))['Text'])
+            xDetected = xDialog.getChild("todetectseparator")
+            xDetected.executeAction("CLICK", tuple())
 
             self.assertEqual('1', get_state_as_dict(xDialog.getChild("fromrow"))['Text'])
 
-            xInputOther = xDialog.getChild("inputother")
-            self.assertEqual("|", get_state_as_dict(xInputOther)['Text'])
-
         document = self.ui_test.get_component()
 
         self.assertEqual("LETTER", get_cell_by_position(document, 0, 0, 1).getString())
diff --git a/sc/qa/uitest/textToColumns/tdf143008.py b/sc/qa/uitest/textToColumns/tdf143008.py
index 087a7b5a8fd5..a14c671d26f6 100644
--- a/sc/qa/uitest/textToColumns/tdf143008.py
+++ b/sc/qa/uitest/textToColumns/tdf143008.py
@@ -22,6 +22,9 @@ class tdf143008(UITestCase):
             enter_text_to_cell(gridwin, "A1", "22/06/2021 10:02 PM")
 
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns") as xDialog:
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xGrid = xDialog.getChild("csvgrid")
                 xColumnType = xDialog.getChild("columntype")
 
diff --git a/sc/qa/uitest/textToColumns/tdf51700.py b/sc/qa/uitest/textToColumns/tdf51700.py
index 98c33d8f3104..b4fdf9b24462 100644
--- a/sc/qa/uitest/textToColumns/tdf51700.py
+++ b/sc/qa/uitest/textToColumns/tdf51700.py
@@ -28,6 +28,9 @@ class tdf51700(UITestCase):
             self.xUITest.executeCommand(".uno:SelectColumn")
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns") as xDialog:
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xcomma = xDialog.getChild("comma")
                 if (get_state_as_dict(xcomma)["Selected"]) == "false":
                     xcomma.executeAction("CLICK", tuple())
diff --git a/sc/qa/uitest/textToColumns/tdf69981.py b/sc/qa/uitest/textToColumns/tdf69981.py
index 87c09e8ffcbf..16a1e9eb305e 100644
--- a/sc/qa/uitest/textToColumns/tdf69981.py
+++ b/sc/qa/uitest/textToColumns/tdf69981.py
@@ -23,6 +23,9 @@ class tdf69981(UITestCase):
             gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "A2:A7"}))
             #Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns", close_button="") as xDialog:
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xtab = xDialog.getChild("tab")
                 xcomma = xDialog.getChild("comma")
                 xtab.executeAction("CLICK", tuple())
diff --git a/sc/qa/uitest/textToColumns/tdf73006.py b/sc/qa/uitest/textToColumns/tdf73006.py
index 1ac519f73c4b..0ffa5e69731e 100644
--- a/sc/qa/uitest/textToColumns/tdf73006.py
+++ b/sc/qa/uitest/textToColumns/tdf73006.py
@@ -26,6 +26,9 @@ class tdf73006(UITestCase):
             self.xUITest.executeCommand(".uno:SelectColumn")
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns") as xDialog:
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xspace = xDialog.getChild("space")
                 if (get_state_as_dict(xspace)["Selected"]) == "false":
                     xspace.executeAction("CLICK", tuple())
diff --git a/sc/qa/uitest/textToColumns/tdf82398.py b/sc/qa/uitest/textToColumns/tdf82398.py
index b0722fcbd14c..2ddf0b60d654 100644
--- a/sc/qa/uitest/textToColumns/tdf82398.py
+++ b/sc/qa/uitest/textToColumns/tdf82398.py
@@ -29,6 +29,9 @@ class tdf82398(UITestCase):
             self.xUITest.executeCommand(".uno:NumberFormatDate")
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns") as xDialog:
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xother = xDialog.getChild("other")
                 xinputother = xDialog.getChild("inputother")
 
diff --git a/sc/qa/uitest/textToColumns/tdf85979.py b/sc/qa/uitest/textToColumns/tdf85979.py
index ca5808947e70..a3be674b0d69 100644
--- a/sc/qa/uitest/textToColumns/tdf85979.py
+++ b/sc/qa/uitest/textToColumns/tdf85979.py
@@ -23,6 +23,9 @@ class tdf85979(UITestCase):
             gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "C1:C5"}))
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns") as xDialog:
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xspace = xDialog.getChild("space")
                 if (get_state_as_dict(xspace)["Selected"]) == "false":
                     xspace.executeAction("CLICK", tuple())
diff --git a/sc/qa/uitest/textToColumns/tdf89907.py b/sc/qa/uitest/textToColumns/tdf89907.py
index ef5f164f9261..a7ea4c3c0207 100644
--- a/sc/qa/uitest/textToColumns/tdf89907.py
+++ b/sc/qa/uitest/textToColumns/tdf89907.py
@@ -37,6 +37,9 @@ class tdf89907(UITestCase):
 
                 # Data - Text to Columns
                 with self.ui_test.execute_dialog_through_command(".uno:TextToColumns") as xDialog:
+                    xSeparatedBy = xDialog.getChild("toseparatedby")
+                    xSeparatedBy.executeAction("CLICK", tuple())
+
                     xother = xDialog.getChild("other")
                     xinputother = xDialog.getChild("inputother")
                     if (get_state_as_dict(xother)["Selected"]) == "false":
diff --git a/sc/qa/uitest/textToColumns/tdf92423.py b/sc/qa/uitest/textToColumns/tdf92423.py
index 99486bb2f00e..ce9cadf3f348 100644
--- a/sc/qa/uitest/textToColumns/tdf92423.py
+++ b/sc/qa/uitest/textToColumns/tdf92423.py
@@ -39,6 +39,9 @@ class tdf92423(UITestCase):
             self.assertEqual(gridWinState["MarkedArea"], "Sheet1.A7:Sheet1.A9")
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns") as xDialog:
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xSemicolon = xDialog.getChild("semicolon")  #check semicolon checkbox
                 if (get_state_as_dict(xSemicolon)["Selected"]) == "false":
                     xSemicolon.executeAction("CLICK", tuple())
diff --git a/sc/qa/uitest/textToColumns/textToColumns.py b/sc/qa/uitest/textToColumns/textToColumns.py
index c67e879d1835..678a75b04c77 100644
--- a/sc/qa/uitest/textToColumns/textToColumns.py
+++ b/sc/qa/uitest/textToColumns/textToColumns.py
@@ -26,6 +26,9 @@ class CalcTextToColumns(UITestCase):
             #Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns", close_button="") as xDialog:
                 #Untag Tab as separator and tag other. Put a dot into the input field next to the other checkbox
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xother = xDialog.getChild("other")
                 xinputother = xDialog.getChild("inputother")
 
@@ -85,6 +88,9 @@ class CalcTextToColumns(UITestCase):
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns", close_button="") as xDialog:
                 # Untag Tab as separator and tag comma.
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xComma = xDialog.getChild("comma")
                 if (get_state_as_dict(xComma)["Selected"]) == "false":
                     xComma.executeAction("CLICK", tuple())
@@ -142,6 +148,9 @@ class CalcTextToColumns(UITestCase):
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns", close_button="") as xDialog:
                 # Untag comma as separator and tag Semicolon
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xSemicolon = xDialog.getChild("semicolon")
                 if (get_state_as_dict(xSemicolon)["Selected"]) == "false":
                     xSemicolon.executeAction("CLICK", tuple())
@@ -199,6 +208,9 @@ class CalcTextToColumns(UITestCase):
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns", close_button="") as xDialog:
                 # Untag comma as separator and tag Semicolon
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xSpace = xDialog.getChild("space")
                 if (get_state_as_dict(xSpace)["Selected"]) == "false":
                     xSpace.executeAction("CLICK", tuple())
@@ -257,6 +269,9 @@ class CalcTextToColumns(UITestCase):
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns", close_button="") as xDialog:
                 # Untag comma as separator and tag Semicolon
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xother = xDialog.getChild("other")
                 xinputother = xDialog.getChild("inputother")
                 if (get_state_as_dict(xother)["Selected"]) == "false":
@@ -315,6 +330,9 @@ class CalcTextToColumns(UITestCase):
             gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "A1:A5"}))
             # Data - Text to Columns
             with self.ui_test.execute_dialog_through_command(".uno:TextToColumns", close_button="") as xDialog:
+                xSeparatedBy = xDialog.getChild("toseparatedby")
+                xSeparatedBy.executeAction("CLICK", tuple())
+
                 xspace = xDialog.getChild("space")
                 xother = xDialog.getChild("other")
                 xinputother = xDialog.getChild("inputother")
diff --git a/sc/source/ui/attrdlg/scdlgfact.cxx b/sc/source/ui/attrdlg/scdlgfact.cxx
index 47e81dc7c09f..af95b1e025fa 100644
--- a/sc/source/ui/attrdlg/scdlgfact.cxx
+++ b/sc/source/ui/attrdlg/scdlgfact.cxx
@@ -1073,10 +1073,9 @@ bool AbstractScSelEntryDlg_Impl::StartExecuteAsync(VclAbstractDialog::AsyncConte
 // =========================Factories  for createdialog ===================
 VclPtr<AbstractScImportAsciiDlg> ScAbstractDialogFactory_Impl::CreateScImportAsciiDlg(weld::Window* pParent,
                                                     const OUString& aDatName,
-                                                    SvStream* pInStream, ScImportAsciiCall eCall,
-                                                    ScAsciiOptions* aOptions)
+                                                    SvStream* pInStream, ScImportAsciiCall eCall)
 {
-    return VclPtr<AbstractScImportAsciiDlg_Impl>::Create(std::make_shared<ScImportAsciiDlg>(pParent, aDatName,pInStream, eCall, aOptions));
+    return VclPtr<AbstractScImportAsciiDlg_Impl>::Create(std::make_shared<ScImportAsciiDlg>(pParent, aDatName,pInStream, eCall));
 }
 
 VclPtr<AbstractScTextImportOptionsDlg> ScAbstractDialogFactory_Impl::CreateScTextImportOptionsDlg(weld::Window* pParent)
diff --git a/sc/source/ui/attrdlg/scdlgfact.hxx b/sc/source/ui/attrdlg/scdlgfact.hxx
index 0f8077240a73..d7dab5e4c4b8 100644
--- a/sc/source/ui/attrdlg/scdlgfact.hxx
+++ b/sc/source/ui/attrdlg/scdlgfact.hxx
@@ -666,8 +666,7 @@ public:
     virtual VclPtr<AbstractScImportAsciiDlg> CreateScImportAsciiDlg(weld::Window* pParent,
                                                                     const OUString& aDatName,
                                                                     SvStream* pInStream,
-                                                                    ScImportAsciiCall eCall,
-                                                                    ScAsciiOptions* aOptions = nullptr) override;
+                                                                    ScImportAsciiCall eCall) override;
 
     virtual VclPtr<AbstractScTextImportOptionsDlg> CreateScTextImportOptionsDlg(weld::Window* pParent) override;
 
diff --git a/sc/source/ui/dbgui/asciiopt.cxx b/sc/source/ui/dbgui/asciiopt.cxx
index c9a4d881baed..4c470793f9ac 100644
--- a/sc/source/ui/dbgui/asciiopt.cxx
+++ b/sc/source/ui/dbgui/asciiopt.cxx
@@ -22,9 +22,11 @@
 #include <comphelper/string.hxx>
 #include <osl/thread.h>
 #include <o3tl/string_view.hxx>
+#include <sfx2/objsh.hxx>
 
 constexpr std::u16string_view pStrFix = u"FIX";
 constexpr std::u16string_view pStrMrg = u"MRG";
+constexpr std::u16string_view pStrDet = u"DETECT";
 
 ScAsciiOptions::ScAsciiOptions() :
     bFixedLen       ( false ),
@@ -86,9 +88,10 @@ static OUString lcl_decodeSepString( std::u16string_view rSepNums, bool & o_bMer
 // The options string must not contain semicolons (because of the pick list),
 // use comma as separator.
 
-void ScAsciiOptions::ReadFromString( std::u16string_view rString )
+void ScAsciiOptions::ReadFromString( std::u16string_view rString, SvStream* pStream4Detect )
 {
     sal_Int32 nPos = rString.empty() ? -1 : 0;
+    bool bDetectSep = false;
 
     // Token 0: Field separator.
     if ( nPos >= 0 )
@@ -96,9 +99,14 @@ void ScAsciiOptions::ReadFromString( std::u16string_view rString )
         bFixedLen = bMergeFieldSeps = false;
 
         const std::u16string_view aToken = o3tl::getToken(rString, 0, ',', nPos);
-        if ( aToken == pStrFix )
-            bFixedLen = true;
-        aFieldSeps = lcl_decodeSepString( aToken, bMergeFieldSeps);
+        if ( aToken == pStrDet)
+            bDetectSep = true;
+        else
+        {
+            if ( aToken == pStrFix )
+                bFixedLen = true;
+            aFieldSeps = lcl_decodeSepString( aToken, bMergeFieldSeps);
+        }
     }
 
     // Token 1: Text separator.
@@ -111,9 +119,22 @@ void ScAsciiOptions::ReadFromString( std::u16string_view rString )
     // Token 2: Text encoding.
     if ( nPos >= 0 )
     {
-        eCharSet = ScGlobal::GetCharsetValue( o3tl::getToken(rString, 0, ',', nPos) );
+        const std::u16string_view aToken = o3tl::getToken(rString, 0, ',', nPos);
+        SvStreamEndian endian;
+        bool bDetectCharSet = aToken == pStrDet;
+        if ( bDetectCharSet && pStream4Detect )
+        {
+            SfxObjectShell::DetectCharSet(*pStream4Detect, eCharSet, endian);
+            if (eCharSet == RTL_TEXTENCODING_UNICODE)
+                pStream4Detect->SetEndian(endian);
+        }
+        else if (!bDetectCharSet)
+            eCharSet = ScGlobal::GetCharsetValue( aToken );
     }
 
+    if (bDetectSep && pStream4Detect)
+        SfxObjectShell::DetectCsvSeparators(*pStream4Detect, eCharSet, aFieldSeps, cTextSep);
+
     // Token 3: Number of start row.
     if ( nPos >= 0 )
     {
diff --git a/sc/source/ui/dbgui/csvtablebox.cxx b/sc/source/ui/dbgui/csvtablebox.cxx
index 2a3a16c0c550..ab8231013009 100644
--- a/sc/source/ui/dbgui/csvtablebox.cxx
+++ b/sc/source/ui/dbgui/csvtablebox.cxx
@@ -57,6 +57,26 @@ ScCsvTableBox::~ScCsvTableBox()
 
 // common table box handling --------------------------------------------------
 
+void ScCsvTableBox::Refresh()
+{
+    mxGrid->DisableRepaint();
+    mxGrid->Execute( CSVCMD_SETLINEOFFSET, 0 );
+    if (mbFixedMode)
+    {
+        mxGrid->Execute( CSVCMD_SETPOSCOUNT, mnFixedWidth );
+        mxGrid->SetSplits( mxRuler->GetSplits() );
+        mxGrid->SetColumnStates( std::vector(maFixColStates) );
+    }
+    else
+    {
+        mxGrid->Execute( CSVCMD_SETPOSCOUNT, 1 );
+        mxGrid->Execute( CSVCMD_NEWCELLTEXTS );
+        mxGrid->SetColumnStates( std::vector(maSepColStates) );
+    }
+    InitControls();
+    mxGrid->EnableRepaint();
+}
+
 void ScCsvTableBox::SetSeparatorsMode()
 {
     if( !mbFixedMode )
@@ -68,13 +88,7 @@ void ScCsvTableBox::SetSeparatorsMode()
     // switch to separators mode
     mbFixedMode = false;
     // reset and reinitialize controls
-    mxGrid->DisableRepaint();
-    mxGrid->Execute( CSVCMD_SETLINEOFFSET, 0 );
-    mxGrid->Execute( CSVCMD_SETPOSCOUNT, 1 );
-    mxGrid->Execute( CSVCMD_NEWCELLTEXTS );
-    mxGrid->SetColumnStates( std::vector(maSepColStates) );
-    InitControls();
-    mxGrid->EnableRepaint();
+    Refresh();
 }
 
 void ScCsvTableBox::SetFixedWidthMode()
@@ -87,13 +101,7 @@ void ScCsvTableBox::SetFixedWidthMode()
     // switch to fixed width mode
     mbFixedMode = true;
     // reset and reinitialize controls
-    mxGrid->DisableRepaint();
-    mxGrid->Execute( CSVCMD_SETLINEOFFSET, 0 );
-    mxGrid->Execute( CSVCMD_SETPOSCOUNT, mnFixedWidth );
-    mxGrid->SetSplits( mxRuler->GetSplits() );
-    mxGrid->SetColumnStates( std::vector(maFixColStates) );
-    InitControls();
-    mxGrid->EnableRepaint();
+    Refresh();
 }
 
 void ScCsvTableBox::Init()
diff --git a/sc/source/ui/dbgui/scuiasciiopt.cxx b/sc/source/ui/dbgui/scuiasciiopt.cxx
index 304424806aa5..2789d33096c8 100644
--- a/sc/source/ui/dbgui/scuiasciiopt.cxx
+++ b/sc/source/ui/dbgui/scuiasciiopt.cxx
@@ -41,6 +41,8 @@
 #include <o3tl/string_view.hxx>
 
 #include <unicode/ucsdet.h>
+#include <sfx2/objsh.hxx>
+#include <svx/txenctab.hxx>
 
 //! TODO make dynamic
 const SCSIZE ASCIIDLG_MAXROWS                = MAXROWCOUNT;
@@ -67,6 +69,7 @@ enum CSVImportOptionsIndex
     CSVIO_FixedWidth,
     CSVIO_RemoveSpace,
     CSVIO_EvaluateFormulas,
+    CSVIO_SeparatorType,
     // Settings for *all* dialog invocations above.
     // Settings not for SC_TEXTTOCOLUMNS below.
     CSVIO_FromRow,
@@ -80,6 +83,13 @@ enum CSVImportOptionsIndex
     CSVIO_PasteSkipEmptyCells
 };
 
+enum SeparatorType
+{
+    FIXED,
+    SEPARATOR,
+    DETECT_SEPARATOR
+};
+
 }
 
 // Config items for all three paths are defined in
@@ -93,6 +103,7 @@ const ::std::vector<OUString> CSVImportOptionNames =
     u"FixedWidth"_ustr,
     u"RemoveSpace"_ustr,
     u"EvaluateFormulas"_ustr,
+    u"SeparatorType"_ustr,
     u"FromRow"_ustr,
     u"CharSet"_ustr,
     u"QuotedFieldAsText"_ustr,
@@ -176,16 +187,16 @@ static void lcl_CreatePropertiesNames ( OUString& rSepPath, Sequence<OUString>&
     {
         case SC_IMPORTFILE:
             rSepPath = aSep_Path;
-            nProperties = 12;
+            nProperties = 13;
             break;
         case SC_PASTETEXT:
             rSepPath = aSep_Path_Clpbrd;
-            nProperties = 13;
+            nProperties = 14;
             break;
         case SC_TEXTTOCOLUMNS:
         default:
             rSepPath = aSep_Path_Text2Col;
-            nProperties = 7;
+            nProperties = 8;
             break;
     }
     rNames.realloc( nProperties );
@@ -196,6 +207,7 @@ static void lcl_CreatePropertiesNames ( OUString& rSepPath, Sequence<OUString>&
     pNames[ CSVIO_FixedWidth ] =        CSVImportOptionNames[ CSVIO_FixedWidth ];
     pNames[ CSVIO_RemoveSpace ] =       CSVImportOptionNames[ CSVIO_RemoveSpace ];
     pNames[ CSVIO_EvaluateFormulas ] =  CSVImportOptionNames[ CSVIO_EvaluateFormulas ];
+    pNames[ CSVIO_SeparatorType ] =     CSVImportOptionNames[ CSVIO_SeparatorType ];
     if (eCall != SC_TEXTTOCOLUMNS)
     {
         pNames[ CSVIO_FromRow ] =       CSVImportOptionNames[ CSVIO_FromRow ];
@@ -215,9 +227,9 @@ static void lcl_CreatePropertiesNames ( OUString& rSepPath, Sequence<OUString>&
 
 static void lcl_LoadSeparators( OUString& rFieldSeparators, OUString& rTextSeparators,
                              bool& rMergeDelimiters, bool& rQuotedAsText, bool& rDetectSpecialNum, bool& rDetectScientificNum,
-                             bool& rFixedWidth, sal_Int32& rFromRow, sal_Int32& rCharSet,
+                             SeparatorType& rSepType, sal_Int32& rFromRow, sal_Int32& rCharSet,
                              sal_Int32& rLanguage, bool& rSkipEmptyCells, bool& rRemoveSpace,
-                             bool& rEvaluateFormulas, ScImportAsciiCall eCall )
+                             bool& rEvaluateFormulas, ScImportAsciiCall eCall, bool& rBeforeDetection )
 {
     Sequence<Any>aValues;
     const Any *pProperties;
@@ -240,8 +252,14 @@ static void lcl_LoadSeparators( OUString& rFieldSeparators, OUString& rTextSepar
     if( pProperties[ CSVIO_TextSeparators ].hasValue() )
         pProperties[ CSVIO_TextSeparators ] >>= rTextSeparators;
 
-    if( pProperties[ CSVIO_FixedWidth ].hasValue() )
-        rFixedWidth = ScUnoHelpFunctions::GetBoolFromAny( pProperties[ CSVIO_FixedWidth ] );
+    rBeforeDetection = true;
+    if( pProperties[ CSVIO_SeparatorType ].hasValue() )
+    {
+        rBeforeDetection = false;
+        rSepType = static_cast<SeparatorType>(ScUnoHelpFunctions::GetInt16FromAny( pProperties[ CSVIO_SeparatorType ] ));
+    }
+    else if( pProperties[ CSVIO_FixedWidth ].hasValue() )
+        rSepType = (ScUnoHelpFunctions::GetBoolFromAny( pProperties[ CSVIO_FixedWidth ] ) ? SeparatorType::FIXED : SeparatorType::DETECT_SEPARATOR);
 
     if( pProperties[ CSVIO_EvaluateFormulas ].hasValue() )
         rEvaluateFormulas = ScUnoHelpFunctions::GetBoolFromAny( pProperties[ CSVIO_EvaluateFormulas ] );
@@ -277,7 +295,7 @@ static void lcl_LoadSeparators( OUString& rFieldSeparators, OUString& rTextSepar
 
 static void lcl_SaveSeparators(
     const OUString& sFieldSeparators, const OUString& sTextSeparators, bool bMergeDelimiters, bool bQuotedAsText,
-    bool bDetectSpecialNum, bool bDetectScientificNum, bool bFixedWidth, sal_Int32 nFromRow,
+    bool bDetectSpecialNum, bool bDetectScientificNum, SeparatorType rSepType, sal_Int32 nFromRow,
     sal_Int32 nCharSet, sal_Int32 nLanguage, bool bSkipEmptyCells, bool bRemoveSpace, bool bEvaluateFormulas,
     ScImportAsciiCall eCall )
 {
@@ -294,8 +312,8 @@ static void lcl_SaveSeparators(
     pProperties[ CSVIO_RemoveSpace ] <<= bRemoveSpace;
     pProperties[ CSVIO_Separators ] <<= sFieldSeparators;
     pProperties[ CSVIO_TextSeparators ] <<= sTextSeparators;
-    pProperties[ CSVIO_FixedWidth ] <<= bFixedWidth;
     pProperties[ CSVIO_EvaluateFormulas ] <<= bEvaluateFormulas;
+    pProperties[ CSVIO_SeparatorType ] <<= static_cast<sal_Int16>(rSepType);
     if (eCall != SC_TEXTTOCOLUMNS)
     {
         pProperties[ CSVIO_FromRow ] <<= nFromRow;
@@ -316,21 +334,24 @@ static void lcl_SaveSeparators(
 }
 
 ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, std::u16string_view aDatName,
-                                   SvStream* pInStream, ScImportAsciiCall eCall,
-                                   const ScAsciiOptions* aOptions)
+                                   SvStream* pInStream, ScImportAsciiCall eCall)
     : GenericDialogController(pParent, u"modules/scalc/ui/textimportcsv.ui"_ustr, u"TextImportCsvDialog"_ustr)
     , mpDatStream(pInStream)
     , mnStreamPos(pInStream ? pInStream->Tell() : 0)
+    , mnStreamInitPos(mnStreamPos)
     , mnRowPosCount(0)
     , mcTextSep(ScAsciiOptions::cDefaultTextSep)
+    , meDetectedCharSet(RTL_TEXTENCODING_DONTKNOW)
+    , mbCharSetDetect(true)
     , meCall(eCall)
-    , mbDetectSep(eCall != SC_TEXTTOCOLUMNS)
     , mxFtCharSet(m_xBuilder->weld_label(u"textcharset"_ustr))
     , mxLbCharSet(new SvxTextEncodingBox(m_xBuilder->weld_combo_box(u"charset"_ustr)))
+    , mxFtDetectedCharSet(m_xBuilder->weld_label(u"textdetectedcharset"_ustr))
     , mxFtCustomLang(m_xBuilder->weld_label(u"textlanguage"_ustr))
     , mxLbCustomLang(new SvxLanguageBox(m_xBuilder->weld_combo_box(u"language"_ustr)))
     , mxFtRow(m_xBuilder->weld_label(u"textfromrow"_ustr))
     , mxNfRow(m_xBuilder->weld_spin_button(u"fromrow"_ustr))
+    , mxRbDetectSep(m_xBuilder->weld_radio_button(u"todetectseparator"_ustr))
     , mxRbFixed(m_xBuilder->weld_radio_button(u"tofixedwidth"_ustr))
     , mxRbSeparated(m_xBuilder->weld_radio_button(u"toseparatedby"_ustr))
     , mxCkbTab(m_xBuilder->weld_check_button(u"tab"_ustr))
@@ -376,40 +397,23 @@ ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, std::u16string_view aD
     OUString sFieldSeparators(u",;\t"_ustr);
     OUString sTextSeparators(mcTextSep);
     bool bMergeDelimiters = false;
-    bool bFixedWidth = false;
+    SeparatorType eSepType = DETECT_SEPARATOR;
     bool bQuotedFieldAsText = false;
     bool bDetectSpecialNum = true;
     bool bDetectScientificNum = true;
     bool bEvaluateFormulas = (meCall != SC_IMPORTFILE);
     bool bSkipEmptyCells = true;
     bool bRemoveSpace = false;
+    bool bBeforeDetection = false;
     sal_Int32 nFromRow = 1;
     sal_Int32 nCharSet = -1;
     sal_Int32 nLanguage = 0;
 
-    if (aOptions)
-    {
-        if (!aOptions->GetFieldSeps().isEmpty())
-            sFieldSeparators = aOptions->GetFieldSeps();
-        if (aOptions->GetTextSep())
-            sTextSeparators = OUStringChar(aOptions->GetTextSep());
-        bMergeDelimiters = aOptions->IsMergeSeps();
-        bFixedWidth = aOptions->IsFixedLen();
-        bQuotedFieldAsText = aOptions->IsQuotedAsText();
-        bDetectSpecialNum = aOptions->IsDetectSpecialNumber();
-        bDetectScientificNum = aOptions->IsDetectScientificNumber();
-        bEvaluateFormulas = aOptions->IsEvaluateFormulas();
-        bSkipEmptyCells = aOptions->IsSkipEmptyCells();
-        bRemoveSpace = aOptions->IsRemoveSpace();
-        nFromRow = aOptions->GetStartRow();
-        nCharSet = aOptions->GetCharSet();
-        nLanguage = static_cast<sal_uInt16>(aOptions->GetLanguage());
-    }
-    else
-        lcl_LoadSeparators (sFieldSeparators, sTextSeparators, bMergeDelimiters,
-                         bQuotedFieldAsText, bDetectSpecialNum, bDetectScientificNum, bFixedWidth, nFromRow,
-                         nCharSet, nLanguage, bSkipEmptyCells, bRemoveSpace, bEvaluateFormulas, meCall);
-    // load from saved settings
+    lcl_LoadSeparators (sFieldSeparators, sTextSeparators, bMergeDelimiters,
+                         bQuotedFieldAsText, bDetectSpecialNum, bDetectScientificNum, eSepType, nFromRow,
+                         nCharSet, nLanguage, bSkipEmptyCells, bRemoveSpace, bEvaluateFormulas, meCall,
+                         bBeforeDetection);
+
     maFieldSeparators = sFieldSeparators;
 
     if( bMergeDelimiters && !bIsTSV )
@@ -430,95 +434,48 @@ ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, std::u16string_view aD
         mxCkbEvaluateFormulas->set_active(true);
     if (bSkipEmptyCells)
         mxCkbSkipEmptyCells->set_active(true);
-    if (bFixedWidth && !bIsTSV)
-        mxRbFixed->set_active(true);
-    if (nFromRow != 1)
-        mxNfRow->set_value(nFromRow);
-
-    // Clipboard is always Unicode, else detect.
-    rtl_TextEncoding ePreselectUnicode = (aOptions ? aOptions->GetCharSet() : (meCall == SC_IMPORTFILE ?
-            RTL_TEXTENCODING_DONTKNOW : RTL_TEXTENCODING_UNICODE));
-    // Sniff for Unicode / not
-    if( ePreselectUnicode == RTL_TEXTENCODING_DONTKNOW && mpDatStream )
+    if (eSepType == SeparatorType::FIXED)
     {
-        mpDatStream->Seek( 0 );
-        constexpr size_t buffsize = 4096;
-        sal_Int8 bytes[buffsize] = { 0 };
-        sal_Int32 nRead = mpDatStream->ReadBytes( bytes, buffsize );
-        mpDatStream->Seek( 0 );
-
-        if ( nRead > 0 )
+        if (bIsTSV)
         {
-            UErrorCode uerr = U_ZERO_ERROR;
-            UCharsetDetector* ucd = ucsdet_open( &uerr );
-            ucsdet_setText( ucd, reinterpret_cast<const char*>(bytes), nRead, &uerr );
-
-            if ( const UCharsetMatch* match = ucsdet_detect(ucd, &uerr) )
-            {
-                const char* pEncodingName = ucsdet_getName( match, &uerr );
-
-                if ( U_SUCCESS(uerr) && !strcmp("UTF-8", pEncodingName) )
-                {
-                    ePreselectUnicode = RTL_TEXTENCODING_UTF8; // UTF-8
-                    mpDatStream->StartReadingUnicodeText( RTL_TEXTENCODING_UTF8 );
-                }
-                else if ( U_SUCCESS(uerr) && !strcmp("UTF-16LE", pEncodingName) )
-                {
-                    ePreselectUnicode = RTL_TEXTENCODING_UNICODE; // UTF-16LE
-                    mpDatStream->SetEndian( SvStreamEndian::LITTLE );
-                    mpDatStream->StartReadingUnicodeText( RTL_TEXTENCODING_UNICODE );
-                }
-                else if ( U_SUCCESS(uerr) && !strcmp("UTF-16BE", pEncodingName) )
-                {
-                    ePreselectUnicode = RTL_TEXTENCODING_UNICODE; // UTF-16BE
-                    mpDatStream->SetEndian( SvStreamEndian::BIG );
-                    mpDatStream->StartReadingUnicodeText( RTL_TEXTENCODING_UNICODE );
-                }
-                else // other
-                    mpDatStream->StartReadingUnicodeText( RTL_TEXTENCODING_DONTKNOW );
-            }
-
-            ucsdet_close( ucd );
+            eSepType = SeparatorType::SEPARATOR;
+            mxRbSeparated->set_active(true);
         }
+        else
+            mxRbFixed->set_active(true);
+    }
+    else if (eSepType == SeparatorType::SEPARATOR)
+        mxRbSeparated->set_active(true);
+    else
+        mxRbDetectSep->set_active(true);
+    if (nFromRow != 1)
+        mxNfRow->set_value(nFromRow);
 
-        mnStreamPos = mpDatStream->Tell();
+    // Clipboard is always Unicode, else rely on default/config.
+    rtl_TextEncoding ePreselectUnicode = (meCall == SC_IMPORTFILE ?
+            RTL_TEXTENCODING_DONTKNOW : RTL_TEXTENCODING_UNICODE);
+
+    // Detect character set only once and then use it for "Detect" option.
+    SvStreamEndian eEndian;
+    SfxObjectShell::DetectCharSet(*mpDatStream, meDetectedCharSet, eEndian);
+    if (meDetectedCharSet == RTL_TEXTENCODING_UNICODE)
+        mpDatStream->SetEndian(eEndian);
+    else if ( meDetectedCharSet == RTL_TEXTENCODING_DONTKNOW )
+    {
+        meDetectedCharSet = osl_getThreadTextEncoding();
+        // Prefer UTF-8, as UTF-16 would have already been detected from the stream.
+        // This gives a better chance that the file is going to be opened correctly.
+        if ( meDetectedCharSet == RTL_TEXTENCODING_UNICODE && mpDatStream )
+            meDetectedCharSet = RTL_TEXTENCODING_UTF8;
     }
 
-    if (aOptions && !maFieldSeparators.isEmpty())
-        SetSeparators(0);
-    else if (bIsTSV)
+    if (bIsTSV)
         SetSeparators('\t');
     else
-    {
-        // Some MS-Excel convention is the first line containing the field
-        // separator as "sep=|" (without quotes and any field separator
-        // character). The second possibility seems to be it is present *with*
-        // quotes so it shows up as cell content *including* the separator and
-        // can be preserved during round trips. Check for an exact match of
-        // any such and set separator.
-        /* TODO: it is debatable whether the unquoted form should rather be
-         * treated special to actually include the separator in the field data.
-         * Currently it does not. */
-        sal_Unicode cSep = 0;
-        OUString aLine;
-        // Try to read one more character, if more than 7 it can't be an exact
-        // match of any.
-        mpDatStream->ReadUniOrByteStringLine( aLine, mpDatStream->GetStreamCharSet(), 8);
-        mpDatStream->Seek(mnStreamPos);
-        if (aLine.getLength() == 8)
-            ;   // nothing
-        else if (aLine.getLength() == 5 && aLine.startsWithIgnoreAsciiCase("sep="))
-            cSep = aLine[4];
-        else if (aLine.getLength() == 7 && aLine[6] == '"' && aLine.startsWithIgnoreAsciiCase("\"sep="))
-            cSep = aLine[5];
-
-        // Set Separators in the dialog from maFieldSeparators (empty are not
-        // set) or an optionally defined by file content field separator.
-        SetSeparators(cSep);
-    }
+        SetSeparators(0);
 
     // Get Separators from the dialog (empty are set from default)
-    maFieldSeparators = GetSeparators();
+    maFieldSeparators = GetActiveSeparators();
 
     mxNfRow->connect_value_changed( LINK( this, ScImportAsciiDlg, FirstRowHdl ) );
 
@@ -551,22 +508,15 @@ ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, std::u16string_view aD
     // Insert one "SYSTEM" entry for compatibility in AsciiOptions and system
     // independent document linkage.
     mxLbCharSet->InsertTextEncoding( RTL_TEXTENCODING_DONTKNOW, ScResId( SCSTR_CHARSET_USER ) );
-    if ( ePreselectUnicode == RTL_TEXTENCODING_DONTKNOW )
-    {
-        rtl_TextEncoding eSystemEncoding = osl_getThreadTextEncoding();
-        // Prefer UTF-8, as UTF-16 would have already been detected from the stream.
-        // This gives a better chance that the file is going to be opened correctly.
-        if ( ( eSystemEncoding == RTL_TEXTENCODING_UNICODE ) && mpDatStream )
-            eSystemEncoding = RTL_TEXTENCODING_UTF8;
-        mxLbCharSet->SelectTextEncoding( eSystemEncoding );
-    }
-    else
-    {
-        mxLbCharSet->SelectTextEncoding( ePreselectUnicode );
-    }
+    // Insert one for detecting charset.
+    mxLbCharSet->InsertTextEncoding( RTL_TEXTENCODING_USER_DETECTED, "- " + ScResId( SCSTR_AUTOMATIC ) + " -" );
 
-    if (nCharSet >= 0 && ePreselectUnicode == RTL_TEXTENCODING_DONTKNOW)
+    if (ePreselectUnicode != RTL_TEXTENCODING_DONTKNOW)
+        mxLbCharSet->SelectTextEncoding( ePreselectUnicode );
+    else if (nCharSet >= 0 && !bBeforeDetection)
         mxLbCharSet->set_active(nCharSet);
+    else
+        mxLbCharSet->SelectTextEncoding(RTL_TEXTENCODING_USER_DETECTED);
 
     SetSelectedCharSet();
     mxLbCharSet->connect_changed( LINK( this, ScImportAsciiDlg, CharSetHdl ) );
@@ -592,10 +542,10 @@ ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, std::u16string_view aD
     mxTableBox->InitTypes( *mxLbType );
     mxTableBox->SetColTypeHdl( LINK( this, ScImportAsciiDlg, ColTypeHdl ) );
 
+    mxRbDetectSep->connect_toggled( LINK( this, ScImportAsciiDlg, RbSepFixHdl ) );
     mxRbSeparated->connect_toggled( LINK( this, ScImportAsciiDlg, RbSepFixHdl ) );
     mxRbFixed->connect_toggled( LINK( this, ScImportAsciiDlg, RbSepFixHdl ) );
 
-    SetupSeparatorCtrls();
     RbSepFix();
 
     UpdateVertical();
@@ -715,9 +665,9 @@ void ScImportAsciiDlg::GetOptions( ScAsciiOptions& rOpt )
     rOpt.SetFixedLen( mxRbFixed->get_active() );
     rOpt.SetStartRow( mxNfRow->get_value() );
     mxTableBox->FillColumnData( rOpt );
-    if( mxRbSeparated->get_active() )
+    if( mxRbSeparated->get_active() || mxRbDetectSep->get_active())
     {
-        rOpt.SetFieldSeps( GetSeparators() );
+        rOpt.SetFieldSeps( GetActiveSeparators() );
         rOpt.SetMergeSeps( mxCkbAsOnce->get_active() );
         rOpt.SetRemoveSpace( mxCkbRemoveSpace->get_active() );
         rOpt.SetTextSep( lcl_CharFromCombo( *mxCbTextSep, SCSTR_TEXTSEP ) );
@@ -732,9 +682,9 @@ void ScImportAsciiDlg::GetOptions( ScAsciiOptions& rOpt )
 
 void ScImportAsciiDlg::SaveParameters()
 {
-    lcl_SaveSeparators( maFieldSeparators, mxCbTextSep->get_active_text(), mxCkbAsOnce->get_active(),
+    lcl_SaveSeparators( GetSeparators(), mxCbTextSep->get_active_text(), mxCkbAsOnce->get_active(),
                      mxCkbQuotedAsText->get_active(), mxCkbDetectNumber->get_active(), mxCkbDetectScientificNumber->get_active(),
-                     mxRbFixed->get_active(),
+                     mxRbFixed->get_active() ? FIXED : (mxRbDetectSep->get_active() ? DETECT_SEPARATOR : SEPARATOR),
                      mxNfRow->get_value(),
                      mxLbCharSet->get_active(),
                      static_cast<sal_uInt16>(mxLbCustomLang->get_active_id()),
@@ -788,10 +738,27 @@ void ScImportAsciiDlg::SetSeparators( sal_Unicode cSep )
 
 void ScImportAsciiDlg::SetSelectedCharSet()
 {
+    rtl_TextEncoding eOldCharSet = meCharSet;
     meCharSet = mxLbCharSet->GetSelectTextEncoding();
+    mbCharSetDetect = (meCharSet == RTL_TEXTENCODING_USER_DETECTED);
     mbCharSetSystem = (meCharSet == RTL_TEXTENCODING_DONTKNOW);
-    if( mbCharSetSystem )
+    if (mbCharSetDetect)
+    {
+        meCharSet = meDetectedCharSet;
+        mxFtDetectedCharSet->set_label(SvxTextEncodingTable::GetTextString(meCharSet));
+    }
+    else if( mbCharSetSystem )
+    {
         meCharSet = osl_getThreadTextEncoding();
+        mxFtDetectedCharSet->set_label(SvxTextEncodingTable::GetTextString(meCharSet));
+    }
+    else
+        mxFtDetectedCharSet->set_label(SvxTextEncodingTable::GetTextString(meCharSet));
+
+    if (eOldCharSet != meCharSet)
+        DetectCsvSeparators();
+
+    RbSepFix();
 }
 
 OUString ScImportAsciiDlg::GetSeparators() const
@@ -810,6 +777,17 @@ OUString ScImportAsciiDlg::GetSeparators() const
     return aSepChars;
 }
 
+OUString ScImportAsciiDlg::GetActiveSeparators() const
+{
+    if (mxRbSeparated->get_active())
+        return GetSeparators();
+
+    if (mxRbDetectSep->get_active())
+        return maDetectedFieldSeps;
+
+    return OUString();
+}
+
 void ScImportAsciiDlg::SetupSeparatorCtrls()
 {
     bool bEnable = mxRbSeparated->get_active();
@@ -817,12 +795,41 @@ void ScImportAsciiDlg::SetupSeparatorCtrls()
     mxCkbSemicolon->set_sensitive( bEnable );
     mxCkbComma->set_sensitive( bEnable );
     mxCkbSpace->set_sensitive( bEnable );
-    mxCkbRemoveSpace->set_sensitive( bEnable );
     mxCkbOther->set_sensitive( bEnable );
     mxEdOther->set_sensitive( bEnable );
+
+    bEnable = bEnable || mxRbDetectSep->get_active();
+    mxCkbRemoveSpace->set_sensitive( bEnable );
     mxCkbAsOnce->set_sensitive( bEnable );
     mxFtTextSep->set_sensitive( bEnable );
     mxCbTextSep->set_sensitive( bEnable );
+
+    OUString aSepName;
+    if (maDetectedFieldSeps.isEmpty())
+        aSepName += ScResId(SCSTR_NONE);
+    else
+    {
+        for (int idx = 0; idx < maDetectedFieldSeps.getLength(); idx ++)
+        {
+            if (idx > 0)
+                aSepName += u" ";
+
+            if (maDetectedFieldSeps[idx] == u' ')
+                aSepName += ScResId(SCSTR_FIELDSEP_SPACE);
+            else if (maDetectedFieldSeps[idx] == u'\t')
+                aSepName += ScResId(SCSTR_FIELDSEP_TAB);
+            else
+                aSepName += OUStringChar(maDetectedFieldSeps[idx]);
+        }
+    }
+    mxRbDetectSep->set_label(ScResId(SCSTR_DETECTED).replaceFirst( "%1", aSepName));
+}
+
+void ScImportAsciiDlg::DetectCsvSeparators()
+{
+    mpDatStream->Seek(mnStreamInitPos);
+    SfxObjectShell::DetectCsvSeparators(*mpDatStream, meCharSet, maDetectedFieldSeps, mcTextSep);
+    mpDatStream->Seek(mnStreamPos);
 }
 
 void ScImportAsciiDlg::UpdateVertical()
@@ -835,10 +842,17 @@ void ScImportAsciiDlg::UpdateVertical()
 void ScImportAsciiDlg::RbSepFix()
 {
     weld::WaitObject aWaitObj(m_xDialog.get());
-    if( mxRbFixed->get_active() )
-        mxTableBox->SetFixedWidthMode();
+    if (mxRbSeparated->get_active() || mxRbDetectSep->get_active())
+    {
+        maFieldSeparators = GetActiveSeparators();
+        if (mxTableBox->IsFixedWidthMode())
+            mxTableBox->SetSeparatorsMode();
+        else
+            mxTableBox->Refresh();
+    }
     else
-        mxTableBox->SetSeparatorsMode();
+        mxTableBox->SetFixedWidthMode();
+
     SetupSeparatorCtrls();
 }
 
@@ -892,13 +906,26 @@ void ScImportAsciiDlg::SeparatorHdl(const weld::Widget* pCtrl)
         mxCkbOther->set_active(!mxEdOther->get_text().isEmpty());
 
     OUString aOldFldSeps( maFieldSeparators);
-    maFieldSeparators = GetSeparators();
     sal_Unicode cOldSep = mcTextSep;
     mcTextSep = lcl_CharFromCombo( *mxCbTextSep, SCSTR_TEXTSEP );
     // Any separator changed may result in completely different lines due to
     // embedded line breaks.
-    if (cOldSep != mcTextSep || aOldFldSeps != maFieldSeparators)
-        UpdateVertical();
+    if (cOldSep != mcTextSep)
+    {
+        DetectCsvSeparators();
+
+        SetupSeparatorCtrls();
+
+        maFieldSeparators = GetActiveSeparators();
+        if (aOldFldSeps != maFieldSeparators)
+        {
+            UpdateVertical();
+            mxTableBox->Refresh();
+            return;
+        }
+    }
+    else
+        maFieldSeparators = GetActiveSeparators();
 
     mxTableBox->GetGrid().Execute( CSVCMD_NEWCELLTEXTS );
 }
@@ -931,14 +958,7 @@ IMPL_LINK(ScImportAsciiDlg, LbColTypeHdl, weld::ComboBox&, rListBox, void)
 
 IMPL_LINK_NOARG(ScImportAsciiDlg, UpdateTextHdl, ScCsvTableBox&, void)
 {
-    // Checking the separator can only be done once for the very first time
-    // when the dialog wasn't already presented to the user.
-    // As a side effect this has the benefit that the check is only done on the
-    // first set of visible lines.
-    mbDetectSep = (mbDetectSep && !mxRbFixed->get_active()
-                && (!mxCkbTab->get_active() || !mxCkbSemicolon->get_active()
-                    || !mxCkbComma->get_active() || !mxCkbSpace->get_active()));
-    sal_Unicode cDetectSep = (mbDetectSep ? 0 : 0xffff);
+    sal_Unicode cDetectSep = 0xffff;
 
     sal_Int32 nBaseLine = mxTableBox->GetGrid().GetFirstVisLine();
     sal_Int32 nRead = mxTableBox->GetGrid().GetVisLineCount();
@@ -958,24 +978,6 @@ IMPL_LINK_NOARG(ScImportAsciiDlg, UpdateTextHdl, ScCsvTableBox&, void)
     for (; i < CSV_PREVIEW_LINES; i++)
         maPreviewLine[i].clear();
 
-    if (mbDetectSep)
-    {
-        mbDetectSep = false;
-        if (cDetectSep)
-        {
-            // Expect separator to be appended by now so all subsequent
-            // GetLine()/ReadCsvLine() actually used it.
-            assert(maFieldSeparators.endsWith(OUStringChar(cDetectSep)));
-            // Preselect separator in UI.
-            switch (cDetectSep)
-            {
-                case '\t':  mxCkbTab->set_active(true);         break;
-                case ';':   mxCkbSemicolon->set_active(true);   break;
-                case ',':   mxCkbComma->set_active(true);       break;
-                case ' ':   mxCkbSpace->set_active(true);       break;
-            }
-        }
-    }
 
     mxTableBox->GetGrid().Execute( CSVCMD_SETLINECOUNT, mnRowPosCount);
     bool bMergeSep = mxCkbAsOnce->get_active();
diff --git a/sc/source/ui/docshell/docsh.cxx b/sc/source/ui/docshell/docsh.cxx
index 4b8e5f041c8d..7ef50cc7a8d3 100644
--- a/sc/source/ui/docshell/docsh.cxx
+++ b/sc/source/ui/docshell/docsh.cxx
@@ -1354,7 +1354,7 @@ bool ScDocShell::ConvertFrom( SfxMedium& rMedium )
 
             if ( const SfxStringItem* pOptionsItem = rMedium.GetItemSet().GetItemIfSet( SID_FILE_FILTEROPTIONS ) )
             {
-                aOptions.ReadFromString( pOptionsItem->GetValue() );
+                aOptions.ReadFromString( pOptionsItem->GetValue(), rMedium.GetInStream() );
                 bOptInit = true;
             }
 
diff --git a/sc/source/ui/inc/asciiopt.hxx b/sc/source/ui/inc/asciiopt.hxx
index 6028b8825d94..af97d4b0f2ba 100644
--- a/sc/source/ui/inc/asciiopt.hxx
+++ b/sc/source/ui/inc/asciiopt.hxx
@@ -52,7 +52,7 @@ public:
 
     static const sal_Unicode cDefaultTextSep = '"';
 
-    void            ReadFromString( std::u16string_view rString );
+    void            ReadFromString( std::u16string_view rString, SvStream* pStream4Detect = nullptr );
     OUString        WriteToString() const;
 
     rtl_TextEncoding    GetCharSet() const      { return eCharSet; }
diff --git a/sc/source/ui/inc/csvtablebox.hxx b/sc/source/ui/inc/csvtablebox.hxx
index e2392a478f4c..9d626493fd54 100644
--- a/sc/source/ui/inc/csvtablebox.hxx
+++ b/sc/source/ui/inc/csvtablebox.hxx
@@ -72,10 +72,12 @@ public:
 
     // common table box handling ----------------------------------------------
 public:
+    void                        Refresh();
     /** Sets the control to separators mode. */
     void                        SetSeparatorsMode();
     /** Sets the control to fixed width mode. */
     void                        SetFixedWidthMode();
+    bool                        IsFixedWidthMode(){ return mbFixedMode; }
 
     ScCsvRuler& GetRuler() { return *mxRuler; }
     ScCsvGrid& GetGrid() { return *mxGrid; }
diff --git a/sc/source/ui/inc/scuiasciiopt.hxx b/sc/source/ui/inc/scuiasciiopt.hxx
index ee8ca78b221d..5b19b8c4d3ee 100644
--- a/sc/source/ui/inc/scuiasciiopt.hxx
+++ b/sc/source/ui/inc/scuiasciiopt.hxx
@@ -31,29 +31,34 @@ class SvxTextEncodingBox;
 
 class ScImportAsciiDlg : public weld::GenericDialogController
 {
-    SvStream*                   mpDatStream;
+    SvStream*                       mpDatStream;
     sal_uLong                       mnStreamPos;
+    sal_uLong                       mnStreamInitPos;
     std::unique_ptr<sal_uLong[]>    mpRowPosArray;
     sal_uLong                       mnRowPosCount;
 
     OUString               maPreviewLine[ CSV_PREVIEW_LINES ];
 
     OUString                    maFieldSeparators;  // selected field separators
+    OUString                    maDetectedFieldSeps; // detected field seps
     sal_Unicode                 mcTextSep;
 
     rtl_TextEncoding            meCharSet;          /// Selected char set.
+    rtl_TextEncoding            meDetectedCharSet;  /// This is computed only once at initialization, so store it.
     bool                        mbCharSetSystem;    /// Is System char set selected?
+    bool                        mbCharSetDetect;    /// Should we autodetect character set ?
     ScImportAsciiCall           meCall;             /// How the dialog is called (see asciiopt.hxx)
-    bool                        mbDetectSep;        /// Whether to detect a possible separator.
 
     std::unique_ptr<weld::Label> mxFtCharSet;
     std::unique_ptr<SvxTextEncodingBox> mxLbCharSet;
+    std::unique_ptr<weld::Label> mxFtDetectedCharSet;
     std::unique_ptr<weld::Label> mxFtCustomLang;
     std::unique_ptr<SvxLanguageBox> mxLbCustomLang;
 
     std::unique_ptr<weld::Label> mxFtRow;
     std::unique_ptr<weld::SpinButton> mxNfRow;
 
+    std::unique_ptr<weld::RadioButton> mxRbDetectSep;
     std::unique_ptr<weld::RadioButton> mxRbFixed;
     std::unique_ptr<weld::RadioButton> mxRbSeparated;
 
@@ -83,8 +88,7 @@ class ScImportAsciiDlg : public weld::GenericDialogController
 public:
                                 ScImportAsciiDlg(
                                     weld::Window* pParent, std::u16string_view aDatName,
-                                    SvStream* pInStream, ScImportAsciiCall eCall,
-                                    const ScAsciiOptions* aOptions = nullptr );
+                                    SvStream* pInStream, ScImportAsciiCall eCall);
                                 virtual ~ScImportAsciiDlg() override;
 
     void                        GetOptions( ScAsciiOptions& rOpt );
@@ -98,6 +102,8 @@ private:
     void                        SetSeparators( sal_Unicode cSep );
     /** Returns all separator characters in a string. */
     OUString                    GetSeparators() const;
+    OUString                    GetActiveSeparators() const;
+    void                        DetectCsvSeparators();
 
     /** Enables or disables all separator checkboxes and edit fields. */
     void                        SetupSeparatorCtrls();
diff --git a/sc/source/ui/unoobj/filtuno.cxx b/sc/source/ui/unoobj/filtuno.cxx
index e5ee8de17d29..575d66147b64 100644
--- a/sc/source/ui/unoobj/filtuno.cxx
+++ b/sc/source/ui/unoobj/filtuno.cxx
@@ -179,25 +179,15 @@ sal_Int16 SAL_CALL ScFilterOptionsObj::execute()
     {
         //  ascii import is special...
 
-        ScAsciiOptions aInOptions, *pInOptions = nullptr;
         INetURLObject aURL( aFileName );
         // tdf#132421 - don't URL encode filename for the import ASCII dialog title
         OUString aPrivDatName(aURL.GetLastName(INetURLObject::DecodeMechanism::Unambiguous));
         std::unique_ptr<SvStream> pInStream;
         if ( xInputStream.is() )
-        {
             pInStream = utl::UcbStreamHelper::CreateStream( xInputStream );
 
-            if (aFilterOptions.isEmpty())
-                aFilterOptions = "DETECT,34,DETECT,,,,,,,,,,,,";
-            SfxObjectShell::DetectCsvFilterOptions(*pInStream, aFilterOptions);
-
-            aInOptions.ReadFromString(aFilterOptions);
-            pInOptions = &aInOptions;
-        }
-
         ScopedVclPtr<AbstractScImportAsciiDlg> pDlg(pFact->CreateScImportAsciiDlg(Application::GetFrameWeld(xDialogParent), aPrivDatName,
-                                                                                  pInStream.get(), SC_IMPORTFILE, pInOptions));
+                                                                                  pInStream.get(), SC_IMPORTFILE));
         if ( pDlg->Execute() == RET_OK )
         {
             ScAsciiOptions aOptions;
diff --git a/sc/source/ui/view/cellsh2.cxx b/sc/source/ui/view/cellsh2.cxx
index 0323ca8c219f..bb7c4cf831b3 100644
--- a/sc/source/ui/view/cellsh2.cxx
+++ b/sc/source/ui/view/cellsh2.cxx
@@ -1040,6 +1040,7 @@ void ScCellShell::ExecuteDB( SfxRequest& rReq )
                     ScImportExport::SetNoEndianSwap( aStream );
                     aExport.ExportStream( aStream, OUString(), SotClipboardFormatId::STRING );
 
+                    aStream.Seek(0);
                     ScAbstractDialogFactory* pFact = ScAbstractDialogFactory::Create();
                     ScopedVclPtr<AbstractScImportAsciiDlg> pDlg(pFact->CreateScImportAsciiDlg(
                             pTabViewShell->GetFrameWeld(), OUString(), &aStream, SC_TEXTTOCOLUMNS));
diff --git a/sc/uiconfig/scalc/ui/textimportcsv.ui b/sc/uiconfig/scalc/ui/textimportcsv.ui
index a7ab406a563c..0f841c5e4910 100644
--- a/sc/uiconfig/scalc/ui/textimportcsv.ui
+++ b/sc/uiconfig/scalc/ui/textimportcsv.ui
@@ -137,7 +137,7 @@
                       </object>
                       <packing>
                         <property name="left-attach">0</property>
-                        <property name="top-attach">1</property>
+                        <property name="top-attach">2</property>
                       </packing>
                     </child>
                     <child>
@@ -151,7 +151,7 @@
                       </object>
                       <packing>
                         <property name="left-attach">0</property>
-                        <property name="top-attach">2</property>
+                        <property name="top-attach">3</property>
                       </packing>
                     </child>
                     <child>
@@ -170,6 +170,18 @@
                       </packing>
                     </child>
                     <child>
+                      <object class="GtkLabel" id="textdetectedcharset">
+                        <property name="visible">True</property>
+                        <property name="can-focus">False</property>
+                        <property name="halign">start</property>
+                        <property name="label" translatable="no" context="textimportcsv|textcharset"></property>
+                      </object>
+                      <packing>
+                        <property name="left-attach">1</property>
+                        <property name="top-attach">1</property>
+                      </packing>
+                    </child>
+                    <child>
                       <object class="GtkComboBoxText" id="language">
                         <property name="visible">True</property>
                         <property name="can-focus">False</property>
@@ -181,7 +193,7 @@
                       </object>
                       <packing>
                         <property name="left-attach">1</property>
-                        <property name="top-attach">1</property>
+                        <property name="top-attach">2</property>
                       </packing>
                     </child>
                     <child>
@@ -200,7 +212,7 @@
                       </object>
                       <packing>
                         <property name="left-attach">1</property>
-                        <property name="top-attach">2</property>
+                        <property name="top-attach">3</property>
                       </packing>
                     </child>
                   </object>
@@ -252,7 +264,7 @@
                             <property name="receives-default">False</property>
                             <property name="use-underline">True</property>
                             <property name="draw-indicator">True</property>
-                            <property name="group">toseparatedby</property>
+                            <property name="group">todetectseparator</property>
                             <child internal-child="accessible">
                               <object class="AtkObject" id="tofixedwidth-atkobject">
                                 <property name="AtkObject::accessible-description" translatable="yes" context="textimportcsv|extended_tip|tofixedwidth">Separates fixed-width data (equal number of characters) into columns.</property>
@@ -272,8 +284,8 @@
                             <property name="can-focus">True</property>
                             <property name="receives-default">False</property>
                             <property name="use-underline">True</property>
-                            <property name="active">True</property>
                             <property name="draw-indicator">True</property>
+                            <property name="group">todetectseparator</property>
                             <child internal-child="accessible">
                               <object class="AtkObject" id="toseparatedby-atkobject">
                                 <property name="AtkObject::accessible-description" translatable="yes" context="textimportcsv|extended_tip|toseparatedby">Select the separator used in your data.</property>
@@ -286,6 +298,27 @@
                             <property name="position">1</property>
                           </packing>
                         </child>
+                        <child>
+                          <object class="GtkRadioButton" id="todetectseparator">
+                            <property name="label" translatable="yes" context="textimportcsv|todetectseparator">Detected</property>
+                            <property name="visible">True</property>
+                            <property name="can-focus">True</property>
+                            <property name="receives-default">False</property>
+                            <property name="use-underline">True</property>
+                            <property name="active">True</property>
+                            <property name="draw-indicator">True</property>
+                            <child internal-child="accessible">
+                              <object class="AtkObject" id="todetectseparator-atkobject">
+                                <property name="AtkObject::accessible-description" translatable="yes" context="textimportcsv|extended_tip|todetectseparator">Use detected separator.</property>
+                              </object>
+                            </child>
+                          </object>
+                          <packing>
+                            <property name="expand">False</property>
+                            <property name="fill">True</property>
+                            <property name="position">2</property>
+                          </packing>
+                        </child>
                       </object>
                       <packing>
                         <property name="expand">False</property>
diff --git a/sfx2/source/doc/objstor.cxx b/sfx2/source/doc/objstor.cxx
index 17f34ec5ad88..7a9f94da4337 100644
--- a/sfx2/source/doc/objstor.cxx
+++ b/sfx2/source/doc/objstor.cxx
@@ -119,7 +119,6 @@
 #include "objstor.hxx"
 #include "exoticfileloadexception.hxx"
 #include <unicode/ucsdet.h>
-#include <unicode/ucnv.h>
 #include <o3tl/string_view.hxx>
 
 using namespace ::com::sun::star;
@@ -960,7 +959,7 @@ void SfxObjectShell::DetectCsvSeparators(SvStream& stream, rtl_TextEncoding& eCh
     std::vector<std::unordered_map<sal_Unicode, sal_uInt32>> aLinesCharsCount;
     std::unordered_map<sal_Unicode, sal_uInt32> aCharsCount;
     std::unordered_map<sal_Unicode, std::pair<sal_uInt32, sal_uInt32>> aStats;
-    constexpr sal_uInt32 nMaxLinesToProcess = 20;
+    constexpr sal_uInt32 nTimeout = 500; // Timeout for detection in ms
     sal_uInt32 nLinesCount = 0;
     OUString sInitSeps;
     OUString sCommonSeps = u",\t;:| \\/"_ustr;//Sorted by importance
@@ -970,17 +969,18 @@ void SfxObjectShell::DetectCsvSeparators(SvStream& stream, rtl_TextEncoding& eCh
     sal_uInt32 nMaxLinesSameChar = 0;
     sal_uInt32 nMinDiffs = 0xFFFFFFFF;
     sal_uInt64 nInitPos = stream.Tell();
+    sal_uInt64 nStartTime = tools::Time::GetSystemTicks();
 
     if (!cStringDelimiter)
         cStringDelimiter = '\"';
 
     for (sal_Int32 nComSepIdx = sCommonSeps.getLength() - 1; nComSepIdx >= 0; nComSepIdx --)
         usetCommonSeps.insert(sCommonSeps[nComSepIdx]);
-    aLinesCharsCount.reserve(nMaxLinesToProcess);
+    aLinesCharsCount.reserve(128);
     separators = "";
 
     stream.StartReadingUnicodeText(eCharSet);
-    while (stream.ReadUniOrByteStringLine(sLine, eCharSet) && aLinesCharsCount.size() < nMaxLinesToProcess)
+    while (stream.ReadUniOrByteStringLine(sLine, eCharSet) && (tools::Time::GetSystemTicks() - nStartTime < nTimeout))
     {
         if (sLine.isEmpty())
             continue;
@@ -1034,7 +1034,7 @@ void SfxObjectShell::DetectCsvSeparators(SvStream& stream, rtl_TextEncoding& eCh
         {
             auto aCurStats = aStats.find(aCurLineChar->first);
             if (aCurStats == aStats.cend())
-                aStats.insert(std::pair<sal_Unicode, std::pair<sal_uInt32, sal_uInt32>>(aCurLineChar->first, std::pair<sal_uInt32, sal_uInt32>(1, 1)));
+                aCurStats = aStats.insert(std::pair<sal_Unicode, std::pair<sal_uInt32, sal_uInt32>>(aCurLineChar->first, std::pair<sal_uInt32, sal_uInt32>(1, 1))).first;
             else
             {
                 aCurStats->second.first ++;// Increment number of lines that contain the current character
@@ -1048,17 +1048,19 @@ void SfxObjectShell::DetectCsvSeparators(SvStream& stream, rtl_TextEncoding& eCh
                 }
                 if (aPrevLineChar == aLinesCharsCount.cend())
                     aCurStats->second.second ++;// Increment number of different number of occurrences.
-
-                // Update the maximum of number of lines that contain the same character. This is a global value.
-                if (nMaxLinesSameChar < aCurStats->second.first)
-                    nMaxLinesSameChar = aCurStats->second.first;
             }
+
+            // Update the maximum of number of lines that contain the same character. This is a global value.
+            if (nMaxLinesSameChar < aCurStats->second.first)
+                nMaxLinesSameChar = aCurStats->second.first;
         }
 
         aLinesCharsCount.emplace_back();
         aLinesCharsCount[aLinesCharsCount.size() - 1].swap(aCharsCount);
     }
 
+    SAL_INFO("sfx.doc", "" << nLinesCount << " lines processed in " << tools::Time::GetSystemTicks() - nStartTime << " ms while detecting separator.");
+
     // Compute the global minimum of different number of occurrences.
     // But only for characters which occur in a maximum number of lines (previously computed).
     for (auto it=aStats.cbegin(); it != aStats.cend(); it++)
@@ -1086,8 +1088,6 @@ void SfxObjectShell::DetectCsvSeparators(SvStream& stream, rtl_TextEncoding& eCh
             }
         }
 
-        if (nInitSepIdx >= 0)
-            break;
     }
 
     stream.Seek(nInitPos);
@@ -1133,9 +1133,9 @@ void SfxObjectShell::DetectCsvFilterOptions(SvStream& stream, OUString& aFilterO
 
 
     //Detect separators
-    aFilterOptions = "";
     if (aSeps == aDetect)
     {
+        aFilterOptions = "";
         OUString separators;
         DetectCsvSeparators(stream, eCharSet, separators, static_cast<sal_Unicode>(o3tl::toInt32(aDelimiter)));
 
@@ -1198,7 +1198,6 @@ ErrCode SfxObjectShell::HandleFilter( SfxMedium* pMedium, SfxObjectShell const *
     // FilterOptions should not be detected here (the detection is done before entering
     // interactive state). For now this is focused on CSV files.
     DetectFilterOptions(pMedium);
-    //::sleep(30);
 
     if ( !pData && (bTiledRendering || !pOptions) )
     {