Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Editing a Flat File with Code...

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
zippy221
Asked:
zippy221
1 Solution
 
rspahitzCommented:
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
 
rspahitzCommented:
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
 
bobbit31Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gencrossCommented:
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
 
olxCommented:
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
 
olxCommented:
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
 
zippy221Author Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now