Forum Discussion

super3origin's avatar
super3origin
Occasional Reader
May 30, 2020

HELP with data delimiters

Hi,

 

I have long list of files in .dat format that I want to convert them into excel format. The files have bunch of data separated by delimiters and I need them to be separated  by columns in excel. I know the excel trick where I can import the data in excel/or open with excel and use the excel text wizard. My problem is that I have over 2000 files that I need to convert and split the texts in columns. Opening and delimiting each one of them will take forever. 

Is there any way I can split all text files at once? 

I can convert all of them to xls, but how I can split the text for all of them at once?  

I appreciate any advice 

excel
Excel on Mac
office 365

3 Replies

  • hynguyen's avatar
    hynguyen
    Iron Contributor
    May 31, 2020

    super3origin I think the best solution for your request is to save all your files in a specific folder,  then write a macro to loop through all files of that same format in that folder, import each to Excel and split Text to column with delimiter, save imported data in a workbook, name it accordingly then close and move to the next file.

    You will need to google vba codes for 2 things:

    - Loop through a directory to open each file. e.g. https://www.thespreadsheetguru.com/the-code-vault/2014/4/23/loop-through-all-excel-files-in-a-given-folder

    - Import file to Excel, e.g. https://sitestory.dk/excel_vba/automatic-import-textfile.htm

    If the number of columns in each file is not consistent, you may need to imitate the approach of this code to automate the import regardless of # columns : https://www.mrexcel.com/board/threads/import-tab-delimited-text-file-into-excel-via-directory.671778/

    • super3origin's avatar
      super3origin
      Occasional Reader
      May 30, 2020
      AshaKantaSharma 

      Thank you for responding!

      I'm not trying to merge the files. Rather, I want each text file to be converted to an excel file with text separated in different columns based on their delimiters (i.e semicolon, space, ...)

      for example,

      Name, orange; time day              .dat

      to 

      | Name | orange | time | day |      .xlsx

      I can do that for one file a time using the text wizard, I'm wondering if there is a way to do that for all of them at once?

Resources