[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Editing a Flat File with Code...

Posted on 2002-06-19
7
Medium Priority
?
200 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
[X]
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
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 800 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
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…
Suggested Courses

656 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