Editing a Flat File with Code...

Posted on 2002-06-19
Last Modified: 2010-05-02
I have a flat file that I am going to import into access, except it is arranged differently then what Access can import in using the text delimited wizard for importing.  All I need to do to allow it to import correctly is to open the flat file, delete the first two lines of the file, and then before each record is imported I will need to delete the first few characters from the beginning of the record OR (and this one I'd prefer) put another " after the first character before the comma, so that Access will know to import that.

For example, here is what the flat file would look like (oh and it there is a blank line between lines/records):

This would be the first line:
0, -1, -3"

This would be the second line:
"0, "Name", "Address", "City", "State", "Zip Code"

This would be the 3rd line:
"1, "Name", "Address", "City", "State", "Zip Code"

I would change the other program to export it all delimited, but it needs to be this way for another older program we have.

Here is what I would prefer it to look like:

Get rid of this line and the next space:
0, -1, -3"

This would be the second line:
"0", "Name", "Address", "City", "State", "Zip Code"
"Name", "Address", "City", "State", "Zip Code"

This would be the 3rd line:
"1", "Name", "Address", "City", "State", "Zip Code"
"Name", "Address", "City", "State", "Zip Code"

I would really like to see if both ways if possible.  Also, would I want to import the flat file as I was manipulating it or would I want to import it after it had been manipulated?


Question by:zippy221
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 22

Expert Comment

ID: 7093432
Something like this:

open "thefile.txt" for input as #1
open "theoutput.txt" for output as #2

if not eof(1) then
  line input #1, dummyline
  if not eof(1) then
    line input #1, dummyline

while not eof(1)
  line input #1, dataline
  print #2, chr$(34); dataline
close #2
close #1
LVL 22

Expert Comment

ID: 7093438
Oh...comma AFTER the first number...

replace this:

print #2, chr$(34); dataline

with this:

iCommaPosition = instr(dataline,",")
if iCommaPosition =0 then
  ' error?
  print #2, dataline
  print #2, left$(dataline, iCommaPosition -1);chr$(34); mid$(dataline, iCommaPosition )
end if
LVL 18

Expert Comment

ID: 7093440
Dim ff as Integer
Dim i as Integer
Dim lineNum as Integer
Dim strLine as String
Dim strFile as String
Dim arrFields() as string

ff = FreeFile

Open "<path to your document>" for input as #ff

lineNum = 0
do while not eof(ff)
   if lineNum > 2 then '' this removes first two lines
       Line Input #ff, strLine

       '' option 1 (quotes first field)      
       arrFields = Split(strLine, ",")
       for i = 0 to UBound(arrFields)
          if i = 0 then
              strLine = chr$(34) & arrFields(0) & chr$(34)
              strLine = strLine & ", " & arrFields(i)
          end if
       '' END OPTION ONE

       '' option 2 (takes out first field)
       arrFields = Split(strLine, ",")
       for i = 1 to UBound(arrFields)
          strLine = strLine & arrFields(i) & ", "
       strLine = Left(strLine, len(strLine) - 2) '' take out trailing comma

       strFile = strFile & strLine & vbCrLf

   end if

ff = FreeFile

Open "<path to new file>" for output as #ff
print #ff, strFile

This is untested code... so some tweaking might have to be done.

Also, in the for loop, make sure you take out one of the options.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 7093447
You would probably want to edit the entire file before importing it.

You can open it and read it line by line:

dim sLine as string
dim sFile as string

open "c:\file.txt" for input as #1

do while not eof(1)
    Line input #1, sLine
    'Get rid of first three charecters or however many using Mid statement
    sline = mid(sline, 4)
    'You can also use instr to find a particular charecter in the line
    iPlace = instr(sLine, ",")
    'iPlace will return the position of the first comma
    'You can now get everything before or after the first comma then do the same thing again to continue to parse if you need to
    sLine = Left(sLine, iPlace - 1) 'Everything before
    sLine = mid(sline, iplace + 1) 'Everything after

    'Now place the line in the holder
    sFile = sFile & sLine & vbnewline


Close #1

'Now write sFile to a new file

open "c:\newfile.txt" for output as #1
print #1, sFile
Close #1

Now you have a new file formatted the way you want it.  These are the basics on how you can parse and change a text file.  I hope it all makes sense.  Let me know if you need anything clarified further.

Accepted Solution

olx earned 200 total points
ID: 7101334
if the file contains

0, -1, -3
"0, "Name", "Address", "City", "State", "Zip Code"
"1, "Name", "Address", "City", "State", "Zip Code"

name it to csv... then import it in access.


Expert Comment

ID: 7101335
sorry, first make a copy of the original flat file, then rename the copy to somename.csv  then go to access and then import it there...

sometimes im not so clear as i wish.

Author Comment

ID: 7396434
That worked great!  There are still some hurdles I have to overcome, since the first half of a record in the text file is text delimited and the second half is fixed length.

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA to copy paste columns form one file to other 20 100
vb6 connector to mongodb 2 141
Visual Studio 2005 text editor 10 49
Set WorkSheet  not Working 9 52
Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question