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?
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.
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.
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.
