Excel Query

[ssalazarjr]

Is their a way to ignore the first two rows when importing an xls file into Excel?

I download an xls file from my POS software but it includes two useless rows to start at the spreadsheet. The data headers start at row 3 and the rest of the data is volatile underneath the headers.

 

 

I used Excel Query Wizard to walk me through importing the data from the xls file. Excel Query Wizard recognized the first column as “Export” and each additional column as F(n). I understand it does that because it assumes that I should have column headers in the first row. I also understand that because my xls file has “Export” in cell A1, that Excel Query Wizard recognizes it as a column header. But my column headers actually begin in row 3.

 

So what I did was import all the columns (example: Export, F2, F3, etc), then I renamed them by using “AS” in the import language (example: Export as “Date”, F2 as “Location”, F3 as “Employee”, etc).

 

But what I would really prefer is for the query to ignore the first two rows in the xls file and import the columns based on the column headers that begin in row 3. It would also be nice if I could tell the query to import “Location” without having to use “as”.

[justsomeguy]

This isn't really a SQL question, but if you don't see an option in the importer then why not just delete the first 2 rows?

[ssalazarjr]

 

This isn't really a SQL question

 

Excel has the "Command Type" listed as SQL.

 

From what I've researched, the "Command Text" looks like SQL to me:

 

SELECT

`Sheet1$`.Export as "Date", `Sheet1$`.F2 as "Invoice #", `Sheet1$`.F3 as "Location", `Sheet1$`.F5 as "Phone", `Sheet1$`.F7 as "Phone #", `Sheet1$`.F8 as "Term", `Sheet1$`.F9 as "Rate Plan", `Sheet1$`.F11 as "Employee"FROM `C:Data Dump FilesRQ-Activation Report-Sales Tracker.xls`.`Sheet1$` `Sheet1$`ORDER BY `Sheet1$`.Export DESC

 

 

why not just delete the first 2 rows?

 

I am trying to save the file from my POS software without having to open it and tamper with it. I have my Store Managers performing these tasks, so I'm trying to make it as "user friendly" as possible. "Work arounds" just open the door for mistakes.

[justsomeguy]

Well, if the data isn't in the format you need then anything is a workaround. So, your options are to either change how it gets exported so it fits with Excel, or change how it gets imported. I haven't used the query wizard before, but your options are going to be limited to what that offers. There isn't a way in SQL to tell it to skip the first few rows, especially if the rows are blank or contain text instead of data.