Importing csv file to access

[jeff.white]

I am trying to import a very large csv file into access. It all seems
to work fine, there is one problem however. It is a comma delimited
file and in some cases the name is entered as:

smith, jr, john

So, having th 'JR' in the data the first name is pushed to the next
column over (department name) and so on. Is there an easy way to
clean this up after it's been imported or during the import wizzard
portion? Any ideas/suggestions would be great!!

 

[Ken Snell \(MVP\)]

Highly unlikely that you'll find an "automatic" way to do this -- too many
permutations to try to figure out.

How in the world did the csv file get generated without text qualifiers when
the values contained embedded commas? Can the csv file be recreated the
correct way?

Otherwise, you could import the data as one long value for each record --
the entire text line into one field. Then you could run a program that
identifies records that have more commas in them than the expected number
based on the number of fields minus 1. Then you could manually edit those
records to put text qualifiers around the values that have the embedded
commas. Then you could run a query that parses the data into the permanent
table.

 

[????]

I am trying to import a very large csv file into access. It all seems
to work fine, there is one problem however. It is a comma delimited
file and in some cases the name is entered as:

smith, jr, john

So, having th 'JR' in the data the first name is pushed to the next
column over (department name) and so on. Is there an easy way to
clean this up after it's been imported or during the import wizzard
portion? Any ideas/suggestions would be great!!

 

[????]

I am trying to import a very large csv file into access. It all seems
to work fine, there is one problem however. It is a comma delimited
file and in some cases the name is entered as:

smith, jr, john

So, having th 'JR' in the data the first name is pushed to the next
column over (department name) and so on. Is there an easy way to
clean this up after it's been imported or during the import wizzard
portion? Any ideas/suggestions would be great!!

 

[Guest]

Jeff,
If you possibly can, go back to the program or person who exported your
..csv file and ask for quote-delimited fields, so your commas will be within
double quotes. Then Access can understand that the comma belongs to a given
field. The way it is now, the extra commas fool Access into thinking it's the
end of the previous field.
If you can't get the file rebuilt with quoted fields, or with fixed-length
fields, it will require, as Ken says, a fairly complex program to parse out
the extra commas...