Linking to Excel Spreadsheet

[Soupy8728]

Good morning all,

I have an Excel spreadsheet that gets downloaded daily from another source. The first 3 rows are unusable data and need to be ignored. I cannot go in every time and delete the rows as that would be impractical. 2nd part of my dilemma: I can't import the data into a temp table as some of the data gets truncated as access "looks" at the first 8 rows of data and if they don't meet the requirements, data gets truncated. Here is what I'd like to do:

1. Download file and put in a specific folder.
2. Use Access to link to this file but start on row 3 (where the actual headers reside).


3. Have this link stay in place and just replace the Excel file in the specified folder on a daily basis (same file name).

I've played around with the Connection string box on the Linked Table Manager but I just can't get it to work. I don't need to edit any of the data on the Excel file, simply view it from Access. Thanks as always!

Sincerely,
Carl aka Soupy8728

[Welshgasman]

What identfies those first three rows?
You could easily delete them with automation.

[Soupy8728]

What identfies those first three rows?
You could easily delete them with automation.

The first 3 rows are just information about the Excel file such as date downloaded, file name. I don't need to see that stuff. How would I go about deleting those rows with automation and where would that automation reside? Within Access? Thank you.

[Welshgasman]

Well you could exclude them if you knew what was in the first column and that data was unique.?
You would carry out the automation from within Access. Just open the file after download, delete rows 1 -3, save the file then process it as you require.

If you could identify them, then exclude them in the query that extracts the data.

ChatGPT reckons you can do it with a sub query.

SELECT *
FROM MyExcelData
WHERE ID NOT IN (
    SELECT TOP 3 ID
    FROM MyExcelData
    ORDER BY ID
)
ORDER BY ID;

You might not need the ORDER clauses.

https://chatgpt.com/share/689c86e8-5...9-ed5fa3d9586c

[CJ_London]

Use a sql query where you can specify a range

[CJ_London]

I would use this sql

SELECT
*
FROM
[Data$A4:G] AS xlData IN 'C:\Path\filename.XLSX' [Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]

for this spreadsheet



produces this result