Solved

import excel file with null values in numeric column into access 2007

Posted on 2011-02-20
18
1,466 Views
Last Modified: 2012-05-11
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)
0
Comment
Question by:drtopserv
  • 6
  • 5
  • 3
  • +3
18 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 34937092
Please provide database structure and sample excel data
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 34937167
You can build an Access table that mimics your Excel file structure, and you can set the Defaults of the columns where you expect NULL data to be 0. Then, import into THAT table first, and then move your data from that temporary table into your "live" table. Unless you have a very high number of columns that don't need to be imported, this often is a much quicker and easier-to-use format than automating Excel.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 34937773
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

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 39

Expert Comment

by:als315
ID: 34938259
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.
0
 
LVL 18

Assisted Solution

by:Richard Daneke
Richard Daneke earned 100 total points
ID: 34939021
This can be approached using Excel in Access 2007.
As you import Excel into Access with the Wizard you can select each field. When on the upper right hand side you see an option for data type -- General, Text, number, date, etc..., you can choose each column and set the field type.
For the numbers you want imported, select numbes.   For the columns you do not want, choose Do Not Import.
Import the spreadsheet data as a new table, when appending or updating the fields into your Access, create a new query field to convert your Null fields.  For example, using the QBE grid  a column of Amount can be used as a new query field  "Amount0 : NZ([Amount], 0)"    The NZ function substitues a 0 for null values in the query.
0
 

Author Comment

by:drtopserv
ID: 34940600
may plz an example how to do it?
give me maybe a code to do this or an accdb file :}
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 34942320
DoDahD gave you a pretty good synopsis of how to do this, but I'll try again:

1) Using the External Data tab, click Excel in the Import grouping. The Wizard should pop up, where you can locate your file, etc.
2) Select to either import into a NEW table or an EXISTING table. I'd try the NEW table first, since this generally is the easisest to work with.
3) Work through the "Import Spreadsheet Wizard", where you can specify which rows to import, Field Headings, etc. Take special notice of the 2nd and 3rd pages, as this is where you specify whether to use the first row as "headings", and where you also setup the various columns and their datatypes
4) AFTER running the Wizard, you should then have a fully populated table.

After building your table, you can then examine the data contained in that table using Access methods. For example, you can build a query that shows you all records with a NULL value in a column named "FirstName" like this:

SELECT * FROM YourTable WHERE FirstName IS NULL

0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 34942410
Also, given the sample files you posted at your duplicate question (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26835852.html), I was able to download those files and use the steps above to import the Excel file into the Access database in about 3 minutes.

Once in there, you can then use VBA/SQL techniques to clean up that table and data as needed.
0
 
LVL 84
ID: 34942431
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.Execute "UPDATE YourNewTable SET Cost1=0 WHERE Cost1 IS NULL"

This would ONLY update those rows where Cost1 is NULL. Rows which have an existing value in Cost1 would be unaffected.
0
 

Author Comment

by:drtopserv
ID: 34942442
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
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 34942544
You didn't attach any files to this question. Please do so in order for the other Experts to be able to download and assist as needed.

You'd have to change that AFTER the import. As I showed earlier, you can use standard SQL to "massage" your data after it's been imported, and then you'd move that data into your live tables. It really doesn't matter what Datatype is assigned to your imported table, since that is NOT your "live" table.

Once you've massaged your data, you'd then move everything from the temporary table into an existing, live table in your database. For example, if your import table is named "tmpExcelImport", and your live table is named "tblLiveData", you would do something like this:

CurrentProject.Connection.Execute "INSERT INTO tblLiveDate(Col1, Col2, Col3) SELECT ColA, CInt(ColB), CDbt(ColC) FROM tmpExcelImport"

Note the use of "CInt" and "CDbl". These are conversion functions that can be called to help you change data from one type to another as you do these sorts of operations.

CInt changes the data to an Integer datatype

CDbl changes the data to a Double datatype

See this link for info on all type conversion functions:
http://office.microsoft.com/en-us/access-help/type-conversion-functions-HA001229018.aspx
0
 
LVL 39

Expert Comment

by:als315
ID: 34942623
Can you try convert your xls to csv and import it?
0
 

Author Comment

by:drtopserv
ID: 34942691
0
 
LVL 84
ID: 34942703
drtopserv: That question will be deleted in 24 hours. You'd be better off attaching those same files here.
0
 

Author Comment

by:drtopserv
ID: 34943492
attached the 2 files.

sample.xlsx
MYpROJECT.accdb
0
 
LVL 39

Expert Comment

by:als315
ID: 34959655
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.
0
 

Author Closing Comment

by:drtopserv
ID: 35340084
Still having problems importing the data
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question