Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
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" wrote: 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
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" wrote: 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" wrote: 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
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" wrote: 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" wrote: 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" wrote: 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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
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. "Sadie" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
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 "Joel" wrote: 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. "Sadie" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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, |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
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,81 5.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" wrote: 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. "Sadie" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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, |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
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" wrote: 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,81 5.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" wrote: 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. "Sadie" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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, |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Txt file into Excel
This Macro works!
Thank you so much Joel for all your help! "Joel" wrote: 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" wrote: 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,81 5.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" wrote: 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. "Sadie" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing a txt file into Excel - | Excel Discussion (Misc queries) | |||
Importing VBA file into Excel | Excel Programming | |||
importing a csv file into Excel | Excel Programming | |||
Importing .pdf file into Excel | Excel Discussion (Misc queries) | |||
Importing CSV file into Excel | Excel Programming |