• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Access 2007 Import problem

I have a very simple .csv file that I tried to import. The only problem was in the name field there was a "," between the first and last name. I imported it any was and used the" as the text qualifer The preview of the import looked fine but when I finished the program broke the first and last name into two columns and the name of the columns were not on the proper data. Yes, I did go back and and take out the "," and it did work, but i must be doing something wrong. Any suggestions?
4 Solutions
If your import data contains commas and you ask it to split the data along the commas, it will do that. However Access should ignore commas that are inside a string, eg "Smith, John", which would be imported as a single string and not split. If your data has a mixture of of strings, some enclosed and some that are not, Access gets confused and will create the problem you got.

You must either clean-up your data beforehand, or import the data without a string qualifier and clean it up afterwards.

For more specific help, post some data samples.
No, you aren't doing anything wrong.
CSV stands for comma separated values
If it has a comma in it, it'll be split :)

Name data is funny.
Sometimes you want it separated--and it's not and you have to concatenate
or Vice Versa.

You may want to consider changing your tables.
Go with a FirstName and LastName column.

It is easy enough in queries to do calculated fields like FullName: [FirstName] & " " & [LastName]
You can also then do things like sort alphabetically by last name -- which is hard to do when name is a single column.

But, you aren't doing anything wrong
hi Senera,
- are using the Access wizard to do the import? i'm trying to simulate your data and import using .csv file. i am using the Access 2007 import wizard: from the menu External Data > Text File > then selecting the .csv file, then specify comma delimited format.

- in my csv file, the data look like below whereby for the 2nd record, the text delimiter wrapping the first, the comma and the lastname:


- referring to the attached image, i specify the Text delimiter to "" and comma as the delimiter. therefore it will display correctly. then finishing the wizard the data will be inserted as it is.

- i'm suggesting that it's the csv formatting that you might have to look into to identify the issue that you're facing.


Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

- typo <Text delimiter to ""> is meant as <Text Qualifier to "">
Serena2345Author Commented:
Thanks for the help but I have done everything that you all have suggested already. And yes I did clean up my data because I had to as I said in my original staement. I don't understand why the wizard would look fine and then the acutally datatbase be so wrong. it seems I did not have this problem before 2007.
Jeffrey CoachmanCommented:
Can you post a sample of the original, *unaltered* text file?
Serena2345Author Commented:
I am giving everyone points for trying to help me. I cannot post unaltered data because of its proprietary nature. If I go in there an change it to take out sensitve information then it is no longer "unaltered". Thanks for you time and concern

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now