Skip first three lines of CSV file (using DoCmd?) in MS Access

13,521

Solution 1

The DoCmd.TransferText method takes a SpecificationName as one of its arguments. Follow the instructions at How to Create an Import Specification to create and save an import specification.

Now go into Access Options, Navigation Options, and show hidden/system objects. You should now be able to open a table named 'mSysIMEXSpecs'. Find the import spec you created earlier based on the SpecName column. Change the 'StartRow' column for that import spec to 3 (the StartRow is zero-based).

Now you should be able to use the DoCmd.TransferText method passing the name of the import spec you created and it will skip your first three lines.

Solution 2

In Access VBA I would use two TextStream objects, one to .ReadLine the original CSV file line-by-line and the other to .WriteLine to a temporary file, skipping the first three lines of the input file. Do some web searches on Scripting.FileSystemObject and I'm sure you'll find some sample code for this.

Then I would use DoCmd.TransferText acImportDelim to import the temporary file into Access.

Solution 3

The Docmd.TransferText method is a good option to go with but as an alternative to modifying your import spec, you could consider importing everything and doing some validation after the import.

So you could, import the entire file and then use a delete query to delete data from the table.

As mentioned in a comment above, modifying the spec via a system table could be tricky for someone else to find where-as a delete query with a nice little comment in your code could work a treat. If it's required use it, if not it could be commented out.

Share:
13,521
extensa5620
Author by

extensa5620

The air of mystery is thick with this one.

Updated on June 04, 2022

Comments

  • extensa5620
    extensa5620 over 1 year

    I need to skip the first three lines of a CSV file when loading into MS Access. The default CSV import does not drop the first three lines. I am thinking of writing a macro to ignore the first three lines.

    My research has shown the DoCmd object but its methods do not cater to drop the first x lines.

    Any thoughts?

  • Gord Thompson
    Gord Thompson over 10 years
    +1 Sneaky! It is definitely a good idea to document this in the code if one decides to use this technique, otherwise the next poor soul who works on that database could go crazy trying to figure out why the initial lines get skipped. Still, thanks for sharing!
  • mwolfe02
    mwolfe02 over 10 years
    Good point. I've actually written a class module that manages my import/export specs programmatically because I got tired of hunting down existing specs when trying to debug problems. But that's an entire blog post that I didn't want to get into here!
  • mwolfe02
    mwolfe02 over 10 years
    +1 This approach was actually my first thought. Managing temp files carries its own set of overhead, but the programmer's intent would be more explicit without the need for good comments. Six and one-half dozen the way I see it.
  • mendel
    mendel about 10 years
    @mwolfe02 I just did this and it works thanks. I just wanted to point out that the StartRow is 0 based. in my case I wanted to skip 2 lines and I entered 2 in the StartRow, so for this example he should enter 3 in StartRow. if he entered 4 he would lose a line of data.
  • mwolfe02
    mwolfe02 about 10 years
    Fixed it. Nice catch @mendel. Thanks.