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

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

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

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

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

Open and Save CSV File
 

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

Regards
kaiserlich