Importing from excel.

[acunha76]

I'm using DoCmd.TransferSpreadsheet to import some data that have numbers and text in one columnn.
EX:

12124TR51236
12RE531TY
100099093
38880929039

I have defined the excel columnn as text and i execute the function wiyhout errors. But when i go to see the data in the access get :

12124TR51236
12RE531TY
1,00099093+E8
3,8880929039+E10

these are produtcs ID so i have to have the number as it is and not in these format...any ideas?? thanks

 

[VBslammer]

Change the cell format back to 'General' and it should import fine.

VBSlammer

 

[RichD]

Formatting of Excel cells to text is not as straightforward as you may think. This is from Excel Help:

Format existing numbers as text

Microsoft Excel stores numbers as numeric data even if you later apply the Text format to the cells that contain the numbers. If you want Microsoft Excel to interpret numbers such as part numbers as text, first apply the Text format to empty cells. Then type the numbers. If you've already entered the numbers, you can change the numbers to text data.

Format Existing Numbers as Text
1Select the cells that contain the numbers you want to format as text.

2On the Format menu, click Cells, and then click the Number
tab.

3In the Category box, click Text, and then click OK.
4Click each cell, press F2, and then press ENTER to reenter the data.

HTH

Rich

 

[mattdrinks]

Rich is right but there is a way to do without having to enter each cell.

Insert a new column and put the formula =TEXT(A1,"0&quot in the first row (I am assuming your data is in column A and you don't have any headers). Then fill down this formula to the lenght of your list. Then copy the entire row and use Paste Specail (Values) to paste it over its self.

This should leave you with a new column where all the values are stored as text.

Sometimes I find it eaiser to save the file as CSV and then import it to Access this makes sure the all the data is saved as text,
But be careful because Excel has a strange way of saving CSV files were it only puts " around fields that contain , or " this confuses Access that expects either all the fields to enclosed in " or none.

Hope some of that helps

Matt

 

[acunha76]

Thank you all.. i've done it..