Solved

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

Posted on 2011-02-20
18
1,637 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 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 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 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 85

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 85

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 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 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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Six Sigma Control Plans

636 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