Solved

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

Posted on 2011-02-20
18
1,394 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
A short article about a problem I had getting the GPS LocationListener working.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now