CSV file

[LJi]

Hi List,

Issue:
User creates output files by running query from PeopleSoft. somehow,
the output files have extra empty columns at the end. these extra
columns cause issue when they try to upload the files into another
system.

Solution for now:
User has to open the CSV file using notepad or wordpad, then deleted
the extra spaces at the end. When user views the file from notepad,
each field has quotes around. It looks like:
"Num", "Code", "Name",...
Then they can upload these files into another system without any
problems.

What I am trying to do:
Since there are many output files out there. User wants to have a macro
to handle the "Delete Extr Spaces" thing. I tried to delete the spaces
from the CSV files directly, but after I deleted the extra columns, if
view the file from notepad, all the quotes gone, and they can't be
uploaded into the next system.

Does anyone have any similiar experience? Why the quotes gone? are
there any ways that can let me delete the spaces without loose the
quotes?

Thanks for any help.

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;151262
Working with Sequential Access Files

http://www.applecore99.com/gen/gen029.asp

You could use low level fileio to strip out the extra columns. Then you
could preserve the double quotes.

 

[LJi]

Hi Tom,

Thank you for your quick reply.

Yes, that is what i was trying to do. I use fileio to strip out the
extra columns, but the file will be txt file instead of CSV. The next
step for user to upload the files into another system, they have to be
in CSV format. If i save the txt file into a CSV format from macro,
then reveiw it from notepad, the quotes gone. the only way i can keep
the quotes is open the txt file, manually save it as a CSV format. But
i can't find a way in macro to do so which user doesn't like it. they
want the macro handle everything until the end.

Any thoughts?

 

[Tom Ogilvy]

No, you are not using low level file io as I suggested if you are opening it
in excel and experiencing the problem you describe. CSV is just a format
and it sounds like you file is already a CSV file even if it has a text
extension. You just want to take the extra commas off the end. Then you
should read the articles I provided and you will be able to do it and then
rename the file with the .CSV extension.


If you want to open it with excel as you currently are, then you can restore
the double quotes by looking at the code in this article.

http://support.microsoft.com/default.aspx?scid=kb;en-us;291296
Procedure to export a text file with both comma and quote delimiters in
Excel

 

[LJi]

It works!!!

Thank you, Tom.