Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to have a control over the excel file when import into Access

Status
Not open for further replies.

sabavno

Programmer
Jul 25, 2002
381
CA
Hi,

I believe many of Access developers have been involved into working with Excel files (import/export) via VBA. So, I was hoping someone could help me with topic.

When I import the Excel file into Access table, I lose some data due to errors such data type conversion failure, etc.

Is there a way to control that? What are the best techniques to apply?

I will appreciate any tips as regards to Excel file handling in the Access.

Thanks.
 
Hi sabavno,

The workaround I use is to save the excel-file as a textfile and then import it into access (using code, but that shouldn't be necessary).
If anyone knows how to control the import wizard built in into access, please let me know too - I have not seen anything on the web relating to this yet.

Greetings,
Katho
 
Yes and No.

Type conversion failures could be a column in the spreadsheet is formatted as Text but may contain mostly numbers. One or more cells may have alphanumeric data in them. Therefore, when importing the ss in, those fields would get dropped out.

If you import the excel spreadsheet into an existing table, you could set all of the field types to Text. This way all the data will come in. Then you can deal with the formatting of the data in Access.

[thumbsup2] Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"
 
Anthony,

I am running into a similar problem with importing my excel spreadsheet. The spreadsheet has a field called Ext. Ref Num., this field can be varchar2. When I import a file in I get a Type Conversion Failure on Ext Ref Num. The field is set to TEXT in Access.

What's wrong here?

thanks
crystalguru
 
I had trouble with the column being named with periods in the heading (Ext. Ref Num.).

Seemed to import okay when I removed the dots.

Let me know!

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top