excel data import - data type issues

[garethl]

i have written a vba routine to import excel files into a temporary table which is then queried to send the data to various parts of a database

the only thing is that where access sees numeric data i.e. phone numbers it tries to make the data type of that field a number type and i do not wnat these to be number type i want them to be text

i found that you can get round this by creating a 'dummy row' in the first line of the excel file which i then delete from the resulting database table using a query

is there however a way to force the import to treat all data as text so i don't need to use this fudge?

 

[boblarson]

1. Create the table once by importing
2. Change the datatype of that column to text
3. Then, use a delete query to delete the data next time instead of deleting the table
4. Use an append query to fill the table instead of a make table query

 

[garethl]

course! thanks

i guess you can't actually force data types on it then

 

[unmarkedhelicopter]

Problem is you will have lost any leading zero's.
You could try storing all telephone numbers in international format, but I haven't tried this so it may cause you as many problems with a "+" at the front.

 

[garethl]

storing as text removes leading zero's?

actually it looks to me as though where there was a leading zero the entire number has been omitted

all the fields stay as text but i still get a table of import errors which all say they are a type conversion failiure

i guess that these are the ones with leading zero's

i guess my dummy row fix was the best way to go

 

[unmarkedhelicopter]

No, if you import the data as a number 01234567 will be stored as 1234567 as Excel does not see the point of a leading zero on a number field. You then convert it to text and you get "1234567" instead of "01234567"
Yeah, sounds best of a bad lot.

 

[boblarson]

But, if you do what I suggested and create the table FIRST with the datatype of TEXT you want, and THEN append to it (I'm not meaning to convert your current values but to re-run the import) then it should go in there just fine.

 

[garethl]

bob

thats what i'm doing i leave the table there and then import into it

the data types do all stay as text as you suggest but i still get a table of import errors

 

[boblarson]

bob

thats what i'm doing i leave the table there and then import into it

the data types do all stay as text as you suggest but i still get a table of import errors

Have you gone in and looked to see exactly which column and row of data are getting entered into the import error table and then matched it up to the spreadsheet to see what is actually in the cell?

 

[garethl]

yeah they are phone numbers, 2 fields, area code and extension

i don't know how to interpret those row numbers though because the data in the table is not stored in any specific order right?

it looks like it may be where someone has acidentally prepended a number with "-" though

 

[boblarson]

i don't know how to interpret those row numbers though because the data in the table is not stored in any specific order right?

The table isn't but the row numbers should match up with the rows in Excel.

 

[garethl]

ok now i can interpret the row numbers i can see whats going on

its exactly as unmarkedhelicopter says it has issues with fields which contain a leading zero

it also has issues with any cells which have funny little green tags on the corners which i think signifiy a number stored as text (in an otherwise number format column)

i get the rest of the record but not the number fields they are just lost so it seems maybe the dummy row really is the best of a bad lot

thanks for all your help though people