Maintaing Text Qualifiers on CSV file passing through Excel

[Joe4]

I am trying to create a "pass though editor" macro in Excel that our staff can use to edit certain CSV files. Basically, one of our software programs requires CSV files to be formatted a specific way. Among other things, every field must be surrounded by Text Qualifiers (double-quotes_.

The program that initially creates the CSV file formats all the fields exactly as they need to be, including putting text qualifiers around all fields. However, due to some data issues, sometimes our staff needs to manually change some of the data in the file. That is where this "pass through editor" macro I am creating comes in (sometimes the changes are too difficult to do in a text editor, and they need to see the data in columns).

The issue I have is this. In my macro that imports the CSV file into Excel, I can choose either to designate delimiters or not.
- If I designate the delimiters, then Excel drops all the demiliters when importing. So then in re-saving the file, only data that has commas in it are exported with delimiters (default Excel behavior).
- If I do not designate the delimiters upon import, then Excel treats my delimiters (double-quotes) as text and imports them. However, when re-saving the CSV file, it then triples up my delimiters (so wherever one double-quote appeared before, now three appear).

Obviously, neither of these two situations work, where I need to maintain a single set of delimiters around each and every field.

Does anyone know how I can accomplish this?

 

[shg]

How about ...

Use the second method to import. In the BeforeSave event, change the file access mode to read-only (if it isn't already from a prior save), and then write out the CSV file yourself, adding the quotes and commas?

 

[Greg Truby]

Joe,

Like shg, I'd probably end up just writing the output file using VBA.

Not sure I'll have the time to try my hand at the coding, but anyone that wants to help's gonna need:

• some sample data they can copy & paste into a test CSV file
• and possibly helpful, but not required
  • a possible edit and the expected output
  • an example of the output generated via each method shown above
  • pertinent code already being used (if any)

 

[Joe4]

To tell you the truth, I haven't done a lot of coding exporting each record directly from VBA before (once or twice a long time ago) - its just not something I have had to do much. Usually the "Save As" options in Excel was sufficient. We have been so busy at work with a big software conversion, that I just haven't had time to work on these phase 2 "accessory" programs yet. I was just hoping there might be a quick trick or setting (or some simple clever idea) that I could implement quickly. Otherwise, they'll just have to wait until I get more time to play around with it.

The concept is fairly simple - the text qualifiers on the incoming file should match those on the outgoing file, i.e.

"Joe","012345","04/05/2010","","ABC Corp, Inc"

My ultimate goal is to make this a universally dynamic program, meaning the number of fields could differ from file to file. But I already figured out a "brute force" method of doing that, i.e. if I set up the import array for the maximum number of fields possible, it will work for any file that has that many fields or less.

Not pretty, but just trying to get them something "quick and dirty" to use until I have the time to put something slick together.

 

[SydneyGeek]

I've used the FileSystem Object to process CSV files and clean up the formatting.
This one is for dealing with dates, but you should be able to replace """ with " in a similar fashion.

Denis

 

[Joe4]

Denis, that looks like it pertains to Access, right?
I am trying to figure out a way how to do this out of Excel.

I have a feeling that I will probably need to do what shg suggested. I just wanted to make sure that wasn't some simple/clever trick I was missing so I don't re-invent the wheel here.

 

[Joe4]

I think I found what I need. This link here: http://www.mcgimpsey.com/excel/textfiles.html, the one entitled: "CSV with quotation marks around each field".

 

[Joe4]

On second thought, my previous post might not work in this instance. That would work great if I wanted text qualifiers around ALL my fields every time. But actually, some files may have that, and others may not.

I guess what I really want is to keep/maintain whatever text qualifiers there are on the original CSV file and not drop or add any in the process of passing it through Excel (or change any of the formatting).

I suppose I could import everything as Text with no delimiters designated (so it will treat them as literal text). Then I could make my export script concatenate every field for each row on Sheet2 in column A, as-is, separated by a comma and export as a text file.

 

[KenCriss]

Joe - I have used parts of this code to export data from excel. This is overkill for what you need, but it is pretty handy.
http://www.xl-logic.com/modules.php?name=Downloads&d_op=viewdownload&cid=5&min=30&orderby=titleA&show=10
If link does not work, look for CSV Range Save tool in the VBA Code download section

 

[SydneyGeek]

Denis, that looks like it pertains to Access, right?
I am trying to figure out a way how to do this out of Excel.

I have a feeling that I will probably need to do what shg suggested. I just wanted to make sure that wasn't some simple/clever trick I was missing so I don't re-invent the wheel here.

Hi Joe, I used that technique with Access but the FSO bit would work out of Excel too. All it does is taking an existing csv and process it, line by line, pushing the new version out to a file name of your choosing.

Denis