Leading Zero in a CSV File

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

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.

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))
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

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.

Just posting so that it's not judged an abandoned question.

Still no (viable) solution,

@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.

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).

@Clif

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

For those customers who have Excel, which some do and some don't.

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.

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.

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.

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.

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.