Importing Null Values

[khosseini]

I am working on a large import app, from excel 2003 to access 2003, and I have gotten it to import several workbooks and worksheets within the workbooks, it is however also importing the fields with nothing in them. I know that is how it is set up and not sure how to say if the fields are empty or include one 0 do not import them. Here is my snippet.

If Dir("C:\Group1.xls") <> " " Then
DoCmd.TransferSpreadsheet acImport, , "Students", "C:\GROUP1", True, "Students1!A1:I23"
DoCmd.TransferSpreadsheet acImport, , "FA03", "C:\GROUP1", True, "FA03!A123"
DoCmd.TransferSpreadsheet acImport, , "SP04", "C:\GROUP1", True, "SP04!A123"
DoCmd.TransferSpreadsheet acImport, , "FA04", "C:\GROUP1", True, "FA04!A123"
DoCmd.TransferSpreadsheet acImport, , "SP05", "C:\GROUP1", True, "SP05!A123"
End If

Thanks!
Kendra

 

[PHV]

A common way is to import the worksheets in a temporary table first and then running filtered append queries.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[khosseini]

That was the conclusion I was starting to come to myself, I was just hoping for that magic bullet that would do it at this stage thanks for the help!

~Kendra

 

[Darrylles]

Hi K,

I've been working a lot lately with this kind of 'Excel' import rubbish.

Have you thought of programmatically linking the Excel sheet(s) which you are then able to simply treat as a table/(s)?

It adds another 'complexity' dimension i.e. you have to have an agreed naming 'convention' for the sheets in order for you to be sure that you are linking to the correct sheets, but this was the best 'automated' way of doing this for me.

Hope this helps,

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[khosseini]

Thanks, that is a good thought.