Importing data from Excel
- Thread starter Ginny2222
- Start date
Colin@Toyota
What's an Access?
- Local time
- Today, 14:17
- Joined
- May 2, 2006
- Messages
- 203
Someone may correct me on this, or offer a better way, but you might have an easier time formatting the data in excel and then importing it. For example use CTRL + H (Find & Replace), or the AutoFilter feature to change all the data to the single character you require, and then import to Access.
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 11:17
- Joined
- Dec 21, 2005
- Messages
- 1,570
Or, import the table to access as a temporary table. Create an empty table with the correct field names and data type definitions. Run an append query based on your imported table to populate the blank one. However, instead of importing the particular column (YourField) whole, use the expression =Left([YourField],1) in the query grid instead.
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 11:17
- Joined
- Dec 21, 2005
- Messages
- 1,570
Is there a reason neither option is suitable for your situation? Just seems like you're giving up without much of a fight...
You could also just import the whole table and run an update query (using the same expression as above) on that column to reduce it down to one character (have to re-import whole table if something goes wrong that way, though).
You could also just import the whole table and run an update query (using the same expression as above) on that column to reduce it down to one character (have to re-import whole table if something goes wrong that way, though).
Hi Craig, I prepare the data in Excel so I will stick with this option. I was hoping that I could take this step out of my process. I have 7 report outputs from another system, these end up in 4 tables in my Access Database, but the fields don't correspond in all of the reports, hence needing to truncate the fields. I am building a macro in Excel to do the cleanup prior to upload to Access. I'll look later at the second option when I have time to try it out.
Thanks.
Ginny
Thanks.
Ginny
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 11:17
- Joined
- Dec 21, 2005
- Messages
- 1,570
Fair enough ....good luck with it 
OLUWAGBENGA ABRAHAM
New member
- Local time
- Today, 11:17
- Joined
- Nov 16, 2007
- Messages
- 1
Fair enough ....good luck with it
PLEASE I WANT TO UNDERSTAND YOUR PROCESS
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 11:17
- Joined
- Dec 21, 2005
- Messages
- 1,570
My 'process'?
Well, usually it involves reading a problem or question that someone poses on the forum, and thinking about it. If I don't understand something, or something additional needs to be explained to answer the question, I usually post back a question to get the required information. By convention, I usually use the english language. I also try to use a mix of upper case and lower case letters to indicate that I am not shouting.
Once I have the necessary information, I try to think of a way to answer the question or solve the problem and post this as an answer to the person who posted the question. If they are able to solve the problem using my suggestion, or decide to do something different (as in this case) I like to wish them well because, well, it's a nice to thing to do. I guess. And who knows, maybe they'll be able, and more willing, to help me in the future if I'm nice to them. And maybe one of the genuine gurus on the site will notice that I'm trying to help others so will be more likely to help me when I get stuck.
Very occasionally I might be slightly facetious in a response when someone values my time so little that they ask a short, but completely 'open' question that would require me to spend forever writing a novel-length post to cover all the possible questions that they might be asking.
That's rare though.
Good luck with your quest for understanding
