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

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

 

[guylen]

"Apple" <http://[email protected]> a écrit dans le message de
Photos de Facebook - Photos de profilRetour à Facebook
3 photos

....

 

[Piet Linden]

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

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

 

[Clifford Bass]

Hi,

You could edit it with the free JujuEdit. See
<http://www.jujusoft.com/software/edit/>. It rocks with huge files!


Clifford Bass

 

[Clifford Bass]

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