Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

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.


Post your answer or comment

comments powered by Disqus
hi,

anyone can help me to create pipe delimited text file from excel via macro?

attached is the excel source and output text file format.

the test file will have the following format:

|date|ID|WorkingDays|DaysPresent|AbsentHours|LateHours|PresentHours|

|yyyy-mm-dd|ID|13|12|0.00|0.00|96.00|

thanks,

I was sent an excel file with the following macro (see end of message) which is supposed to export data from the spreadsheet into a pipes delimited text file, for use by another application. however, I cannot get the macro to work. The only immediate issue is that the macro was scripted in another environment using Excel 2000 and we are using Excel 2002. I did not think that would matter much, but here I am. I am not very good with Excel macros yet and I frankly do not see what is wrong with this macro.

All assistance greatly appreciated.

Sub ExportToOpenC()

Dim strFilename As String
Dim strPipe As String
Dim objRange As Excel.Range 'Range of selected fields.
Dim dblFile As Double 'Output file handle.
Dim lngRow As Double 'Loop counter for loop through rows.
Dim lngColumn As Double 'Loop counter for loop through columns.
Dim strBuffer As String 'Buffer containing output line when being compiled.

'Handle ANY error by terminating procedure.
On Error GoTo Err_Export_Failed

'Get user options.
strFilename = InputBox("Enter full path & filename to export to.", "Export Util", "C:Ouput.txt")
strPipe = InputBox("Enter pipe deliminator character.", "Export Util", "|")

'Get Currently selected range of cells.
Set objRange = Application.Selection

'Open output file.
dblFile = FreeFile 'Grab free file handle.
Open strFilename For Output As dblFile

'Row Loop
For lngRow = LBound(objRange.Value, 1) To UBound(objRange.Value, 1)
strBuffer = ""

'Column Loop
For lngColumn = LBound(objRange.Value, 2) To UBound(objRange.Value, 2)
'Add Pipe delimantor if not first cell.
If (lngColumn > LBound(objRange.Value, 2)) Then
strBuffer = strBuffer & strPipe
End If
'Append next cell to output buffer.
strBuffer = strBuffer & Trim(objRange.Values(lngRow, lngColumn))
Next lngColumn

Print #dblFile, strBuffer
Next lngRow

'Close Ouput
Close #dblFile

MsgBox "Selected fields exported to " & strFilename, vbInformation, "Export Util"

Exit Sub

Err_Export_Failed:
MsgBox "Failed to export to " & strFilename, vbInformation, "Export Util"

End SubText

I have a csv file which I have opened in Excel 2008 on my mac.

How do I export it as a pipe delimited text file.

I can only see an option to save it as a tab delimited text file.

If I cant save as a pipe delimited text file,
Is there a way to convert from a tab delimited text file to a pipe delimited text file?

Hi, Can anyone tell me how to save a excel spreadsheet into a pipe delimited text file? I know how to do the reverse, already. Thank you!

Having trouble finding a good example of how to import pipe delimited files for Excel VBA 2003. Basically I need a routine that prompts the user to select the Excel file, than imports the file to the active spreadsheet. In this case the file would be imported to cell "A1".... I know this import routine is just the heart of the module, I do need error handling routines as well but this procedure will suffice for now. Thanks

Hi,

my boss send me a mail with 5 csv files and wants me to load them as a
pipe delimited text file to our IT department. Since the whole IT
department is already at home and I have to do it this evening, I hope
to find some help here.
Thank you very much in advance!!

Kind regards

Tim

--
germantim
------------------------------------------------------------------------
germantim's Profile: http://www.excelforum.com/member.php...o&userid=37559
View this thread: http://www.excelforum.com/showthread...hreadid=571895

When I convert a .txt document to excel, my columns are all formatted to
general, and hwne I change them to numbers, it adds a zero at the end of the
numbers in one of my columns. The number is a 13-16 digit long number and as
an example it show in the cell as: 512345E +15. How do I avoid the extra zero
at the end of my number?

Hi,

my boss send me a mail with 5 csv files and wants me to load them as a pipe delimited text file to our IT department. Since the whole IT department is already at home and I have to do it this evening, I hope to find some help here.
Thank you very much in advance!!

Kind regards

Tim

Hi all,

I have a powerpoint presentation in which I'd like to link text to Excel. I have a slide that has 5 bullet points (these were created in PPT). Each bullet has a company name, current stock price, and some other text. I would like to link the current price to an Excel worksheet that I maintain.

I have succeeded in getting the PPT to link to Excel by using Copy/Paste Special/Past Link. However, my problem is that the formatting of the PPT slide is different that the Excel file (font size, color), and I cannot figure out how to make the pasted link take on the characteristics of the PPT bullet (other than to change the Excel file, which isn't an option).

Any ideas? Any help would be much appreciated.

Thanks,
Bill

I've also posted this query here: http://www.theofficeexperts.com/foru...9292#post29292

I have a problem when importing text to Excel (2003).

I have a Word-document with 1 000 paragraphs. Some of the paragraphs
only have one short line of text but most of them consist of several
lines of text separated by a manual line break. It is crucial to keep
the line breaks.

When I paste the data into Excel the text is placed in cells on a
"line-by-line" basis and not paragraph by paragraph. Each line of text
in the word document is placed in its own cell. I want each paragraph
to go in one cell and on the same time keep the manual line breaks
inside the cell. I guess part of the problem is that in Excel a hard
line break is Alt+Enter and in Word Shift+Enter.

Anyone got a workaround for this?

--
Fredrik E. Nilsen

I need to convert numbers as text to values that can be added for a long
column of entries. HELP

I have a problem when importing text to Excel (2003).

I have a Word-document with 1 000 paragraphs. Some of the paragraphs
only have one short line of text but most of them consist of several
lines of text separated by a manual line break. It is crucial to keep
the line breaks.

When I paste the data into Excel the text is placed in cells on a
"line-by-line" basis and not paragraph by paragraph. Each line of text
in the word document is placed in its own cell. I want each paragraph
to go in one cell and on the same time keep the manual line breaks
inside the cell. I guess part of the problem is that in Excel a hard
line break is Alt+Enter and in Word Shift+Enter.

Anyone got a workaround for this?

--
Fredrik E. Nilsen

I currently have some .txt files which are tab delimited, and I need to
convert them so that they are pipe(|) delimited. Any ideas how to do this?
Excel seems to support comma, tab and space delimiting, not sure how to use
pipe or tilda.

Hi Friends,
I have an excel sheet which I need to store as a pipe delimited text file.
This should be done using macro.
For example:
input:

45 56 78 abcd

output:

45|56|78|abcd|

I cannot create a tab delimited or csv file and then replace manually. It has to be done automatically.

Please Help!!

Hello -
I hope I am posting this to the correct discussion group.

When converting a .tsv file to Excel using the Text Import Wizard > Column
Data Format > Step 3, which is "Do not import column (Skip)," is there any
way I can globally exclude columns? I have 50 columns to exclude, and
clicking each column individually and excluding each column one-by-one takes
a lot of time. In other words, is there a way I can exclude columns in
groups vs. one at a time?

My .tsv files are too large to convert to Excel and delete the columns there.

Thanks a lot in advance,

Hi there,

I need help in opening text delimited file to excel without excel
changing data such as dates or number without heading zeros to its
version.

Right now, I am thinking to write VB macro behind command button, to
set a new worksheet in to text format and get the text delimited file
into that worksheet. Could you suggest how I can do this ? sample codes
will be helpfull too.

Thank you

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

How to I change the default setting when importing a .txt file?

I spend a lot of time converting Excel (Office 2002) files into HTML.
The quickest way to clear off all the Microsoft formatting crud seems
to be to save as a tab delimited .TXT file.

This works well but it is extremely irritating that having saved as
"tab delimited text" from Excel, that the same piece of software cant
recognise its own file when you reopen it! It seems to default to
Delimited and file origin
"932: Japanese (Shift-JIS)" whereas what I want is file origin of:
"1252 : Western European (Window)". However this requires clicking on
the drop-down list (which always takes a while to fill up for some
reason) and the scrolling to the bottom of the list...!

How can I get Excel to recognise the file-type of what it has just
saved?!

Ship

I have a text file de-limited with tab. I need to convert the same file to
excel and use chart options to draw a graph. Though i am able to import the
text file in to excel file i am unable to draw graph with that data. Please
help me do it.

Hi there - I have copied about 35,000 records from our website and pasted them into MS Excel 2010. They are formatted as text in the following way: 08/28/2010 17:52:49

How do I convert them from text to date, and also convert them from US to UK format so that I can analyse and order them?

I have tried the "text to columns" function using the DMY format and I have also tried to convert them manually by formatting the cells as date 04/12/10 etc using the format cells function.

Any help would be most gratefully appreciated!

It appears that when performing a cell full of HTML text into multiple columns using Delimited Text to Columns with a delimiter that multiple lines are disregarded, you need everything to be in one long line without line breaks.

Please correct me if I'm wrong here, but I suppose all that I need is to remove all the line breaks and format this HTML so that it's excel friendly and still works on the web.

Does anyone have an excel HTML clean up macro?

THANKS!

Hi there,

First post.....

I downloaded a file from my database (not access) which outputs the
file to .txt

Using excel I can set each colum for the text and it puts each piece of
data in to a column etc. Very nice.

One of the pieces of data I output from the system is a persons date of
birth, however, it is shown as (an example) 19340101. I can convert this
to something that looks nicer by using
=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) but that doesn't convert
the number in to a date format.

I need to convert 19340101 to 01/01/1934 in a date format that excel
can understand.

Any ideas.

Thanks in advance.

Stewart

--
samhain
------------------------------------------------------------------------
samhain's Profile: http://www.hightechtalks.com/m92
View this thread: http://www.hightechtalks.com/t2262615

I want the individual tab delimited fields to be represented in Excel as rows
of their own. Right now the entire tab delimited file is in the first column.

Thanks

I am using delimited text-to-columns in Excel 2003. I would like to
break up a single text column into two additional text columns based
on the 1st two occurances of the space delimiter (" ") within each
cell. All other spaces should be ignored. Finally, for what remains
fo the original column, I'd like to break it into an additional column
based on the last occurance of the colon delimiter (":"), with any
other colons ignored. Is there a way to do this?

Before I imported the text into Excel, I could have made the job
easier by replacing the 1st 2 spaces, and the last colon, in the text
into a special delimiting character which occurs nowhere else, such as
"@". It would be a big step backward to go back to the external text
file because I've already done a lot of grouping of rows into an
outline structure (it's a somewhat hefty file with many levels of
groupings throughout).

Perhaps I can preserve the outline structure by re-importing the text
into adjacent columns (after injecting the unique delimiting
character)....Hmmmm.....

Hi,

I have come across a problem to import data from a text file into excel. I have previously used software called Monarch Pro from Datawatch which was magical and converting data from text to excel was a breeze but unfortunately I do not have access to that software anymore. I shall try to explain what I want to do. I have hope that one of the brilliant brains in this community will be able to come up with a solution.

I have a text file with some headers (in rows) and then data in columns. The text file has page breaks. So if some data specific to a particular header is large, it will appear in multiple pages. Each page will repeat the headers. In case of less data, the next set of data appears in the next page. Please refer to the attached files.

What I am trying to achieve is that when I import that data into excel, the currency and account number (Headers) should become part of the data in columns.

The currency and account number will change as the headers change in text file.

Please note that the data is very huge. So it is almost impossible to import the data through “Import External Data” option and then manually insert columns and add the headers into columns. I’ll be grateful if this can be achieved through a macro.

PS: The calculation in the enclosed text file is not correct because I just copied and pasted the data without calculating the correct balance in the last column.


No luck finding an answer? You could always try Google.