excel field length = 255 causing problem in access 1

[jfdabiri]

hi,
i'm trying to import an excel sheet into a table in access using vbs. however, the excel sheet has column names, and valid data, but the field length is always set to 255 for text fields. the table has predefined field lengths and it's causing error "field is too smaall ....". is there anyway to get around this, and truncate unneeded text?
thanks.

 

[PHV]

You may import the excel sheet into a temporary table and then use an append query with trimmed/truncated fields.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jfdabiri]

thanks phv,
i tried that. that's the problem i'm running into. i import the excel file into a temporary table. the temp table has field lengths set to 255. then when i append this temp tbl to the permanent master, it complains about long field lengths. i suppose it's a problem with access. but when i use the manual append using access user interface, there's no prblem. but i need to do this in vbs. and it gives me an error. as i described.

 

[jfdabiri]

phv,
how would i trim, truncate fields when i append a table to another one?

 

[PHV]

A starting point (with a field length = 15):
INSERT INTO theTable (Field1)
SELECT Left(Trim(Field1), 15) FROM tempTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jfdabiri]

thanks, phv.
i found out what the problem was. because this script used to work. all the text fields from excel get imported to the temp file with a length of 255. however, the main table with pre-defined field lengths, truncates the nulls after the data. but, if there's data beyond the defined length, then the script throws error. it just happened that the field middle initial had full middle name instead of just one character.
thanks for your suggestion.