IMPORT NUMBER FIELD AS TEXT 1

[marchristensen]

I am importing an Excel spreadsheet into Access. One of the columns on the spreadsheet contains numbers. I have defined the cells in this field as text in Excel. I want the field to be a text (alpha) field in Access. I have tried both Import and Link Tables, and the field always turns into a numeric field in Access. Is there any way I can make this field come from Excel to Access as text (alpha).

Many thanks for your help.

 

[B827]

If the format of the Excel Spreadsheet is always the same then you can import into an existing table. By using a delete query you can clear the previous data before importing the new data. You can set the field in question to Text in the design of the Access table.

For a one off import do the import once. Delete the contents change the Field formats and import into this table. Sandy

 

[marchristensen]

Sandy
Thanks for your speedy response. I followed your instructions and was able to get them to work, but only when I saved the Excel spreadsheet as a .csv (comma delimited) file. I did the import as you suggested, appending to the existing Access table. It worked! (YEAH)

However, when I bring in the saved .xls Excel file, I don't seem to have the option of adding to an existing table in Access. I must create a new table, and the number field switches from text (alpha) to numeric.

So two follow-up questions: Is there any way to make this work using an .xls Excel file? Is there any way to make this work with fewer steps?
Thanks again.
Lynn

 

[B827]

Hi Lynn

I think that you've probably had bit longer wait this time I looked at your posting just before I went home.

I have several systems which take Excel Output and place into Access tables. The hardest part is getting the fields formatted. You may have noticed that Text fields are formatted as 255 characters no matter how many are actually used. Number fields often need changing as well.

To get back to your main problem - once you have got the destination table sorted then you can use macros or code to load the spreadsheet. Assuming you're using Access95/97 or 2000 then this should apply, I'm not sure about the XP. I like to use code, if then source file name is likely to change, which some of mine do, then I have used to Common Dialog Control on a Form which allows searching for the source file. In code the command you need is:

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

I usually dot the i's and cross the t's by putting something in all the fields I can. So I end up with:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "SAP_Period", Fname, True

In this case Fname is the file transferred in a variable looking like:

"G:\Import Files\Period5.xls"

and the first row of the spreadsheet is field names.

Using the TransferSpreadhsheet Instruction in a macro will lead you through the process in a reasonably straight forward way. And there is an option to covert macros to Code once you have got it working.

Let me know if this doesn't help.
Sandy

 

[marchristensen]

Sandy
Thanks again for your help. I still can't get it to work with the .xls file. Any ideas?

I will try some of the programming methods you suggest, although my programming skills are a bit rusty (like me!).

Thanks again for your help and follow-up.
Lynn

 

[B827]

Post Your E-Mail address and I'll send a little working example (database and spreadsheet). It is much easier to start from something which is known to work and copy to your own application. Sandy

 

[marchristensen]

Sandy
You are so kind.
marchristensen@hotmail.com
I will wait to hear from you.
Thanks again.
Lynn