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.
0
dgr
2/23/2010 2:34:26 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
983 Views

Similar Articles

[PageSpeed] 6

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
0
Dave
2/23/2010 3:34:18 PM
Reply:

Similar Artilces:

Distribution Files for MFC in VS .net 2003
I have an app that was developed under Visual Studio VC++ 6.0 & MFC and deployed at our customer site. I have since installed Visual Studio .NET 2003 and rebuilt the application under the new development environment. I wanted to do a quick test of the executable and copied it up to a test machine and tried to run it. I got a series of "unable to find xxx.dll" errors. I realized that none of the new DLLs were installed on the test machine. Does anyone have a pointer to a good document on what distribution files are required? I know about the obvious ones like MFC71.DLL...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

A macro to increase line spacing
I'm trying to make a macro on my Word 2007 "Quick Access Toolbar" that will add 6 points after each line that I've selected. When I record a macro to do this, it adds the 6 points fine, but then sets the indent at "0", which was what the paragraph had when I recorded the macro. What I'd like it to do is ignore the indent of whatever paragraph I'm using the macro on, leave it as it is, and just add 6 points. Any suggestions how to do that? Here is how the Macro reads in VB--perhaps if I alter some of the parameters that would do it. Thanks for...

All macros failing part way through with different error messages
All the macros I have in various workbooks are failing - usually part-way through - with different error messages each time. When I check the debugger and try to edit the text, there seems no reason why there should be a problem. The macro has so far stopped on instructions to : - open a workbook - select a worksheet - paste a column in a different place Several times the macro has just stopped running without completing and without showing an error message. I cannot run a host of urgent daily reports without using the macros. Please help! NO way to help unless you tell us exactly...

file cloning
I was wondering. How come it is possible to clone a file (using right click copy/paste file), but not possible to do this for other documents (apps and clip-art etc)?. Could it be possible to have an add-in in excel to prevent people from copying documents on their desktop?? -- shnim1 You can copy files that way (rightclick|copy, rightclick|paste). But most windows applications are no longer just simple .exe files (like back in the old DOS days). They usually have tons of other stuff that gets installed with them--and that stuff gets scattered all over your harddrive (windows folder, wi...

parsing a date and time field #2
I am having trouble parsing the date and time in a field. I download data from a data base and the date and time come together in one field. I want to seperate the two. The date and time comes across as the following: "2/1/2009 14:37" in the cell. When I parse it, it seperates into three columns as follows: "2/1/2009", 2:37 AM", and "PM" I can see what is going on but I would like to get two columns with one as the date and the other as the correct time. are they any ideas on how to address this? Try using the TimeValue and DateValue functions. First format ...

Disappearing Combobox Text
Just opened up a number of existing databases and found the same problem in each. It is not possible to see the list of options in a ComboBox although the list is there. It is as if the fore colour is white and the back colour is white. If you select one of the options the text then appears in the control, black text on a white back ground. As a quick test i have created a new form and inserted a combobox, again the details of the options is not visible although the selected option text appears in the control after i make a random selection. I have checked all the visibility and colou...

VLookup in VBA giving error message
Hi, I'm having trouble making the vlookup function work in VBA. the values that are used are all dates, I don't know if that makes a difference. Worksheets("sheet1").range("a1").value = application.WorksheetFunction. _ Vlookup(Range("c1"), range("a:a"), 2) this gives me a run-time error '1004' Unable to get the vlookup property of the Worksheet function class so i tried this: Worksheets("sheet1").range("a1").value = application.Vlookup(Range("c1"), _ range("a:a"), 2) but this gives me a #REF ...

macro #2
I am pretty new to using (or attempting to use) a Macro in a workbook. I am having trouble following how and what to do to even start the process; ie If there needs to be a worksheet already in progress or can I start from a new sheet. THEN where to go from there. Can I have some help in this area? Can some one supply me with a sample MACRO? Any help would be greatly appreciated Dan The best way to get started with macros is to use the Macro Recorder. Go to the Tools menu, choose Macros, then Record New Macro. Then, carry out some simple tasks and examine the code that is produced. ...

how to build the netsample ipconfig to the exe file?
C:\WINCE500\public\common\oak\drivers\netsamp\ipconfig\ipconfig.cpp i want to make ipconfig.exe. and i could found the sample code. but it source code builded to the lib file. in ipconfig sources files, TARGETNAME=ipconfig TARGETTYPE=LIBRARY SOURCES= \ $(TARGETNAME).cpp the project is .lib file. but the source code has a _tmain() funciton in the source. it's looks possible to compile to the exe file. how can it compile to the exe file? You can add the SYSGEN_NETUTILS and you will have the ipconfig.exe integrated to your OS. Search ipconfig in the cat...

How do I export Lotus Approach files into an Excel spreadsheet?
I need to export data from Lotus Approach to Excel; please help. I am using an old version of Lotus SmartSuite 9.5 and I have Microsoft Office 2003 Basic. Well, I don't know Approach at all but is there a common file format that both use e.g. comma delimited. If so , save in that format from Approach and import into Excel. "LEWOLF" wrote: > I need to export data from Lotus Approach to Excel; please help. I am using > an old version of Lotus SmartSuite 9.5 and I have Microsoft Office 2003 Basic. ...

outlook can't receive exe files
A guy here at work can't get exe files through his outlook. Is there a check to uncheck somewhere to allow it to do this? He can receive normal attachments. ...

Importing spam list
Hi, I have a long list of spam email addresses that I want to import into outlook. How do I do this? Do they have to be separated by commas? Thaks, Paul Where is this spam list generated? What version of Outlook? Where do you propose to import them? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Paul Ellis asked: | Hi, | I have a long list of spam email addresses...

Macros #34
I am trying to edit and create some macros in an excel spreadsheet that was created by someone else. There are macros for this spreadsheet but for some reason when I go to Tools -> Macro -> Macros it doesn't show any in the list. When I go to Tools -> Macro -> Visual Basic Editor there is code for the macros. I have some buttons on the spreadsheet that I want to assoicate with the macros but the buttons almost seem like they are locked, when i write click them nothing happens but a shortcut menu should appear. Is there maybe some special security on there I don't...

retrieving folders.old file
probably been posted before, but need some help. i was getting the "MSIMN has caused an error in directdb.dll" i found the solution by renaming the folders.dbx file to folders.old. here's the problem, i opened express back up and my sent folder was empty. my question is, how or can i retrieve that old sent message list?? ...

Yet another duplicate record dilemma
I have a table with records where one field are duplicates. I'm able to query to find duplicates and delete them, however what I need to do is find the duplicates, produce a total from another field, delete the duplicates and update the record field with the new total. Use the Find duplicates wizard, the build an Update query and either add to the field: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = MyField + Query1.MyField or just update it: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = Query1.MyField Then delete the duplicate data. -- Ar...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

Add new record through Form view
I have a table which is linked to a form, i would like to have a command button which will add a new record to my table. On the click of command button it should view the last empty row of my table in a form. On Sat, 26 Dec 2009 11:11:01 -0800, Ranjith Kurian <RanjithKurian@discussions.microsoft.com> wrote: >I have a table which is linked to a form, i would like to have a command >button which will add a new record to my table. >On the click of command button it should view the last empty row of my table >in a form. The Click event should show [Event Proced...

How to make a Add key disabled after adding one record unless
Hi, I have a form where I am adding records. There are two boxes where one has to put the lineitemnumber and another box where one has to put the description. Unless the above two boxes are filled in the Add command box need to be disabled. Now, the problem is after adding one record the Add button is enabled and I cannot disable it. I would appreciate any help to solve this. The error message is: The methos is not supported The following is the code: Private Sub cmdadd_Click() On Error GoTo Err_cmdadd_Click If (IsNull(Me.LineItemID) = True) Or (IsNull(Me.Description) = True) Then ...

Excel 2000 vs. Excel 2002
I am having troubles with a workbook that I created that is havin problems opening. I created it in 2002, and it opens fine in Excel 2002 for other people However, when I send it to someone who has Excel 2000, it takes over a hour to open. Now I also made a very similar report that works just fine whe trasferred to excel 2000. Here are a couple of stats on the workbook that is having problems: 1.5mb 500+ externel links 500+ subtotals 200+ simple calculations (a1+b1; a1/b1;etc..) 1 Worksheet in the book. 2 columns with conditional formatting Thanks, Joh -- Message posted from http://ww...

Macro
Here is one for you experts - I am a novice I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can an...

Any FREAKIN' way to import DBX files into Outlook 2003
I've tried: Importing via Outlook | Import from another Program or File Importing via Outlook | Import Internet Mail and Addresses Exporting from Outlook Express Tried Many, many times... Can Microsucks make this any more complicated... It's a FREAKIN' DBX file collection NO - No other Application has it Open. YES - The Files ARE there YES - the Internet Account IS there Yes - I've wasted more of my time IMPORTING into Outlook Express in VISTA just to RE-EXPORT back to Outlook. What a bunch of freakin' idiots... Another 2 hours wasted - because one Microsoft applica...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

macro for invoice totals
I have this macro that inserts lines and copies a invoice header. Now I want to total the invoice. In the first row after a value in B in E the word totals must be inserted, H, I and K must be summed the amount of lines differ on each invoice but there is a heading from where it must be summed. CTNS(H), QTY(I), Total(K) Thanks a lot. Public Sub Deilv2() Dim LastRow As Long Dim row_index As Long Dim rng As Range Set rng = Range("B2:K25") Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row For row_index = LastRow - 1 To 26 S...

Opening .prn files in XL2000
I am using a software that does not save data/reports in .csv or .xls formats; only in printed versions. Is there a way to save the printed report in a file and open the file in XL2000? If there is, how is the print file produced, where is it saved, etc? A friend suggested setting up a generic printer but didn't know how to go about it. You may want to give that other software just one more chance--look under File and see if there is a SaveAs option. You may find something upon further review. But if you want to add a generic printer, I think it'll depend on your version of win...