Question Leading Zero question

[lite4d]

I have a table that i am importing that has alphanumeric content and upon import from excel 2010 to access 2010 it drops the leading zeros. I have tried formatting the excel spreadsheet to every possible combination but it still drops them. What format am i missing? Here is the field names and data:

Material

002091234
LLPWE0001

I want to hold the formatting to 9 characters for all but I need the leading zeros on the ones that begin with them.

Thanks!

Chris

 

[namliam]

Your excel sheet probably has a formatting on the column to fill (missing) leading zero's

Your excel sheet simply doesnt have the leading zero's thus they wont be imported.

You can fix it by running an update query on your imported table.

 

[lite4d]

When I pull the data from our database to excel it has a general format and all of the cell are formatted the way I want them. When it is imported to access it drops the leading zeros and saya that the "LL" starting numbers are invalid and will not be imported. How can i get around this and keep the numbers as they are.

Thanks

Chris

 

[namliam]

Make sure that in the first few lines of the excel file is one line that has LL, can you try that?

Keep in mind a FORMAT is not the same as a VALUE...

What you see is the FORMAT, if you select the cell and look at the formula bar... that is the actual VALUE

 

[lite4d]

Didn't work. It won't import the "LL numbers. It says "Type Conversion Failure" for every record that has the "LL" numbers. The others write over the way they are formatted. I'm gonna try to build a new table with the same columns that the infor has and try an append to see if that works.

 

[gemma-the-husky]

Add a row 2 to the spreadsheet. In this column put the word "TEXT" or similar above the data.

This should make the "number" column import as text.

Then delete the surplus data row, after the import.

What happens is Access/Excel determine the data type based on the first few rows of data. If they are all numeric, then it treats the column as numeric