How to omit 25 rows of header while importing data to access

  • Thread starter Thread starter Apple
  • Start date Start date
A

Apple

I have an external file that i want to import to open with access.

This file's features

1. this is a *.csv file.
2. filesize 1, 000, 000 kB. (unmanageable filesize to open with word
processor or text)
3. first 25 rows are text, which are not arranged in tabular format. I do
not need these information but i am unable to delete them with any software
due to the huge filesize.
4. rows 26 onwards are in tabular format and these are the data i need.
5. data in rows 26 onwards, are separated by "comma" (,).

The reason I need to use access is due to the huge number of data, I cannot
open with Excel. I do not need to link any data.

In access, when i try to open the file in access, access will automatically
create a database and link the data. However, it detects the number of fields
based on the first few row of text and I end up having only one column,
losing all my data in the other columns.

How do I
(a) specify the file import so that access will skip the first 25 rows and
extract the data from row 26 onwards or
(b) import the data as a table without linking them (not sure if this will
help me to extract the data i need, but worth a try, maybe) or
(c) any other suggestions??
 
I have an external file that i want to import to open with access.

This file's features

1. this is a *.csv file.
2. filesize  1, 000, 000 kB. (unmanageable filesize to open with word
processor or text)
3. first 25 rows are text, which are not arranged in tabular format. I do
not need these information but i am unable to delete them with any software
due to the huge filesize.
4. rows 26 onwards are in tabular format and these are the data i need.
5. data in rows 26 onwards, are separated by "comma" (,).

The reason I need to use access is due to the huge number of data, I cannot
open with Excel. I do not need to link any data.

In access, when i try to open the file in access, access will automatically
create a database and link the data. However, it detects the number of fields
based on the first few row of text and I end up having only one column,
losing all my data in the other columns.

How do I
(a) specify the file import so that access will skip the first 25 rows and
extract the data from row 26 onwards or
(b) import the data as a table without linking them (not sure if this will
help me to extract the data i need, but worth a try, maybe) or
(c) any other suggestions??
Click to expand...

You could name the range (starting at the 26th row),
see Ken Snell's page...
"Read Data from EXCEL File via Query (SQL Statement)"
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

SELECT T1.*, 1 AS SheetSource
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFolder\MyFile.xls].
[WorksheetName$A2:U65536] as T1;

So you would start with $A26
 
Hi,

Another possibility is NotePad++, which has a macro recording/running
capability. See <http://notepad-plus.sourceforge.net/uk/site.htm>.

Of course there is always the old method... do the import entirely in
VBA where you would open the file, read until you find the start of the data,
for each row of data do an insert into the table until you get to the end of
the file, and then close the file.

Clifford Bass
 
Back
Top