Import excel format (1 Viewer)

[pekajo]

Hi,
I have an issue with the DoCmd.TransferSpreadsheet acImport command.
One of the import fields is a phone number which has all kinds of numbers such as 03 87698076, N/A, as well as mobile 6756434546 (majority). It appears it read the data as numeric and drops all data that is not numeric or has spaces.
Is there a way of importing and making this field Text so it does not drop this data.
I have tried formatting the excel but it does not work.
Thanks
Peter

 

[theDBguy]

Hi. Since you want the data as Text anyway, can you add some dummy text data on the first row/record?

 

[arnelgp]

edit the Excel file and specifically change the Format of the Column to Text.

 

[pbaldy]

I suspect you could also create an import spec that specified the data types.

 

[Galaxiom]

Unless the columns have a specific datatype defined the import looks at the values in the first few row and decides the datatype based on them.

Alternatively put a row with values that are definitely text at the top.

I don't think import specs are supported for Excel files.

 

[pbaldy]

I don't think import specs are supported for Excel files.

I think you're right. I know I use them for csv files, which are text files even though they'll open in Excel.

 

[Pat Hartman]

Since Import Specs are not allowed for Excel files, you have two choices and both require some Excel automation.
1. Open the Excel file and save as .csv. That will allow you to use an Import SPEC.
2. Open the Excel file and change the column format as arne suggested or insert a dummy row as row 2 with dummy data in the format you want to be assumed. This is less effective than arne's suggestion since as Galaxiom mentioned, Access and Excel are in cahoots and Access examines ~ 20-30 records and uses them to determine the data type. If all are numeric, the assumption will be numeric. If all are dates, the assumption will be a date. If there is a mix or all are text, the assumption will be text.

 

[CJ_London]

there is another way to force text. Whether it is of use to you depends on what you are doing with the data.

Ensure the range includes the header row but set the hasheading property to no. Columns will import with headings F1, F2 etc and the first row will contain the header information which you will need to ignore. All fields will consequently be text. But this is easily handled if you are using a query to then update your table/s

 

[pekajo]

Thanks all for your help.
I have fixed it at the source.

 

[theDBguy]

Thanks all for your help.
I have fixed it at the source.

Hi. Glad to hear you got it sorted out. Good luck with your project.