import csv file into access without some rows

[marianne27]

Hi,
i need procedure in VBA to import data into access from csv excel file without some records,, as header and footer. Example,,, i have table in csv file, which contains some
sentence which not belong table date
_______________________________________
A1 this is some sentence title.......
A2 title
A3.......
A7 DATA DATA DATA DATA DATA
A8 DATA DATA DATA DATA DATA
......
....
A2210 DATA DATA DATA DATA
A2211 this is some sentence
A2212
_______________________________________________

My Acess shoud contain only record between A7 to A2210. Does anyone knows procedure or whatever in VBA who solves my problems ?

thanks a lot

 

[earthasa]

Hi Marianne,
You could import the whole thing, then delete the first 6 records. If you don't want the data to be finalized in the database with those 6 records included, wrap the process inside a transaction.

Or, you could also export the Excel file without the first 6 rows as part of the data.
Or, you could identify the first 7 records based on what type of data they contain using SQL to reference the CSV file, and include WHERE conditions that exclude those first 6 records.
Or, you could add a column in Excel with a special value for those first 6 rows, and have a condition in your query to exclude based on that flag.

Or -- probably slowest but likely bareable in this case -- you could open a recordset based on the CSV file, advance to the 7th record, then import the data one record at time in a loop.

You might get more detailed info on an Access forum/group.

Asa

 

[marianne27]

This is not help me.I want procedure, because i have a lot files csv, and i need procedure to do automaticaly that process for me.Not always first 7 rows i need delete.. and some in the end of file senteces.We need procedure that recognize in column 1 numerical data, and if A not numerical data , that rows must be deleted.I several times get csv file, and i must open in access, because other datebases.

Thanky for attemp to help me

 

[earthasa]

Hi Marianne,
I had no indication from your original question that you were looking to identify numerical data in the first column.
Since Ihave not done the task lately, it would take me some time to look up the correct VBA syntax. If you are looking for ONE routine to handle any CSV data going into ANY table, it is very complicated.

If the CSV files always have the same columns and are are always going into the same table, you can use an action query with the following condition on the first field:

IsNumeric([Field1])


I hope my small nudges help you along your way to a solution, in any case.

Asa