HELP with data delimiters

%3CLINGO-SUB%20id%3D%22lingo-sub-1428884%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20with%20data%20dilimiter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428884%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684338%22%20target%3D%22_blank%22%3E%40super3origin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20if%20it%20helps%20in%20you%20case.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fprofessor-excel.com%2Fmerge-excel-files-combine-workbooks-one-file%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fprofessor-excel.com%2Fmerge-excel-files-combine-workbooks-one-file%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428955%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20with%20data%20dilimiter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428955%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402789%22%20target%3D%22_blank%22%3E%40AshaKantaSharma%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20responding!%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20not%20trying%20to%20merge%20the%20files.%20Rather%2C%20I%20want%20each%20text%20file%20to%20be%20converted%20to%20an%20excel%20file%20with%20text%20separated%20in%20different%20columns%20based%20on%20their%20delimiters%20(i.e%20semicolon%2C%20space%2C%20...)%3CBR%20%2F%3E%3CBR%20%2F%3Efor%20example%2C%3CBR%20%2F%3E%3CBR%20%2F%3EName%2C%20orange%3B%20time%20day%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.dat%3CBR%20%2F%3E%3CBR%20%2F%3Eto%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%7C%20Name%20%7C%26nbsp%3Borange%20%7C%20time%20%7C%20day%20%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.xlsx%3CBR%20%2F%3E%3CBR%20%2F%3EI%20can%20do%20that%20for%20one%20file%20a%20time%20using%20the%20text%20wizard%2C%20I'm%20wondering%20if%20there%20is%20a%20way%20to%20do%20that%20for%20all%20of%20them%20at%20once%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428723%22%20slang%3D%22en-US%22%3EHELP%20with%20data%20delimiters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428723%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20long%20list%20of%20files%20in%20.dat%20format%20that%20I%20want%20to%20convert%20them%20into%20excel%20format.%20The%20files%20have%20bunch%20of%20data%20separated%20by%20delimiters%20and%20I%20need%20them%20to%20be%20separated%26nbsp%3B%20by%20columns%20in%20excel.%20I%20know%20the%20excel%20trick%20where%20I%20can%20import%20the%20data%20in%20excel%2For%20open%20with%20excel%20and%20use%20the%20excel%20text%20wizard.%20My%20problem%20is%20that%20I%20have%20over%202000%20files%20that%20I%20need%20to%20convert%20and%20split%20the%20texts%20in%20columns.%20Opening%20and%20delimiting%20each%20one%20of%20them%20will%20take%20forever.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20I%20can%20split%20all%20text%20files%20at%20once%3F%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20convert%20all%20of%20them%20to%20xls%2C%20but%20how%20I%20can%20split%20the%20text%20for%20all%20of%20them%20at%20once%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20any%20advice%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1428723%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1429110%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20with%20data%20delimiters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684338%22%20target%3D%22_blank%22%3E%40super3origin%3C%2FA%3E%26nbsp%3BI%20think%20the%20best%20solution%20for%20your%20request%20is%20to%20save%20all%20your%20files%20in%20a%20specific%20folder%2C%26nbsp%3B%20then%20write%20a%20macro%20to%20loop%20through%20all%20files%20of%20that%20same%20format%20in%20that%20folder%2C%20import%20each%20to%20Excel%20and%20split%20Text%20to%20column%20with%20delimiter%2C%20save%20imported%20data%20in%20a%20workbook%2C%20name%20it%20accordingly%20then%20close%20and%20move%20to%20the%20next%20file.%3C%2FP%3E%3CP%3EYou%20will%20need%20to%20google%20vba%20codes%20for%202%20things%3A%3C%2FP%3E%3CP%3E-%20Loop%20through%20a%20directory%20to%20open%20each%20file.%20e.g.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.thespreadsheetguru.com%2Fthe-code-vault%2F2014%2F4%2F23%2Floop-through-all-excel-files-in-a-given-folder%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.thespreadsheetguru.com%2Fthe-code-vault%2F2014%2F4%2F23%2Floop-through-all-excel-files-in-a-given-folder%3C%2FA%3E%3C%2FP%3E%3CP%3E-%20Import%20file%20to%20Excel%2C%20e.g.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsitestory.dk%2Fexcel_vba%2Fautomatic-import-textfile.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsitestory.dk%2Fexcel_vba%2Fautomatic-import-textfile.htm%3C%2FA%3E%3C%2FP%3E%3CP%3EIf%20the%20number%20of%20columns%20in%20each%20file%20is%20not%20consistent%2C%20you%20may%20need%20to%20imitate%20the%20approach%20of%20this%20code%20to%20automate%20the%20import%20regardless%20of%20%23%20columns%20%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fboard%2Fthreads%2Fimport-tab-delimited-text-file-into-excel-via-directory.671778%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mrexcel.com%2Fboard%2Fthreads%2Fimport-tab-delimited-text-file-into-excel-via-directory.671778%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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 

3 Replies
Highlighted
Highlighted
@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?
Highlighted

@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-...

- 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...