Removing unwanted rows from a csv file to an imported table.

[Margaret]

I have a csv data dump that I need to import into a table.
It has 7 rows of miscellaneous data before the field names
are given and then the data.

I created the field names and then imported the csv file
to the table but all that appears is the 7 rows, plus one
extra row with the header in it. The other 54,000 lines
come through as an error file.

Is there someway to remove these rows so that only the
data I need comes through.

Eventually there will be download monthly that I want to
build a module for to do all the work.

Appreciate your response and help.
Regards
Margaret

 

[Jason Wood]

You can edit a csv manually, using any text editor, or
Excel. But you should try to fix things at the source. Do
you control the generation of the csv? Is it direct from
a database, or through some other interface? Is it
possible the csv file was generated once, and then your
data was appended to it later? Or does it look like
multiple tables are being dumped into the csv at once?

 

[Margaret]

Thanks for this. The csv file is a fixed report generated
through a core reporting system available to me. I can
remove the files in Excel and then save it, then do the
import into Access and go from there, but as we are trying
to automate the processes I was hoping (praying) for an
easy solution. Someone mentioned pre-tables to me, but I
am not sure how to set this up.
The csv data comes out the same each month, no appending.
Thanks for your help. I will take it from here UNLESS you
have some info on using pre-tables to strip out misc. data.
Regards
Margaret

 

[John Nurick]

Hi Margaret,

I usually fix this sort of thing by using a Perl script, which can be
called in a batch file or from within Access
(http://www.mvps.org/access/api/api0004.htm for details of how to make
Access wait until the other process has finished).

Dumping the first 7 lines of the file is a one-liner that can be done
direct from the commandline with

perl -ne"print if $. > 7" inputfile > outputfile

If Perl isn't or can't be installed on your machines, you can do the
same thing in Access VBA. The idea is to open the file as a textfile,
and read it line by line, counting the lines. After the 7th line, write
each line to an output file after reading it, and stop when you get to
the end. Then import the modified file in the usual way.

This can be done either with the old-fashioned Open #, Line Input # and
Write # file input/output commands, or the newer FileSystemObject and
TextStream objects. If your files contain or may contain Unicode text
you should use the objects.

Thanks for this. The csv file is a fixed report generated
through a core reporting system available to me. I can
remove the files in Excel and then save it, then do the
import into Access and go from there, but as we are trying
to automate the processes I was hoping (praying) for an
easy solution. Someone mentioned pre-tables to me, but I
am not sure how to set this up.
The csv data comes out the same each month, no appending.
Thanks for your help. I will take it from here UNLESS you
have some info on using pre-tables to strip out misc. data.
Regards
Margaret

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Margaret]

Thank you for your help John. I will try it.
Have a great day.

-----Original Message-----
Hi Margaret,

I usually fix this sort of thing by using a Perl script, which can be
called in a batch file or from within Access
(http://www.mvps.org/access/api/api0004.htm for details of how to make
Access wait until the other process has finished).

Dumping the first 7 lines of the file is a one-liner that can be done
direct from the commandline with

perl -ne"print if $. > 7" inputfile > outputfile

If Perl isn't or can't be installed on your machines, you can do the
same thing in Access VBA. The idea is to open the file as a textfile,
and read it line by line, counting the lines. After the 7th line, write
each line to an output file after reading it, and stop when you get to
the end. Then import the modified file in the usual way.

This can be done either with the old-fashioned Open #, Line Input # and
Write # file input/output commands, or the newer FileSystemObject and
TextStream objects. If your files contain or may contain Unicode text
you should use the objects.

Thanks for this. The csv file is a fixed report generated
through a core reporting system available to me. I can
remove the files in Excel and then save it, then do the
import into Access and go from there, but as we are trying
to automate the processes I was hoping (praying) for an
easy solution. Someone mentioned pre-tables to me, but I
am not sure how to set this up.
The csv data comes out the same each month, no appending.
Thanks for your help. I will take it from here UNLESS you
have some info on using pre-tables to strip out misc. data.
Regards
Margaret

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.