Importing Txt file into Excel

[Sadie]

When I import a .txt file into excel all of the data is displayed in one row.

How do I get excel to put the data into the proper rows?

I just open the .txt file and the import wizard opens. The data is delimited
by commas. Sorting the data into columns seems to work fine. Unfortunately,
the data just displays in the first row in different columns. It does not
seem to recognize when the new row should start.

The column that contains the data where the new row should start has the
data from the last column of the previous row in it as well, example:
$1,337.52"01-Jan-2008
The $1,337.52 should be the last cell in the row and the 01-Jan-2008 should
be the first cell of the next row. Instead they are in the same cell.
Therefore, the entire set of data is all in one row!

I am not very experienced and would appreciate any help.

Thanks,

 

[Joel]

I think the problemn is with the end-of-line character(s). A PC requires a
Carriage Return (Ascii 13) followed by a Line Feed (Ascii 10). Your file was
create on another system that didn't produce both the CR and LF. The code
below will fix any problem with the file. The code will read your file and
create a 2nd file with the corrected end of line (EOL) characters. After
running this code read the new file into excel and see if it solves your
problem.


Sub FixEOL()

Const ForReading = 1, ForWriting = -2, _
ForAppending = 3


CR = Chr(13)
LF = Chr(10)

ReadFile = Application _
.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Read File")
If ReadFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

WriteFile = Application _
.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Write File")
If WriteFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, _
ForReading, TristateFalse)
Set fout = fs.CreateTextFile _
(Filename:=WriteFile, overwrite:=True)

FoundCR = False
Do While fin.AtEndOfStream <> True
ReadData = fin.read(1)
Select Case ReadData

Case CR:
If FoundCR = True Then
'two CR in a row write LF inbeteen the two CR
fout.write LF
fout.write CR
Else
FoundCR = True
fout.write CR
End If
Case LF:
If FoundCR = True Then
''Normal sequence CR foloowed by LF
fout.write LF
FoundCR = False
Else
'Bad Sequence LF without CR, Write CR
fout.write CR
fout.write LF
End If
Case Else
If FoundCR = True Then
'Bad Sequence CR without LF, wite LF
fout.write LF
fout.write ReadData
FoundCR = False
Else
'Normal dequence of two character in middle of line
fout.write ReadData
End If

End Select
Loop
fin.Close
fout.Close
End Sub

 

[Sadie]

Thank you Joel for answering so quickly!

I am very new to using excel. Where do I enter the CODE information you
provided? How do I run the CODE?

Thank you so much for your help

 

[Joel]

The worksheet type ALT-F11 get you to the VBA Window. Then go to menu Insert
- Module. Past code from SUB ... to End Sub from the web posting to the VBA
module window.

Run the code from VBA by typing F5.


To execute VBA you have ot have you security level set to Medium and when
you open the workbook you must answer yes to the Window Do you want to
execute macros.


Set your security level from worksheet menu

Tools - Macro Security - Medium.

 

[Sadie]

I changed my security settings, I opened the VBA window, I copied the CODE
from the web post into the VBA window, I ran the Code by pressing F5 from the
VBA window.

An Open File window was displayed and I opened the problem TXT file. A Save
File window opened and I saved the fixed TXT file. Then I opened the newly
fixed TXT file in excel and I got the same results - all of my data was in
one row. I do not know what I am doing wrong.

However, I did figure out that if I open the problem TXT file in WORD and
use FIND &REPLACE to change all the "" with a special character (Manual Line
Break), resave the TXT file, and then import the new file into EXCEL, most of
the data is displayed in the proper rows. The first and last rows still need
to be manually altered (NOT A FUN TASK!). This solution is still problematic
but it puts about 95% of the data in the proper spot.

Thanks Joel for your help!

 

[Joel]

If you post some of your irigina;l text file I can mdoify my code to do the
same thing you are doing in word. All you will have to do is run my new
macro and the code will be fixed. I need to see a sample of the text file to
get it right. Actuall just the end of the line where you are putting the
line break. ther must be some other character that is missing.

It is better to make your manual changes using NotePad rather than word.
word can cause some additional problems. Also take a look at my converted
code in Notepad to make sure that my code actually worked.

 

[Joel]

To find the character you are replacing copy the end of line character
inbetween the double quotes in the code below. I have blnk in the double
quotes so you can see how the code works.

Sub test()

MyStr = " "
MyLen = Len(MyStr)
MsgBox ("the character is chr: " & Asc(MyStr))

End Sub

 

[Sadie]

I looked at the Fixed TXT file (created after running your CODE) in Notepad
and it looks the same as the original Problem TXT file. I can not see any
changes.

Here is some of the data from the problem TXT file:

"01-Mar-2008","Charges","$0.00","$1,317.60","$0.00","$0.00","$0.00","$1,317.60","$0.00","$0.00","$0.00","$0.00","$1,317.60","$3,557.93-""01-Mar-2008","Charges","$0.00","$47.14","$0.00","$0.00","$0.00","$47.14","$0.00","$0.00","$0.00","$0.00","$47.14","$3,510.79-""01-Mar-2008","Payment
Made","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$3,815.59","$0.00","$0.00","$3,815.59","$3,815.59-","$7,326.38-"

Ideally, this data should be displayed as three rows of data in Excel. Each
new row starts with the date.

When I open the problem TXT file in Notepad it displays as solid lines of
data going from one side of the page to the other side of the page, there are
no breaks.

When I REPLACE the "" with Manual Line Breaks in a word document the data
changes and is displayed in groups, each group representing the data for a
new row.

The data I pasted into this post looks different than either situation, so I
hope it is what you need.

Thanks for the tip about editing the TXT file in notepad instead of word,
hopefully your new macro will work.

Thanks again for your help!

 

[Joel]

This was a little tricky because all the field had double quotes around them.
I wrote the code so it should work under any condition. You have 14 fields
per line. So I counted the number of field and after the 14th field I added
a carraige return. I count the number of double quotes pairs to get the 14th
field. Then before I get the next double quote I add a carriage return.


Sub FixEOL()

Const ForReading = 1, ForWriting = -2, _
ForAppending = 3


CR = Chr(13)
LF = Chr(10)

ReadFile = Application _
.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Read File")
If ReadFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

WriteFile = Application _
.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Write File")
If WriteFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, _
ForReading, TristateFalse)
Set fout = fs.CreateTextFile _
(Filename:=WriteFile, overwrite:=True)

EOL = False
StartString = False
CountFields = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.read(1)
Select Case ReadData
'test for double quote
Case Chr(34)
If StartString = False Then
If EOL = True Then
fout.writeline ""
EOL = False
CountFields = 0
End If
StartString = True
Else
StartString = False
CountFields = CountFields + 1
If CountFields = 14 Then
EOL = True
End If
End If
fout.write Chr(34)
Case Else
fout.write ReadData
End Select
Loop
fin.Close
fout.Close
End Sub

 

[Sadie]

This Macro works!

Thank you so much Joel for all your help!