Import from EXCEL selectively

  • Thread starter Thread starter MikeF
  • Start date Start date
M

MikeF

Re table "CashRec" ...

An easy way to control the number of records that are imported from Excel is
to enter <>0 in the Data Validation property of the TOTAL field.

However it needs to be a bit more selective, and should be:

TOTAL field must be <> 0, UNLESS text in the CATEGORY field equals "Cash".

Can anyone assist with the proper syntax?

Thanx,
- Mike
 
Kardan,
Thanx for the msg.

Presumably you mean using MS Query.
Will give it a go, and advise.

Is there any alternate method, using the Access table, you would also suggest?

Thanx,
- Mike

Kardan via AccessMonster.com said:
Hi Mike

Have you tried to query fromo the Excel spreadsheet? This way you could put
the criteria in the query.

The syntax of the SQL would be..

SELECT * FROM [Excel 8.0;HDR=Yes;C:\YourPath\YourFile.xls] WHERE (TOTAL <> 0
OR CATEGORY ='Cash';

Hope this helps.
Re table "CashRec" ...

An easy way to control the number of records that are imported from Excel is
to enter <>0 in the Data Validation property of the TOTAL field.

However it needs to be a bit more selective, and should be:

TOTAL field must be <> 0, UNLESS text in the CATEGORY field equals "Cash".

Can anyone assist with the proper syntax?

Thanx,
- Mike
Click to expand...

--
Regards,

Richard
www.kardanconsulting.co.uk

Message posted via AccessMonster.com
Click to expand...
 
Kardan's suggestion has nothing to do with using MS Query.

The suggestion is to create a query using the SQL posted. You'd do that
instead of linking to the spreadsheet.

If the intent is to import the data into Access, you'd use that query as the
basis of an Append query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MikeF said:
Kardan,
Thanx for the msg.

Presumably you mean using MS Query.
Will give it a go, and advise.

Is there any alternate method, using the Access table, you would also
suggest?

Thanx,
- Mike

Kardan via AccessMonster.com said:
Hi Mike

Have you tried to query fromo the Excel spreadsheet? This way you could
put
the criteria in the query.

The syntax of the SQL would be..

SELECT * FROM [Excel 8.0;HDR=Yes;C:\YourPath\YourFile.xls] WHERE (TOTAL
<> 0
OR CATEGORY ='Cash';

Hope this helps.
Re table "CashRec" ...

An easy way to control the number of records that are imported from
Excel is
to enter <>0 in the Data Validation property of the TOTAL field.

However it needs to be a bit more selective, and should be:

TOTAL field must be <> 0, UNLESS text in the CATEGORY field equals
"Cash".

Can anyone assist with the proper syntax?

Thanx,
- Mike
Click to expand...

--
Regards,

Richard
www.kardanconsulting.co.uk

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
Now I understand.
That will not work, as it's linked to a static file.
There are AT LEAST 500 different spreadsheets, each with different info in
the relevant fields, that the info needs to be imported from.


Douglas J. Steele said:
Kardan's suggestion has nothing to do with using MS Query.

The suggestion is to create a query using the SQL posted. You'd do that
instead of linking to the spreadsheet.

If the intent is to import the data into Access, you'd use that query as the
basis of an Append query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MikeF said:
Kardan,
Thanx for the msg.

Presumably you mean using MS Query.
Will give it a go, and advise.

Is there any alternate method, using the Access table, you would also
suggest?

Thanx,
- Mike

Kardan via AccessMonster.com said:
Hi Mike

Have you tried to query fromo the Excel spreadsheet? This way you could
put
the criteria in the query.

The syntax of the SQL would be..

SELECT * FROM [Excel 8.0;HDR=Yes;C:\YourPath\YourFile.xls] WHERE (TOTAL
<> 0
OR CATEGORY ='Cash';

Hope this helps.

MikeF wrote:
Re table "CashRec" ...

An easy way to control the number of records that are imported from
Excel is
to enter <>0 in the Data Validation property of the TOTAL field.

However it needs to be a bit more selective, and should be:

TOTAL field must be <> 0, UNLESS text in the CATEGORY field equals
"Cash".

Can anyone assist with the proper syntax?

Thanx,
- Mike

--
Regards,

Richard
www.kardanconsulting.co.uk

Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
You can generate the SQL dynamically so that it refers to each of the 500
spreadsheets in sequence.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MikeF said:
Now I understand.
That will not work, as it's linked to a static file.
There are AT LEAST 500 different spreadsheets, each with different info in
the relevant fields, that the info needs to be imported from.


Douglas J. Steele said:
Kardan's suggestion has nothing to do with using MS Query.

The suggestion is to create a query using the SQL posted. You'd do that
instead of linking to the spreadsheet.

If the intent is to import the data into Access, you'd use that query as
the
basis of an Append query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MikeF said:
Kardan,
Thanx for the msg.

Presumably you mean using MS Query.
Will give it a go, and advise.

Is there any alternate method, using the Access table, you would also
suggest?

Thanx,
- Mike

:

Hi Mike

Have you tried to query fromo the Excel spreadsheet? This way you
could
put
the criteria in the query.

The syntax of the SQL would be..

SELECT * FROM [Excel 8.0;HDR=Yes;C:\YourPath\YourFile.xls] WHERE
(TOTAL
<> 0
OR CATEGORY ='Cash';

Hope this helps.

MikeF wrote:
Re table "CashRec" ...

An easy way to control the number of records that are imported from
Excel is
to enter <>0 in the Data Validation property of the TOTAL field.

However it needs to be a bit more selective, and should be:

TOTAL field must be <> 0, UNLESS text in the CATEGORY field equals
"Cash".

Can anyone assist with the proper syntax?

Thanx,
- Mike

--
Regards,

Richard
www.kardanconsulting.co.uk

Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
Back
Top