none
When we convert Excel to text (with the help of text then content cut) RRS feed

  • Question

  • Hi ,

    When I convert excel to text with the help of excel saveAs method then extra double quotes come with string(text) data, for this 

    Problem after did google, we found one solution to use .prn(printer file) then issue resolved but it's printer file so data cut.full data not coming in printer file. I'm not found the proper solution so that.

    public static void GetExcelToText(string sFilePath)
            {
                Excelintrop.Application appExl = null;
                Excelintrop.Workbook workbook = null;
                Excelintrop.Worksheet nwSheet = null;
                List<string> filePaths = null;
                string temPath = sFilePath;
                try
                {
                    filePaths=new List<string>();
                    appExl = new Excelintrop.Application();
                    appExl.DisplayAlerts = false;
                    workbook = appExl.Workbooks.Open(sFilePath, Missing.Value, Missing.Value, Missing.Value,
                                                      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                                      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                                      Missing.Value, Missing.Value, Missing.Value);
                    
                    for (int i = 1; i <= workbook.Sheets.Count;i++)
                    {
                        temPath = Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location)+ Path.DirectorySeparatorChar + Guid.NewGuid().ToString() + ".prn";
                        nwSheet = (Excelintrop.Worksheet)workbook.Sheets[i];
                                           
                        Excelintrop.Range rang = nwSheet.UsedRange;
                        
                        rang.Cells.WrapText = true;
                        rang.Columns.AutoFit();

                        nwSheet.SaveAs(Filename: temPath, FileFormat: Excelintrop.XlFileFormat.xlTextPrinter, CreateBackup:false);                   
                        filePaths.Add(temPath);
                    }
                    workbook.Close();
                    appExl.Workbooks.Close();
                    appExl.Quit();

                    using (var output = new StreamWriter(Path.ChangeExtension(sFilePath, ".txt")))
                    {
                       foreach(var path in filePaths)
                        {
                            using (var input = new StreamReader(path))
                            {
                                output.WriteLine(input.ReadToEnd());
                            }
                        }
                    }
                  
                //File.Delete(Path.ChangeExtension(sFilePath, ".txt"));
                File.Move(Path.ChangeExtension(sFilePath, ".prn"), Path.ChangeExtension(sFilePath, ".txt"));
                File.Delete(Path.ChangeExtension(sFilePath, ".prn"));
                }            
                catch (Exception ex)
                {
                    Log.Debug("ProLaw.Utils: OfficeExtensionFunctions: GetExcelToText" + ex.Message);
                }
                finally
                {
                    foreach (var path in filePaths)
                    {
                        File.Delete(path);
                    }
                    filePaths = null;
                    File.Delete(Path.ChangeExtension(sFilePath, ".xls"));
                    appExl.Workbooks.Close();
                    appExl.Quit();
                }
            }

    what is the best approach to get full data without double quotes?

    Thanks,

    Kapil



    Thanks, Kapil Khare

    Wednesday, September 27, 2017 5:43 AM

All replies

  • Hi Kapil Khare,

    How do you use the SaveAs method? I tried use SaveAs manually to save an Excel file as a .txt file and it does not create quotes for string data. It seems that you had post a screenshot in the thread but it missed, I would suggest you add the screenshot again.

    I have also tried your code, it also works for me too. What do you mean full data not coming in printer file? Which part of data is missed?

    I would suggest you share a simply excel file and the screenshot of your current result so we could try to reproduce your issue.

    You could share file/screenshots via One Drive and put link here.

    Besides, we also need know the version of your office. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 28, 2017 6:18 AM
  • Hi Kapil Khare,

    did you try to use "xlTextWindows" instead of "xlTextPrinter"?

    if you did not try it then you can try to make a test with it.

    let us know if that solves your issue or not.

    Reference:

    XlFileFormat Enumeration (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 10, 2017 6:59 AM
    Moderator