convert excel to pipe delimited text file

It seems to be more actively updated and documented as well.Īlso, as noted by below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support ( Pivot table issue in ExcelLibrary) In all, EPPlus seems to be the best choice as time goes on. There are a few known bugs with each library as noted in the comments. You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.ĮxcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats. xlsx format yet, but they are working on adding that functionality in. This looks to be a port of the PHP ExcelWriter that you mentioned above. It's a free, open source library posted on Google Code: You can use a library called ExcelLibrary. PathExists = (GetAttr(pName) And vbDirectory) = vbDirectory MsgBox ('Number of Fragment Files processed: ' & book_counter)įunction verify_folder(path As String) As String

'Do stuff here (this just prints the file name) Workbooks.Open Filename:=folder_path & sWB_name Sub process_folder()ĭim pWB As Workbook, sWB As Workbook, sWB_name As String It then checks to make sure the path is valid and then loops through each file does something, closes the file and at the end it prints the number of files opened. The folder path is hard coded in the macro (this could be changed to bring up a folder dialogue. Note if will try and use all files (not just text ones) so I usually create a special folder just for importing. I've go a macro I often use for looping through a folder of files. Is there any way the file name can be taken based upon current sheet name during 'save as'? Is there any better and easier way of doing all this? May be like a batch mode etc. Hence every time I have to rename the saved file appropriately. However 'sample1' gets hard coded in macro.

Then Finish.Īfter recording macro, I am able to do step 2 to 4 with single key combination. Choose delimited file with pipe (|) delimiter and select text data type after selecting all column in next wizard tab. Select column A and use text to column wizard.ģ. Then it creates new workbook with single sheet with sheet name same as sample1 copying all lines in file under column A.Ģ. Just assume I have files as sample1.txt to … sampleN.txt.

Below is what I found best efficient manual way of doing it. Also all the fields should be treated as text while converting it.

Convert excel to pipe delimited text file