none
Best way to convert large xlsx file into tab delimited text file using script RRS feed

  • Question

  • Hi,

    I have a large size xlsx file which contains over 15 separated sheet. I want to convert each of them into tab delimited text file. Would be some best way?

    I checked google. I could use OLEDB. I am not sure this is the best/efficient way or not.

    Hopefully I could use PowerShell or C#

    thanks

    M


    Maggie

    • Moved by Terry Xu - MSFT Friday, June 1, 2018 2:29 AM Excel Develop Related
    Thursday, May 31, 2018 8:24 PM

Answers

  • Hello maggie0099,

    As far as I know, Excel has provided the option to save workbook as Tab Delimited text file directly.

    So the code maybe

       //add reference to Microsoft Office Interop Excell dll
                string soureFilePath = @"C:\Users\terryx\Desktop\Book1.xlsx";
                string savePath = @"C:\Users\terryx\Desktop\";
                Microsoft.Office.Interop.Excel.Application xlApplication = new Microsoft.Office.Interop.Excel.Application();
                xlApplication.Visible = true;
                Microsoft.Office.Interop.Excel.Workbook workbook = xlApplication.Workbooks.Open(soureFilePath);
                foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in workbook.Worksheets) {
                    worksheet.Activate();
                    workbook.SaveAs(savePath + worksheet.Name + ".txt", Microsoft.Office.Interop.Excel.XlFileFormat.xlCurrentPlatformText);
                }

    Best Regards,

    Terry


    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.

    • Proposed as answer by Terry Xu - MSFT Monday, June 4, 2018 1:31 AM
    • Marked as answer by maggie0099 Tuesday, June 5, 2018 2:22 PM
    Friday, June 1, 2018 3:02 AM

All replies

  • Hello maggie0099,

    As far as I know, Excel has provided the option to save workbook as Tab Delimited text file directly.

    So the code maybe

       //add reference to Microsoft Office Interop Excell dll
                string soureFilePath = @"C:\Users\terryx\Desktop\Book1.xlsx";
                string savePath = @"C:\Users\terryx\Desktop\";
                Microsoft.Office.Interop.Excel.Application xlApplication = new Microsoft.Office.Interop.Excel.Application();
                xlApplication.Visible = true;
                Microsoft.Office.Interop.Excel.Workbook workbook = xlApplication.Workbooks.Open(soureFilePath);
                foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in workbook.Worksheets) {
                    worksheet.Activate();
                    workbook.SaveAs(savePath + worksheet.Name + ".txt", Microsoft.Office.Interop.Excel.XlFileFormat.xlCurrentPlatformText);
                }

    Best Regards,

    Terry


    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.

    • Proposed as answer by Terry Xu - MSFT Monday, June 4, 2018 1:31 AM
    • Marked as answer by maggie0099 Tuesday, June 5, 2018 2:22 PM
    Friday, June 1, 2018 3:02 AM
  • Hello maggie0099,

    What's the state of the thread? Is your original issue resolved? Please feel free to let us know if your issue has any update.

    Best Regards,

    Terry


    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.

    Monday, June 4, 2018 1:37 AM
  • Thanks Terry.

    I did not use the way you mentioned since I need to do more dynamic way to extract certain data from xlsx file. What I did is to go through each sheet and extract column using Oledb API.

    Thanks at lot!


    Maggie

    Monday, June 4, 2018 2:04 PM
  • Hello maggie0099,

    OLEDB is a set of COM-based interfaces that expose data from a variety of sources. I don't think Oledb provide a function to convert xlsx to txt and this forum(Excel for Developers) is for development issue related to Excel Object Model. If you do need use Oledb to convert a xlsx to txt, you may need look for a more related forum.

    Thanks for understanding,

    Best Regard,

    Terry


    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, June 5, 2018 3:27 AM
  • Hi Terry....

    One question: when using the Microsoft Office Interop DLL for Excel, do you really need to have the actual Excel application installed in order to use this DLL?

    Thanks...


    Chuy Varela

    Wednesday, October 31, 2018 9:29 PM