Forgot password? | Forgot username? | Register

CSV file format and Excel 14 (Office 2010) 64-bit

CSV file format and Excel 14 (Office 2010) 64-bit

Hi

Once upon a time I could open CSV files (that are dumped down when running reports) in Excel for simple viewing and interrogating, and occasionally editing data to test the impact of data changes on Crystal reports.

Now when I do this and save the changed data (as CSV file) something in the format changes and Crystal can't see any data in the file.  Excel seems to be adding extra quote marks around the already quote-marked data (when viewing in notepad after saving changes in Excel).

The version of Excel I am running is 14.0.6129.5000 (64-bit), as came bundled with Office 2010.

Has anyone encountered this before? And better yet, have you found a solution? I have tried Mac CSV and MS DOS CSV, but no dice.

Note: if I open the file in Notepad, rather than Excel, I can edit data and save and it works fine. But notepad is not a very nice way of viewing this data.

Mark

Edited by: Mark Bradley - 05-Dec-13 12:34:29

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: CSV file format and Excel 14 (Office 2010) 64-bit

Hi Mark,
This seems to be an ongoing problem with opening .csv files in Excel.  If you make any modification, Excel will strip quotes out of your text even if you resave as a .csv.  I searched around on forums to see if anyone found a solution, and it seems that some people have created macros to export data out of Excel with the quotes (instead of "saving as" which will strip them).  There is even a description of how to create one on the Microsoft site: http://support.microsoft.com/kb/291296/en-us.

I tried that out myself, and with an addition of an If...Then statement to the line in which the quotes are added, I was able to have it NOT quote the data around the first row (column headings) and the first column (row numbers from the EMu export).  So the file looks more like it should, but the problem is that Crystal still will not recognize the data.

I know this doesn't solve your problem, but I thought that maybe it may help if your Visual basic skills are better than mine, or perhaps it will lead you on a path to a solution. 

Good luck!  And please post again if you find a solution :-)
Kara

*************
Kara M. Lewis
Collections Information System Administrator/Analyst
National Museum of the American Indian, Smithsonian Institution

Kara Lewis
CIS Administrator/Analyst
useravatar
Offline
39 Posts
Female  Website 
Administrator has disabled public posting. Please login or register in order to proceed.
There are 0 guests and 0 other users also viewing this topic

Board Info

Board Stats
 
Total Topics:
601
Total Polls:
0
Total Posts:
1362
User Info
 
Total Users:
827
Newest User:
Marcus Swann (Axiell Melbourne)
Members Online:
2
Guests Online:
152