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

[sabavno]

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.

 

[katho]

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

 

[ajdesalvo]

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.

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[Crystalguru]

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

 

[ajdesalvo]

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"