Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1843
  • Last Modified:

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)
0
drtopserv
Asked:
drtopserv
  • 6
  • 5
  • 3
  • +3
5 Solutions
 
Neil RussellTechnical Development LeadCommented:
Please provide database structure and sample excel data
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Dale FyeCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
als315Commented:
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
 
Richard DanekeCommented:
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
 
drtopservAuthor Commented:
may plz an example how to do it?
give me maybe a code to do this or an accdb file :}
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
drtopservAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
als315Commented:
Can you try convert your xls to csv and import it?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
drtopserv: That question will be deleted in 24 hours. You'd be better off attaching those same files here.
0
 
drtopservAuthor Commented:
attached the 2 files.

sample.xlsx
MYpROJECT.accdb
0
 
als315Commented:
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
 
drtopservAuthor Commented:
Still having problems importing the data
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now