Importing from Excel row order

[T2B]

If you import data from an Excel spread sheet into an Access table
will the data always insert in the same order as the spread sheet?
For example, will Row 20 in the spreadsheet be Row 20 in the Access
table?

 

[Jerry Whittle]

Databases don't work that way. In theory you can put all the records in a
table in a certain order; close the database; reopen it and the records will
be in different order.

What you do in a database is to sort the records in a query to give them an
order. Therefore if your Excel spreadsheet has the rows numbered in a series,
then you could see the records in a set order using a query.

 

[Jeff Boyce]

Why? Why bother pushing data from Excel over into Access? What will having
it in Access allow you to do that you can't already do?

(these are not facetious questions, but asked to elicit more specific
description. Since Access is NOT a spreadsheet, importing Excel data into
Access is really only the first step of several you'll need to take to get
good use of Access.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[T2B]

I'm asking the question because I pull raw sales data into an Access
table. Once in the table some data massaging is done.

The first column of the xls has a date which represents the date for
all succeeding rows until another date appears like:

09/01/2009 $20.00
null $25.00
null $21.00
null $15.00
09/02/2009 $15.00
null $15.00
null $22.00
09/03/2009 $12.00
null $25.00
null $21.00
null $18.00

etc.

If I can expect the rows to import in the same order as above, as in
the xls, I can run a function to fill down the dates for the nulls
once in Access.

If I can't expect the rows to import in the the same order I'll need
to come up with another routine.

Thanks for your answers.

 

[Hans Up]

I'm asking the question because I pull raw sales data into an Access
table. Once in the table some data massaging is done.

The first column of the xls has a date which represents the date for
all succeeding rows until another date appears like:

09/01/2009 $20.00
null $25.00
null $21.00
null $15.00
09/02/2009 $15.00
null $15.00
null $22.00
09/03/2009 $12.00
null $25.00
null $21.00
null $18.00

etc.

If I can expect the rows to import in the same order as above, as in
the xls, I can run a function to fill down the dates for the nulls
once in Access.

Insert a column in the spreadsheet. In the first cell of that column,
enter a formula to return the row number of that cell's address:

=ROW(A1)

Then copy that formula to the other cells in the column. After
importing to Access, you can use the values from that column to
establish your row order.

If I can't expect the rows to import in the the same order I'll need
to come up with another routine.

If that Excel ROW() function approach is impractical, you could open the
worksheet as an ADO record set, and move through the rows to insert
each into your Access table.

 

[T2B]

Yes, Hans Up, that looks good, meanwhile I worked this up:

fill_col = 1 'Column to fill with line numbers
item_col = 8 'Column with most rows
rowStart = 1
intCount = 0
For i = rowStart To Cells(Rows.Count, item_col).End(xlUp).Row
Cells(i, fill_col) = intCount + 1
intCount = intCount + 1
Next i