• 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?
0
Serena2345
Asked:
Serena2345
4 Solutions
 
borkiCommented:
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.
0
 
Nick67Commented:
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
0
 
OP_ZaharinCommented:
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:

John,25,Male
"Alan,Tham",28,Male
Sarah,30,Female

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


wizard

table
0
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

 
OP_ZaharinCommented:
- typo <Text delimiter to ""> is meant as <Text Qualifier to "">
0
 
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.
0
 
Jeffrey CoachmanCommented:
Can you post a sample of the original, *unaltered* text file?
0
 
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
0

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