CSV file with a comma in some text fields how to import

[etaf]

Access version 2010 and excel version 2010

Hi

I have a CSV file with all full post codes in UK and some other demographic info

the 3rd field is a tet field with a city name - unfortunately on some of the records they have included a comma
so

Bristol, city of
instead of
Bristol city of

for example

and so my data splits on this comma and then all the other fields do not match up

because the import wizard is splitting the data on the comma at the first screen, I cant skip that field only just import the data fields I need

any suggestions on how to fix

the file is too large for excel

and i thought of using note pad and just selecting the first half of the data file - pasting into excel and then sorting on one of the columns at i can then group the incorrect fields and then
delete shift left on those entries = laborious, and also its taking hours to scroll down the text file

I hoped to be able to select - find say post code ME and then that would highlight the select = but that does not work

any suggestions to save me a few hours tomorrow welcome

thanks

 

[Joe4]

Where/how is this CSV being created initially?
The standard protocol of a CSV file is that any entry that has commas in it (that are not delimiters but actual data) should be enclosed with Text Qualifiers, usually double-quotes.
Then when importing into most programs, there is the option of telling it what to use as Text Qualiifers (Access has this option).
This is how Access (or any other program) can differentiate commas that are part of the data from commas that are delimiters. Otherwise, there is no way for the program to know what the difference is.

So for example, let's say you have a simple file of full names and ages, but some of the full names had commas in it, here is how it might look with the Text Qualifiers.

Name,Age
John Doe,17
"Bill Smith, Jr",25
Dana Jones,34
"Tom Davis, III",9

If your data does not already have these Text Qualifiers in it, I would recommend going back to the source where the files are being created and request that they do this. If you are not sure whether or not they are already included, open the CSV in NotePad or WordPad (NOT in Excel), and see if you can find any Text Qualifiers.

 

[etaf]

thanks

yes, it does have the "" around the field - so I must be importing incorrectly some how


I saw the


"bristol,
City of"
in to cells - when I copied a sample into excel and then also say it imported into one cell


so its years since I have used access


I just used the external > text> told it was delimited > do i need to tell it about the "," fields ?
where do I tell it to use the text qualifiers ?


the csv has been supplied from an external source

 

[etaf]

just tested on a small dummy file - I see the option now dah!! thanks - saved me a few hours tomorrow

 

[Joe4]

Cool! Glad you got it all worked out now!

 

[alansidman]

Cross posted: [SOLVED] CSV file with a comma in some text fields how to import

Please read this: Excelguru Help Site - A message to forum cross posters