Remove commas from table, remove " from CSV output

[BenH]

Hello,

I have searched but cannot find.

I have been creating an Access DB to process data contained in 20 exteranal spreadsheets. The format of the sheets is the same.

I have gotten everything into one table OK using a loop and TransferSpreadsheet.

I've done some processing on it using action queries and , and exported it as one single merged CSV file OK using TransferText. This text file will be imported into a seperate Ingres database.

I have three issues;

1) The exported CSV contained "" around the values, which I do not want, I cannot find any way using this method to stop this?

I can replace in the text editor to remove it, but is there a way of stopping it happen?

2) Sometimes the XLS files contain commas in the middle of one of the fields. This does not present a problem until it's exported to CSV, and when I import to the other DB it thinks there's ane excess field.

Is there an easy way or removing any commas from all the table's string fields before export? All the fields in the table containing the data are strings.

Thanks for any advise.

 

[DJkarl]

The reason you have " around the values is probably because Access detected commas in the value fields. I would caution against removing the commas unless you are explicitly directed to do so, having the " (aka a text qualifier) tells a CSV importer to import everything between the double quotes as one field, regardless whether there are additional commas found.

To remove the " from exporting setup an import/export spec and select text qualifier none.

To remove the commas from the values in your table run an update query on the field(s) using the replace function (Replace(FieldName,Chr$(44)," ")).

 

[BenH]

Thanks, but I'm having problems with the Export Spec.

The TransferText was working fine without an Export Spec before.

I've created the spec, and named it RentAccExport - my Transfer Text is below;

DoCmd.TransferText acExportDelim, "RentAccExport", "qryRentAcc", "c:\output.txt", False

This errors with runtime error 3011. The Microsoft Jet Engine could not find "output.txt" - obviously there's no file there as I want to create a new one

Strangely, if I remove the quotes "" around the export specification name, it works, but I get the " again the output...

Hmmm...

 

[DJkarl]

Your issue sounds similar to something over at utteracces, the solution there was to delete the export/import specs and re-create them from scratch.

http://www.utteraccess.com/forum/TransferText-acExportDeli-t689214.html