Solved

Editing a Flat File with Code...

Posted on 2002-06-19
7
194 Views
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"
OR
"Name", "Address", "City", "State", "Zip Code"

This would be the 3rd line:
"1", "Name", "Address", "City", "State", "Zip Code"
OR
"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?

Thanks,

Zippy221
0
Comment
Question by:zippy221
7 Comments
 
LVL 22

Expert Comment

by:rspahitz
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
  endif
endif

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

Expert Comment

by:rspahitz
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
else
  print #2, left$(dataline, iCommaPosition -1);chr$(34); mid$(dataline, iCommaPosition )
end if
0
 
LVL 18

Expert Comment

by:bobbit31
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)
          else
              strLine = strLine & ", " & arrFields(i)
          end if
       next
       '' END OPTION ONE

       '' option 2 (takes out first field)
       arrFields = Split(strLine, ",")
       for i = 1 to UBound(arrFields)
          strLine = strLine & arrFields(i) & ", "
       next
       strLine = Left(strLine, len(strLine) - 2) '' take out trailing comma
       '' END OPTION TWO (MAKE SURE TO REMOVE ONE OF THE OPTIONS)

       strFile = strFile & strLine & vbCrLf

   end if
loop

close(ff)
ff = FreeFile

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

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.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 4

Expert Comment

by:gencross
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
    'or
    sLine = mid(sline, iplace + 1) 'Everything after

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

loop

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.
0
 
LVL 1

Accepted Solution

by:
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.

:)
0
 
LVL 1

Expert Comment

by:olx
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.
0
 

Author Comment

by:zippy221
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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms access #TYPE! error on report when no data 4 75
VB 6.0 printer how to align 6 62
Copy a row 12 61
VB6 - Scroll Mouse wheel on Picturebox 13 42
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now