2

I receive a csv file periodically from a database download and I cannot change the way this csv file is produced. I need to convert it to an Excel file to pass it on to an external partner. There are two fields causing trouble in the csv file when I import into Excel: a permit number which is actually a character string, and a location description which can contain commas and other punctuation.

If I right click on the csv file and select Open With Excel, the location description is imported correctly but some permit numbers are converted to scientific notation.

Alternatively, if I use the text import wizard in Excel, I can import the permit numbers as text, but the location description ends up being split across multiple cells even when I set the text qualifier to ".

Here is a simplified example of the data as it appears in the csv file:

id, "Permit Number", Species, Longitude, "Location Details"
1, 2F66-16, DE, -120.0001, "near a road, in woods (FR16)"
2, 678E-15, DE, -120.0002, "near milepost 65, north side of road"

Is there a solution that will allow me to import both fields correctly? This question is very similar but has no answers: excel text importing: wizard vs opening a file within Explorer Thanks for any help.

Update: I think the Location Description is being split into multiple cells because of line breaks in the csv. I'm not sure how these are being generated or how to remove them or deal with them, but when I open the file in Excel, the line breaks are still there (although the entire location description is in one cell). Unfortunately I can't post the full example data because it is sensitive data not easily converted to dummy data. Any thoughts on how to remove line breaks from a csv file?

Improve this question
5
  • What version of Excel? How are you "importing"? Commented Mar 8, 2017 at 21:27
  • I'm using Excel 2010 and importing by either right-clicking on the csv and selecting Open with Excel or by using the text import wizard in Excel. Commented Mar 8, 2017 at 21:36
  • By using the text import wizard in Excel, I assume you are setting up a connection from the Data tab. Problem with this file is that the separator is comma + space. Excel does not handle two character separators well. But so long as all of the fields with space-separated strings are enclosed in quotes, you can use space for the separator; and then strip off the commas in post-processing. That will help with dealing with empty fields. For your other problems, we'd need to see some data that can reproduce the problem. What you have supplied does not. Commented Mar 8, 2017 at 21:42
  • Given the update to OP that says the problem may be due to line breaks in the csv, the only option I have found to work in such cases is to use PowerQuery. That allows you to use the csv as a data source, define field types to ensure that Permit Number is not mis-interpretted as a number, and also respects multi-line text feilds. Commented Mar 9, 2017 at 3:25
  • BTW, if you are not familiar with PowerQuery, it is a Microsoft extension to Excel (free with the current versions of Office) and will allow you to do what I described completely within the Excel user interface. Commented Mar 9, 2017 at 3:27

3 Answers 3

Reset to default
3

For whatever reason, Excel doesn't seem to like spaces between the comma and the quote. If you do a global find and replace on your input data (change comma-space-quote to comma-quote) -- and then parse your data, it will honor the text qualifiers as you need.

id, "Permit Number", Species, Longitude, "Location Details" 1, 2F66-16, DE, -120.0001, "near a road, in woods (FR16)" 2, 678E-15, DE, -120.0002, "near milepost 65, north side of road"

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You are on the right track using the text import wizard within Excel, that at least gives you more control.

I don't know why the text qualifier isn't working, that's exactly what it is designed to do.

Are you specifying that final column as TEXT instead of leaving it as GENERAL?

Do the sample items in the wizard show correctly?

Are you positive they are " characters, and something similar (like smart quotes)?

It's a pain, but if nothing else works, you can always combine the columns back together at the end:

  1. Insert a column before your broken up details
  2. Add a formula like =E2 & "," & E3 (or longer if it splits into more than two)
  3. Copy the combined column, then paste values (over the same cells)
  4. Delete the now extraneous columns
Improve this answer

2 Comments

I tried specifying the location description as text but it's still being split into multiple cells. The wizard does not show the sample data correctly - it shows with the same mistakes that appear in the resulting excel spreadsheet. I'm not positive that the text qualifiers are not smart quotes or something else, but when I view the csv file in Notepad they appear to be simple double quotes. Thanks for the post-processing solution - I'll use it if I have to but would prefer to be able to solve it in import.
@LM6 If the wizard shows it wrong, then the data isn't right. It shows the first X rows, and uses that to guess how the rest of the rows will look. As in Scott's answer, your simplified example absolutely should work, so there is something else going on in your actual data.
0

Was able to do it with the text importer:

enter image description here

enter image description here

enter image description here

Improve this answer

1 Comment

I guess my example data was too simple. I also have fields that are missing values so I can't select 'Treat consecutive delimiters as one' because then successive field values are misplaced. But this does seem to work for the records that are not missing values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.