# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  converting pipe delimited text to excel

## dealofsd

Hi,
I am little new to macros, i am trying to convert around 500 pipe delimited text file into excel so that in excel they appear in different columns.
example; text file 1|2|3
when converted , excel file should have 1,2,3 in column a,b,c.

I know its doable with little programming, but unable to do it as i am new comer.
Can any one please help with this.

----------


## tlafferty

Just open the text file from within Excel. Select the delimited option, click Next, Other and specify a | then click finish.

----------


## jaslake

Hi dealofsd
Assuming your data is in Column A and starts on line 3 


```
Please Login or Register  to view this content.
```

----------


## dealofsd

> Just open the text file from within Excel. Select the delimited option, click Next, Other and specify a | then click finish.



Hi tlafferty, thats right, but i have over 5000 files which i need to convert into excel, and i might need to do it again and again every couple of weeks. So trying to find other options  :Frown:

----------


## dealofsd

> Hi dealofsd
> Assuming your data is in Column A and starts on line 3 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```



Hi jaslake,
My data is in .txt file with pipe as delimiter and i need to convert them to excel file files to respective columns in excel. 
Is above code a macro? i tried creating macro but when i run, it is complaining that there is data to parse.

----------


## tlafferty

Hi dealofsd -
did you see my post? Excel has a built-in capabity of doing this without you writing a single line of code. Just open the text file in Excel and step through the wizard - your data will be parsed for you with no hassles.

----------


## jaslake

Hi dealofsd
You asked for a Macro solution.  Please post a sample of the text file that represents your actual data; I'll present you with a Macro solution.  Of course tlafferty is correct, Excel has native functions that do the same.

----------


## dealofsd

Hi John,
I understand that from excel i can bring data from test and choose delimiter as pipe, data can be correctly bought into the excel but i have around 5000 such files. So i am looking for better solution. I am not sure if excel can process all 5000 files one time and convert all text file to excel file, if it is then that would be great.
I am attached my sample file with 7 columns with 1 header and 1 record , when they converted into excel, 1row will have header in first 7 columns and data in their corresponding excel. I am attaching excel file.

----------


## dealofsd

> Hi dealofsd -
> did you see my post? Excel has a built-in capabity of doing this without you writing a single line of code. Just open the text file in Excel and step through the wizard - your data will be parsed for you with no hassles.



Hi tlafferty,
As you mentioned i was able to get data from .txt to .xls file. But i have 5000 such files, is there any option to convert all the text files in the folder to excel?, that would be wonderful if i can do it.

----------


## jaslake

Hi dealofsd
Yes, Excel can do this (I think...never tried it with that many files...not aware of any limitation) although it may be slow with 5000 files.



> I am not sure if excel can process all 5000 files one time



Do all your text files have 7 pipe delimited items? Are they all in Column A? Do they all start on Line 2? Are the Headers also pipe delimited as your sample file shows and in Row 1? Are the text files all in the same Folder?

----------


## dealofsd

> Hi dealofsd
> Yes, Excel can do this (I think...never tried it with that many files...not aware of any limitation) although it may be slow with 5000 files.
> Do all your text files have 7 pipe delimited items? Are they all in Column A? Do they all start on Line 2? Are the Headers also pipe delimited as your sample file shows and in Row 1? Are the text files all in the same Folder?




Hi John,
All my text files have pipe (2-25) delimited items,
 I did not get what you mean my Column A in text file??.
Yes headers are also pipe delimted and values from line 2 are corresponding values to headers. Yes all Text files are all in the same folder. 
All text files have 2to 25 items/fields delimited by pipe which will are actually column names. Table was converted to this file and i need to convert these files to tablular form to validate them.

----------


## jaslake

Hi dealofsd
Place the attached workbook in the same folder where your text files live. Click the button to run the procedure. Please use some sample data as the procedure will process all text files (5000 if 'ya got 'em). Let me know of issues.


```
Please Login or Register  to view this content.
```

----------


## dealofsd

> Hi dealofsd
> Place the attached workbook in the same folder where your text files live. Click the button to run the procedure. Please use some sample data as the procedure will process all text files (5000 if 'ya got 'em). Let me know of issues.
> 
> 
> ```
> Please Login or Register  to view this content.
> ```



Thanks For you reply, sorry i was out of town for a while. When i run your program, its converting all the files to excel but the problem is, its dumping all the data into column A in excel sheet instead going to different column whenever there is pipe.
Example: 1|2|3 is data in the text file
Your program is is putting 1|2|3 in column A of excel sheet instead of putting 1 in columnA and 2 in column B and 3 in columnC

----------


## jaslake

Hi dealofsd

Sorry 'bout that...I was able to duplicate this



> Your program is is putting 1|2|3 in column A of excel sheet instead of putting 1 in columnA and 2 in column B and 3 in columnC



In my original testing, such did not happen...in subsequent testing it did.

I've tested the code in the attached in Excel 2000 and Excel 2007...it appears to do as you require. Let me know of additional issues.

----------


## dealofsd

> Hi dealofsd
> 
> Sorry 'bout that...I was able to duplicate this
> 
> In my original testing, such did not happen...in subsequent testing it did.
> 
> I've tested the code in the attached in Excel 2000 and Excel 2007...it appears to do as you require. Let me know of additional issues.




Thank you so much, it is working perfectly good

----------


## jaslake

Glad it works for you.  If that satisfies your need, please mark your thread as "Solved".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save

----------


## nkormanik

Would you please update this macro for tab delimited text files?

Thank you.

Nicholas

----------


## jaslake

Hi Nicholas

Welcome to the Forum!

I'll be glad to look at this with you. Unfortunately our Forum Rule 2 discourages "hijacking" of posts. 

Forum Rule 2. *Don't post a question in the thread of another member -- start your own.* If you feel it's particularly relevant, provide a link to the other thread.

So, start your own Thread...PM me when you've done so with a link to the Thread...I'll look at it.

----------


## chayes

I am new to this site.  I have been abhle to convert XML file to Excel; but that only gives me drop down menus for each header.  If I run the original file in pipe delimited will that make each category in the previous drop down menus into its own column?

----------


## arlu1201

Chayes,

Welcome to the Forum, unfortunately:

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

----------

