Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CSV file
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CSV file
http://support.microsoft.com/default...b;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. -- Regards, Tom Ogilvy "LJi" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CSV file
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CSV file
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...b;en-us;291296 Procedure to export a text file with both comma and quote delimiters in Excel -- Regards, Tom Ogilvy "LJi" wrote in message oups.com... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CSV file
It works!!!
Thank you, Tom. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File:1 and File:2 -- Double Files when Opening One File | Excel Discussion (Misc queries) | |||
I saved file A over file B. Can I get file B back? | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
I SAVED A FILE OVER ANOTHER A FILE IN EXCEL. THE OLD FILE WAS AN . | Excel Discussion (Misc queries) | |||
i received a file that reads powerpoint document file file exten. | Excel Discussion (Misc queries) |