Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open and Save CSV File
Hi all,
I'm trying to open a CSV-File with double-quotation mark and comma. Afterwards i need to make some checkings in this file and save the correct data again. The source data looks like this: "019292","Placards","1" The result when saving is this: 19292,Placards,1 Leading zero is missing and the double quotation mark is missing. My code is this: Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(xlsdatei, xlMSDOS, 1, xlDelimited, xlTextQualifierDoubleQuote, False, False, True, False, False, False) xlBook.SaveAs FileName:="C:\test.csv", FileFormat:=xlCSV xlBook.Close SaveChanges:=False Please help. Thank you! Regards kaiserlich |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open and Save CSV File
that is because Excel doesn't save a CSV file in the format. You would have
to write a macro to write it back out in that format. Better to just edit it in wordpad perhaps. Anyway, here is a link to some code: 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 "kaiserlich" wrote: Hi all, I'm trying to open a CSV-File with double-quotation mark and comma. Afterwards i need to make some checkings in this file and save the correct data again. The source data looks like this: "019292","Placards","1" The result when saving is this: 19292,Placards,1 Leading zero is missing and the double quotation mark is missing. My code is this: Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(xlsdatei, xlMSDOS, 1, xlDelimited, xlTextQualifierDoubleQuote, False, False, True, False, False, False) xlBook.SaveAs FileName:="C:\test.csv", FileFormat:=xlCSV xlBook.Close SaveChanges:=False Please help. Thank you! Regards kaiserlich |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open and Save CSV File
I realized too late: CSV and Excel don't like each other.
The problem is that i've done already a big check with different data sources (access db etc.), so i can't change it manually in the file, and to make this checking differently would take me hours to change. How can i solve the problem with the leading zeros? This program for "" helps me partially because i do have lot of different CSV Files for that i need to run it automatically. And these files will be importet into e.g. Access DB - where i already found some errors when trying to import these files. It looks like the program does set some "" wrong (the source data contains really everything in it, in some cases " only at the end etc.) - but i couldn't check it in details. Is there another solution? Hopefully kaiserlich "Tom Ogilvy" schrieb: that is because Excel doesn't save a CSV file in the format. You would have to write a macro to write it back out in that format. Better to just edit it in wordpad perhaps. Anyway, here is a link to some code: 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 "kaiserlich" wrote: Hi all, I'm trying to open a CSV-File with double-quotation mark and comma. Afterwards i need to make some checkings in this file and save the correct data again. The source data looks like this: "019292","Placards","1" The result when saving is this: 19292,Placards,1 Leading zero is missing and the double quotation mark is missing. My code is this: Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(xlsdatei, xlMSDOS, 1, xlDelimited, xlTextQualifierDoubleQuote, False, False, True, False, False, False) xlBook.SaveAs FileName:="C:\test.csv", FileFormat:=xlCSV xlBook.Close SaveChanges:=False Please help. Thank you! Regards kaiserlich |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open and Save CSV File
The solution is right there at your finger tips. Write some code or modify
that code to do exactly what you want. -- Regards, Tom Ogilvy "kaiserlich" wrote in message ... I realized too late: CSV and Excel don't like each other. The problem is that i've done already a big check with different data sources (access db etc.), so i can't change it manually in the file, and to make this checking differently would take me hours to change. How can i solve the problem with the leading zeros? This program for "" helps me partially because i do have lot of different CSV Files for that i need to run it automatically. And these files will be importet into e.g. Access DB - where i already found some errors when trying to import these files. It looks like the program does set some "" wrong (the source data contains really everything in it, in some cases " only at the end etc.) - but i couldn't check it in details. Is there another solution? Hopefully kaiserlich "Tom Ogilvy" schrieb: that is because Excel doesn't save a CSV file in the format. You would have to write a macro to write it back out in that format. Better to just edit it in wordpad perhaps. Anyway, here is a link to some code: 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 "kaiserlich" wrote: Hi all, I'm trying to open a CSV-File with double-quotation mark and comma. Afterwards i need to make some checkings in this file and save the correct data again. The source data looks like this: "019292","Placards","1" The result when saving is this: 19292,Placards,1 Leading zero is missing and the double quotation mark is missing. My code is this: Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(xlsdatei, xlMSDOS, 1, xlDelimited, xlTextQualifierDoubleQuote, False, False, True, False, False, False) xlBook.SaveAs FileName:="C:\test.csv", FileFormat:=xlCSV xlBook.Close SaveChanges:=False Please help. Thank you! Regards kaiserlich |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open and Save CSV File
Could Excel be thinking this column is a Number Field when you open it.
I experienced this issue a while ago working with Amazon Order files. I used the Workbooks.Opentext method, and then set ALL the FIELDS (columns) to text on the import. It never dropped a 0 in front of a number after that. I've found that opening CSV files automatically(ie opening them through the OPEN FILES, or dbl clicking on them) creates some unwanted side effects, I discovered that when I went through the EXTERNAL DATA method, and forced all the columns (or atleast the ones I thought Excel would convert to a number) to text I had a much better result. You can look in VBA help for the OpenText method to get a full grip on how to force all fields to TEXT. HTH RJK "kaiserlich" wrote in message ... Hi all, I'm trying to open a CSV-File with double-quotation mark and comma. Afterwards i need to make some checkings in this file and save the correct data again. The source data looks like this: "019292","Placards","1" The result when saving is this: 19292,Placards,1 Leading zero is missing and the double quotation mark is missing. My code is this: Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(xlsdatei, xlMSDOS, 1, xlDelimited, xlTextQualifierDoubleQuote, False, False, True, False, False, False) xlBook.SaveAs FileName:="C:\test.csv", FileFormat:=xlCSV xlBook.Close SaveChanges:=False Please help. Thank you! Regards kaiserlich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Macro to Open File, Delete Contents, Save New File | Excel Discussion (Misc queries) | |||
Can't open a file and save under a different name | Excel Discussion (Misc queries) | |||
Office 2003 - "autocomplete" in file | open or file | save no longer works | Excel Discussion (Misc queries) | |||
open & save old xls file | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming |