Open and Save CSV File

[kaiserlich]

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

 

[Tom Ogilvy]

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.aspx?scid=kb;en-us;291296
Procedure to export a text file with both comma and quote delimiters in Excel

 

[kaiserlich]

Hi RJK

Yes you are right, it opens without leading 0 when using "Workbook.Open". I
tried to use the Opentext method but i always get an error with it. "Type
mismatch" - only if i remove my "Set xlBook = " part it works. But after this
i would need to make all the checking...

Now i need to do the rest - may be you know the next good trick!

I do hope so ;-)

Thanks and Regards
kaiserlich

 

[kaiserlich]

Hi Tom

1. the leading 0 is still missing, the code doesn't solve this problem
2. with the code for the "" i got the problem of "Unparsable Record"
because some Data is not exported correctly:
Example: ,"PLACARD,"AUDIO"",
Correct would be: ,"PLACARD,""AUDIO""",

Hope you see my problem. But thank you anyway for your help!

Regards
kaiserlich

 

[kaiserlich]

I don't need help anymore. I did it with Access and not with Excel!

Regards
kaiserlich