converting pipe delimited text to excel

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.

Originally Posted by tlafferty

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

Originally Posted by jaslake

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.

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.

Originally Posted by 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.

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.

Originally Posted by 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.
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.

Originally Posted by 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.

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

Originally Posted by jaslake

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

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

Thank you.

Nicholas

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?