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

[tigeravatar]

Wayne,

CSV files should have any field that includes a comma as part of the text surrounded by double-quotes, like "Bristol, city of"
If whatever you're using did not output it in that manner, then it did not create the CSV file correctly.

[etaf]

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 ?

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

[alansidman]

Cross posted at: http://www.mrexcel.com/forum/microso...ow-import.html


Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).