Leading Zero in a CSV File
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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))
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.
* 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.
ASKER
Just posting so that it's not judged an abandoned question.
Still no (viable) solution,
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.
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.
ASKER
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"
But your initial sentence assumes/requires Excel be on the user PC
"the user will later double click to open it in Excel"
ASKER
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.
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.
ASKER
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'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-withi
5. Your requirements don't address the problem of Excel changing a leading zero text cell to a numeric cell automatically.
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-withi
5. Your requirements don't address the problem of Excel changing a leading zero text cell to a numeric cell automatically.
ASKER
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.
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.
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.
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"
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.
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"
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.
2. output your data directly to an Excel file using and Excel ActiveX object.