Cannot change Data Type when importing excel file

[angelesta]

When importing an excel file, I have a column called "Account Number" that is formatted in Excel as "text". Whenever I import or link this table in Access, it automatically changes the Data Type to "Number" but I need it to be "Text".

However, when importing, the Data Type field is greyed out and it won't let me change it to text!
I've tried saving it in .csv format, but that doesn't work either (the columns don't line up, unfortunately).
Any idea how to default this to text so whenever I link or import this file it will ALWAYS be text and not a "Long Integer".
Thanks Amigos!

 

[jasmith4]

The CSV-file import is best, because you can change the data type and much more. But you should import a CSV as Delimited, not Fixed-length, because it's delimited by commas. And don't forget to tell it that strings have quotes around them -- Excel saves strings that way.

As far as keeping your settings, just before clicking the Finish button on the Import Text Wizard, click Advanced to enter the wonderful world of specifications. All your choices should be there, so now you can click the Save As button to save them. Next time you have to import the CSV (with perhaps more data), click the Advanced button first, then click the Specs button to pick the specs you've just saved.

 

[angelesta]

Thank you but the csv. file format won't work - the information isn't clean (unfortunately) and there are too many lines to fix.
ANy other ideas?
Is there a way to make an append query to fix this? I've tried this:

Right("0000000" & [New_Account_Code],7)

But it will still save as an Number!!!! I need a 7 digit text field and it won't work.
Frustrating!

 

[jasmith4]

Let me assume your account numbers are in column A, and that cell A1 has the heading "Account Number". Insert a blank column in B, enter a new heading in B1, and in B2 enter the formula =TEXT(A2,"0000000"). Fill that down, save the spreadsheet, and when you're importing in Access, don't use column A, use column B instead.

 

[alansidman]

Another approach is to insert a blank row in your excel spreadsheet just below the header row. In cell A2, put a space and save your file. Now import the file and it should import column A as text.

Alan

 

[Norie]

Are you losing data when it's imported as a number?

Is it 'just' the leading zeroes?

If it is you could try something like this.

AccNoText:Format(AccountNo, "0000000")

That should add the missing zeroes and will return Text.

 

[angelesta]

Awesome, awesome! Thank you everyone, all great suggestions and I appreciate it!
That is exactly what happens...even thought the Excel file is in text and the table is set to text, because the Account Number has some accounts with 6 numbers, it loses that leading zero which is a huge problem later on in some of my queries.
Every time I refresh my Master Account table it keeps defaulting back to Number!
Thanks everyone for your help, I might put all these ideas in place to ensure this stops happening