Open and Save CSV File

[Guest]

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

 

[Guest]

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

 

[Guest]

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]

The solution is right there at your finger tips. Write some code or modify
that code to do exactly what you want.

 

[Newsgroup]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

Regards
kaiserlich