98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Converting Excel to CSV
Converting Excel to CSV
Resolved · Medium Priority · Version 2003
Wed 11 Mar 2009, 16:12Delegate Hilary said...
Hilary has attended:
Access Intermediate course
Access Advanced course
Access Introduction course
Converting Excel to CSV
I have an Excel file that I have to run through a data cleaning programme which only accepts files in CSV format and reads them through Excel. In the record ID field, I have some IDs that begine with NOV and are followed by four numbers, for example NOV1982. These are not dates but are in text format. When I save the file as a CSV (comma delimited) file and reopen it, these IDs are automatically converted into date format. Is there a way to avoid this? Thank you.
Thu 12 Mar 2009, 10:05Trainer Andrew said...
RE: Converting Excel to CSV
Hi Hilary
Thank you for your question. I have been able to recreate the situation you describe where the ID data is exported in text format but when Excel re-opens the CSV file it assumes these must be date values and formats them as such.
One way around this would be to create a blank spreadsheet and go to the Data menu and choose Import External Data. Change the file type to All files and open your CSV file.
The import wizard will prompt you to confirm the format of the data being imported. On the third step of the wizard you can specify that the column containing IDs should be treated as Text.
I hope this helps. Let us know how you get on and if you have any further questions.
Kind regards,
Andrew
Thu 12 Mar 2009, 11:03Delegate Hilary said...
RE: Converting Excel to CSV
Hi Andrew,
Thank you, that works for me to get the file back in Excel, however if I resave it as a CSV file it reverts back to the dates. I need to have an Excel CSV file (text format) that can be imported into this data processing programme. Do you think this is possible?
Thanks,
Hilary
Thu 12 Mar 2009, 11:20Trainer Andrew said...
RE: Converting Excel to CSV
Hi Hilary
Thanks for letting me know. I notice that Excel imports the data as text but then doesn't apply the text format to the column once the data is back in Excel. So it looks like after the import you will need to select the column containing the IDs, right click and choose Format Cells and set to Text again.
Does this help? If this is something you have to do a lot of (and the CSV file name is always the same) you may find it easier to record a macro to cut down the administration involved.
I hope this helps.
Kind regards,
Andrew
Thu 12 Mar 2009, 11:32Delegate Hilary said...
RE: Converting Excel to CSV
Hi Andrew,
I imported the CSV file again into Excel as text, then again highlited the cells and made them text and tried to save it as a new CSV file. When I opened the CSV file it again came out as dates.
I have to use these files often but there is not usually a case where the IDs look like dates so I have never had this problem before. I came up with a temporary solution of changing the NOV to XYZ and ran it through the programme, but when I got the output and tried to find/replace XYZ bac with NOV again, with the column in text format, it would be converted to dates again. The only way I could keep it as text is if I went in to each cell manually and replaced the XYZ with NOV. I don't understand why it is happening like this.
I am not very familiar with macros but do you think this would solve my problem?
Thank you,
Hilary
Thu 12 Mar 2009, 12:01Trainer Andrew said...
RE: Converting Excel to CSV
Hi Hilary
It looks like there is no way to switch off this behaviour in Excel.
You have another option though. When you enter a value in Excel such as the ID you can prefix it with a single apostrophe e.g.
'NOV14
The apostrophe tells Excel to not apply any formatting to that cell. It will always be treated as text. This may not be so helpful for you as you are exporting the data to another application. Excel doesn't display the apostrophe in the cell.
Without apostrophes the process we have described is the best I can suggest for working with your data in CSV format. That is format the column as text. Save as CSV. One thing you may want to do when you bring the CSV file back into Excel is rather than import into a blank workbook replace the .csv file extension with .txt. This triggers the import wizard so you can force Excel to bring the data in as text without having to import it into an existing file. Once it has arrived set the format the column for ID to text again and continue as before.
Macros can help automate processes that are repetitive.
Kind regards,
Andrew
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Creating Quick Column ChartsSelect the information you want to appear in the chart and press F11. This creates a new chart on a seperate worksheet. |
