Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing and importing a text file with records through a VBA macro
Hi all,
I'm really stuck in a problem in parsing and importing a text file into an Excel sheet with a VBA macro. I strongly need to do it by a VBA macro in order to postprocess the resulting data through Excel. I searched a lot into the network and I found something useful but I can't achieve the final solution actually. Can you help me please? Your help is really appreciated. My text file is containing several records formatted like this: REC1_FIELD1 REC1_FIELD2 REC1_FIELD3 REC1_FIELD4 REC1_FIELD5 REC1_FIELD6 REC2_FIELD1 REC2_FIELD2 REC2_FIELD3 REC2_FIELD4 REC2_FIELD5 REC2_FIELD6 ... Keywords: 1) FIELDS are separated by space but they are also on different rows (you can considered they are separated also by a "single" linefeed); example using C language syntax: i.e. ...REC1_FIELD2" "REC1_FIELD3"\n"REC1_FIELD4" "REC... 2) RECORDS are separated by an empty row (you can consider they are separated by a "double" linefeed). i.e. ...REC1_FIELD6"\n\n"REC2_FIELD1... I need to import each RECs on single row, separating fields in cells, having an Excel table like this: +-----+----------------------+---------------------- +----------------------+----------------------+---------------------- +------------------------ | |A | B |C |D |E |F | 1 |REC1_FIELD1 |REC1_FIELD2 |REC1_FIELD3 |REC1_FIELD4 |REC1_FIELD5 | REC1_FIELD6 | 2 |REC2_FIELD1 |REC2_FIELD2 |REC2_FIELD3 |REC2_FIELD4 |REC2_FIELD5 | REC2_FIELD6 | 3 | | | | | | FIRST STRATEGY: The parsing strategy I was thinking is a) replace the double linefeed ("\n\n") with an temporary char (i.e. "@") b) replace the single linefeed "\n" with tab char ("\t") c) replace the "@" with the single linefeed "\n" NOW THE FILE SHOULD BE IN THIS FORMAT: REC1_FIELD1"\t"REC1_FIELD2"\t"REC1_FIELD3"\t"REC1_ FIELD4"\t"REC1_FIELD5"\t"REC1_FIELD6"\n" REC2_FIELD1"\t"REC2_FIELD2"\t"REC2_FIELD3"\t"REC2_ FIELD4"\t"REC2_FIELD5"\t"REC2_FIELD6"\n" d) import the text file reading line by line and filling by cells. Is there a way to do all these replacement through a VBA macro? SECOND STRATEGY: a) begin to read text file into a string choosing the linefeed "\n" as delimiter b) Write the string into a cell and move on the cell on the right c) until a second linefeed "\n" is not found: do point (a) and (b) else 'a second linefeed is found move on the next row do point (a) and (b) Is there a way to do it through a VBA macro? Any suggestions, examples, whatever is appreciated. Thanks so much for your help and your time. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing and importing a text file with records through a VBA macro
This seemed to work for me:
Option Explicit Sub testme() Dim FSO As Object Dim RegEx As Object Dim myFile As Object Dim myContents As String Dim myInFileName As String Dim myOutFileName As String myInFileName = "C:\test.txt" myOutFileName = "C:\testout.txt" Set FSO = CreateObject("Scripting.FileSystemObject") Set myFile = FSO.OpenTextFile(myInFileName, 1, False) myContents = myFile.ReadAll myFile.Close Set RegEx = CreateObject("VBScript.RegExp") With RegEx .Global = True .IgnoreCase = False .Pattern = vbCrLf myContents = .Replace(myContents, "@") .Pattern = "@@" myContents = .Replace(myContents, vbCrLf) .Pattern = "@" myContents = .Replace(myContents, vbTab) End With Set myFile = FSO.CreateTextFile(myOutFileName) myFile.Write myContents myFile.Close End Sub Make sure that there are no @'s in your file... dgr ion wrote: Hi all, I'm really stuck in a problem in parsing and importing a text file into an Excel sheet with a VBA macro. I strongly need to do it by a VBA macro in order to postprocess the resulting data through Excel. I searched a lot into the network and I found something useful but I can't achieve the final solution actually. Can you help me please? Your help is really appreciated. My text file is containing several records formatted like this: REC1_FIELD1 REC1_FIELD2 REC1_FIELD3 REC1_FIELD4 REC1_FIELD5 REC1_FIELD6 REC2_FIELD1 REC2_FIELD2 REC2_FIELD3 REC2_FIELD4 REC2_FIELD5 REC2_FIELD6 ... Keywords: 1) FIELDS are separated by space but they are also on different rows (you can considered they are separated also by a "single" linefeed); example using C language syntax: i.e. ...REC1_FIELD2" "REC1_FIELD3"\n"REC1_FIELD4" "REC... 2) RECORDS are separated by an empty row (you can consider they are separated by a "double" linefeed). i.e. ...REC1_FIELD6"\n\n"REC2_FIELD1... I need to import each RECs on single row, separating fields in cells, having an Excel table like this: +-----+----------------------+---------------------- +----------------------+----------------------+---------------------- +------------------------ | |A | B |C |D |E |F | 1 |REC1_FIELD1 |REC1_FIELD2 |REC1_FIELD3 |REC1_FIELD4 |REC1_FIELD5 | REC1_FIELD6 | 2 |REC2_FIELD1 |REC2_FIELD2 |REC2_FIELD3 |REC2_FIELD4 |REC2_FIELD5 | REC2_FIELD6 | 3 | | | | | | FIRST STRATEGY: The parsing strategy I was thinking is a) replace the double linefeed ("\n\n") with an temporary char (i.e. "@") b) replace the single linefeed "\n" with tab char ("\t") c) replace the "@" with the single linefeed "\n" NOW THE FILE SHOULD BE IN THIS FORMAT: REC1_FIELD1"\t"REC1_FIELD2"\t"REC1_FIELD3"\t"REC1_ FIELD4"\t"REC1_FIELD5"\t"REC1_FIELD6"\n" REC2_FIELD1"\t"REC2_FIELD2"\t"REC2_FIELD3"\t"REC2_ FIELD4"\t"REC2_FIELD5"\t"REC2_FIELD6"\n" d) import the text file reading line by line and filling by cells. Is there a way to do all these replacement through a VBA macro? SECOND STRATEGY: a) begin to read text file into a string choosing the linefeed "\n" as delimiter b) Write the string into a cell and move on the cell on the right c) until a second linefeed "\n" is not found: do point (a) and (b) else 'a second linefeed is found move on the next row do point (a) and (b) Is there a way to do it through a VBA macro? Any suggestions, examples, whatever is appreciated. Thanks so much for your help and your time. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing a text file of individual records | Excel Discussion (Misc queries) | |||
a Macro for importing & parsing text? | Excel Programming | |||
Importing Text File Data into Excel where records span several row | Excel Discussion (Misc queries) | |||
Analyzing Text file records in VBA without importing into Excel spreadsheet | Excel Programming | |||
Parsing imported text file with macro... help! | Excel Programming |