import excel file with null values in numeric column into access 2007
Hi,
I have excel file which have numeric column that contain null values,
i need to be succesfull inmporting the file into new table with any error of null value (to be inserted with 0 value) by vba code in access 2007 .
also to choise what exactly field to import (not all fields in the excel sheet)
I have excel file which have numeric column that contain null values,
i need to be succesfull inmporting the file into new table with any error of null value (to be inserted with 0 value) by vba code in access 2007 .
also to choise what exactly field to import (not all fields in the excel sheet)
Neil Russell
Please provide database structure and sample excel data
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
NO POINTS PLEASE
I agree with LSM on this one. That method allows you to accept data in the Excel format, then you can run some error checking on the data in that table and once that data is accurate, you can use a query to move it into you "production" table, and can use Access function calls to convert data into the right datatype or to fill in NULLs and that type of thing.
NO POINTS PLEASE
I agree with LSM on this one. That method allows you to accept data in the Excel format, then you can run some error checking on the data in that table and once that data is accurate, you can use a query to move it into you "production" table, and can use Access function calls to convert data into the right datatype or to fill in NULLs and that type of thing.
NO POINTS PLEASE
I always prefer convert excel files to csv format, import it as text (or read with VBA sub) and then get from text any values.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
may plz an example how to do it?
give me maybe a code to do this or an accdb file :}
give me maybe a code to do this or an accdb file :}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For example, if you wish to update all NULL values in the Cost1 column to 0, you could then do this (AFTER importing the spreadsheet):
CurrentProject.Connection.
This would ONLY update those rows where Cost1 is NULL. Rows which have an existing value in Cost1 would be unaffected.
CurrentProject.Connection.
This would ONLY update those rows where Cost1 is NULL. Rows which have an existing value in Cost1 would be unaffected.
ASKER
LSMConsulting,
When i do the import process, i`ll have error table. which tells me that some field been not imported cuz it contain blanks, i still need that column to be number type , but this prevent me to import it as number with fill the blank cells with 0 for example.
plz try to check the file attached. with row 10 which have blank values
When i do the import process, i`ll have error table. which tells me that some field been not imported cuz it contain blanks, i still need that column to be number type , but this prevent me to import it as number with fill the blank cells with 0 for example.
plz try to check the file attached. with row 10 which have blank values
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you try convert your xls to csv and import it?
ASKER
drtopserv: That question will be deleted in 24 hours. You'd be better off attaching those same files here.
ASKER
If you don't like convert xlsx to csv, you can crete update query, which will update all NULL values in imported table to 0.
ASKER
Still having problems importing the data