Solved

Editing a Flat File with Code...

Posted on 2002-06-19
7
193 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:gencross
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

728 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

10 Experts available now in Live!

Get 1:1 Help Now