Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-20
18
Medium Priority
?
1,736 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 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 48

Expert Comment

by:Dale Fye
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
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 
LVL 40

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 19

Assisted Solution

by:Richard Daneke
Richard Daneke earned 300 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 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 85
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 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 40

Expert Comment

by:als315
ID: 34942623
Can you try convert your xls to csv and import it?
0
 
LVL 85
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 40

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Progress

721 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