Access 2007 Import problem

Posted on 2011-04-19
Last Modified: 2012-05-11
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?
Question by:Serena2345
    LVL 9

    Accepted Solution

    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.
    LVL 26

    Assisted Solution

    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
    LVL 23

    Assisted Solution

    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.


    LVL 23

    Assisted Solution

    - typo <Text delimiter to ""> is meant as <Text Qualifier to "">

    Author Comment

    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.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Can you post a sample of the original, *unaltered* text file?

    Author Closing Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now