Solved

Leading Zero in a CSV File

Posted on 2008-10-22
18
989 Views
Last Modified: 2012-05-05
My app creates a CSV file that the user will later double click to open it in Excel.  The CSV file contains, among other info, a couple of "numeric" text fields (zip code is one).  When the fields contain a leading zero, Excel seems to drop it.

A couple of caveats...

1. The users are, shall we say, less than tech-savvy so having them format the Excel columns every time the file is opened is out of the question.

2. The file will be opened by double clicking on it, so utilizing the Excel "text import" option is also out of the question.

3. One option I saw was to wrap the value in equals/quotes (like: ="<value>").  Excel retains this and will confuse the user if they attempted to edit the value.

Seems to me you used to be able to get around this by prepending an apostrophe, but that no longer seems to work, at least no in Excel 2003.

Any other valid options?

TIA
0
Comment
Question by:Clif
  • 9
  • 7
18 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 22780919
1. output your numeric text fields as quote-delimited.
2. output your data directly to an Excel file using and Excel ActiveX object.
0
 
LVL 10

Author Comment

by:Clif
ID: 22785324
1. Wrapping the numeric value in quotes doesn't appear to work.
CFI,0,"02604",20.000,G,1
CFI,0,"02604",9.890,G,2

2. I'm hoping to avoid using the Excel object, but the suggestion is duly noted
0
 
LVL 45

Accepted Solution

by:
aikimark earned 50 total points
ID: 22785525
@Clif

"...doesn't appear to work"

Very interesting...and surprising that Excel doesn't get this right. :-(

The solution appears to require prepending an equal sign as shown in the snippet.
CFI,0,="02604",20.000,G,1

CFI,0,="02604",9.890,G,2

Open in new window

0
 
LVL 10

Author Comment

by:Clif
ID: 22785573
The problem with that is that the equals sign and quotes are then part of the text in Excel.  As I said on item #3 of the original question, should the user attempt to edit the value, seeing the equals sign and quotes would confuse them.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 22786880
The snippet illustrates an old data entry trick to force numeric text to be treated as text instead of numbers.  Unfortunately, the apostrophe does show up in the cell contents.

You might replace Excel with your own application and use a spreadsheet object like the one from FarPoint.

You could create a different program to do the Excel importing.  It would need to be associated with the CSV file type.

You might create some generic workbook template that always runs an Open routine and changes the format of the third column to text.  But that will cause one of those macro virus warnings.

====================
The problem appears that the CSV importer has 'problems' or 'issues'.  :-(
The most reliable importing method is in the snippet.  I first rename the file to a non-CSV file type and then issue the .OpenText method.  This involves the least amount of Excel automation as I could generate.
CFI,0,'02604,20,G,1

CFI,0,'02604,9.89,G,2
 
 

=================================

filecopy "C:\Documents and Settings\AikiMARK\My Documents\test.csv","C:\Documents and Settings\AikiMARK\My Documents\test.csvImport"
 

Workbooks.OpenText FileName:="C:\Documents and Settings\AikiMARK\My Documents\test.csvImport", _

        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True, _

        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1))

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 22789431
Please note the following:
* Using the Workbooks.OpenText method, you no longer have to use quotes around the 02604 text column.
* If you immediately performed a .SaveAs method out to the .CSV file, the user is 'editing' the CSV file with Excel.  (use overwrite and no prompt options)
* If you need to keep a more tidy directory, you should Kill the .CSVImport file.
* If you want to streamline this process further, you might save the file as .CSVImport rather than .CSV
* You can have the same icon as Excel for your application, so the user knows to double click on the file icon, assuming you have associated the .CSVImport with your ExcelImporter utility.

0
 
LVL 10

Author Comment

by:Clif
ID: 22844286
Just posting so that it's not judged an abandoned question.
Still no (viable) solution,
0
 
LVL 45

Expert Comment

by:aikimark
ID: 22844841
@Clif

What about my suggestion that you write a small applet that invokes the TextImport worksheet function?  Let's say that your program is called CSVImport.exe.  During the installation of this program, you change the registry entry for CSV file association from MSExcel.exe to CSVImport.exe.

Thereafter, your less-than-bright users see what you want them to see in the Excel worksheet and they haven't had to change their habits or learn anything new.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 10

Author Comment

by:Clif
ID: 22866800
The "TextImport" applet you described would require Excel, If I could do that, I could just create the Excel file.  Unfortunately the design forbids the requirement of the user having Excel on their machine (See Caveat #2 in my original post).
0
 
LVL 45

Expert Comment

by:aikimark
ID: 22866963
@Clif

But your initial sentence assumes/requires Excel be on the user PC
"the user will later double click to open it in Excel"
0
 
LVL 10

Author Comment

by:Clif
ID: 22866999
For those customers who have Excel, which some do and some don't.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 22867246
What should your Excel-less customers see when they double-click on the .CSV file?

In general, you can distribute the importer utility along with your application.  During setup, you can detect whether Excel is installed on the PC and then associate the CSV file type with your imported utility.

If you have some naming standard with your output routine, you can change the importer to do its magic when the file matches the pattern or pass it directly to Excel/Notepad (whatever is defined at the time of installation) if it doesn't match the pattern.
0
 
LVL 10

Author Comment

by:Clif
ID: 22867361
It doesn't matter what Excel-less people see.
I'm sorry of I sound a little snarky, but I have specific requirements that must be met.  These requirements were posted in my initial question so it's not as if I'm changing them just to avoid grding the question.  The reason that these requirements exist is my own business, suffice it to say that they do exist and are unchangeable.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 22867494
I don't mind the snark as long as we are communicating.  Other EE experts may disagree.
1. The only way to format the CSV file to correctly import into Excel would be to prepend the columns with an apostrophe character.  It still works.  However, there are two problems:
  * the users will see the apostrophe in Excel and in any other application, such as Notepad.
  * if the user removes the leading apostrophe, Excel will strip the leading zeroes.
  * Even if the CSV is successfully imported into Excel with the leading zeroes and no prepended apostrophe, the cell may be automatically converted to numeric by Excel if the user edits the cell, whether that edit removes leading zeroes or not.

2. I'm working within your requirements.

3. The solution is to create an ExcelCSV importer applet that is installed, along with the main application, on your users' PCs.  The importer applet can intelligently (selectively) do the import into Excel or not.

"The reason that these requirements exist is my own business..."
4. I'm not challenging your requirements.  There *does* seem to be an inconsistency between your double-click-to-open-within-Excel action and your later statement that seems to drop the condition/requirement that Excel is installed on your users' PCs.

5. Your requirements don't address the problem of Excel changing a leading zero text cell to a numeric cell automatically.
0
 
LVL 10

Author Comment

by:Clif
ID: 22867704
1. There in lies the problem and the reason I asked the question.
2. No, you keep asking me to create an app that requires the existence of Excel.
3. I am not allowed to create an app or applet that requires the user have Excel installed.
4. The inconsistency is only if you refuse to consider the possibility that their will be more than one machine available.  In reality, those that do not have Excel locally will be emailing the CSV file to another department (who do not want a Text Import applet)
5.  Again, this is the problem.  At one point many years ago, an apostrophe prepended to a value forced Excel to format that value as text.  It no longer does this, I was wondering if Microsoft had changed it from an apostrophe to something else.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 22867978
5. I just verified that the leading apostrophe trick still works with Excel2003.  You can force data to be treated as text by starting the column data with an apostrophe.
Note: The user will see the apostrophe in the cell contents.

2 & 3 & 4. I'm not suggesting that creating and distributing an importing applet along with your application requires the presence of Excel.  

* * * IF, AND ONLY IF, the user has Excel would your applet be used * * *

If any of your users does NOT have Excel, they will not use your applet.  They won't even know it exists.

===============
6. It is *YOUR APPLICATION* that creates a CSV file.  If *some* users have a problem when they double-click on the file icon, my recommendation addresses that subset of your users.  I am not suggesting anything that would affect any non-Excel users.

7. I am *NOT* trying to change your requirements and constraints.  

8. You could create an applet that displays the candidate CSV files to the user and opens the selected file with the appropriate application (Notepad, Excel).  The user does not need to see the raw CSV file in an Explorer window, just the list your applet displays.

9. Unless you add more user-related constraints (installation, distribution, authority, etc.) I can only suggest that you have enough to solve the problem as you've described it.

10. The points aren't as important as solving problems.  However, it is incumbent upon you to adequately describe the problem and all the constraints.  I suspect that we are still having this discussion because you have not disclosed something that is very important.
0
 
LVL 3

Expert Comment

by:WRNewman
ID: 26523637
There are many tools that create csv files but the actual structure is often sloppy
To be sure of your file create it yourself

Open "myfile,csv" for out put as #1

The big problem in writing csv files is the separator itself and the quotes needed so if you want
1,"text",2.768,"more text" you can do it using ascii characters 44 is a comma 34 is double quotes
so
print #1, "1"+chr(44)+chr(34)+"text"+chr(34)+chr(44)+"2.768"+chr(44)+chr(34)+"more text"

will do the trick. It works because the data is embedded in the string by removing VB's own " quotes and inserts 1 into the output string, it then adds a comma from the chr(44) then the quotes needed by the CSV rules from the chr(34) then removes the quotes the text and inserts that followed by another set of quotes from the chr(34) etc

Using this method absolutely guarantees that the CSV format you want is what you get.
Sorry the answer is a bit late, have not been around here for a long time.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now