Exporting to CSV File - getting semi-colon instead of comma as delimiter.

[nigel]

Hi,

I'm using VBA to export data from a table direct to a CSV file

DoCmd.TransferText acExportDelim, , "ExportTable", filePath

this produced a file with COMMA separated values, until...my computer
got fried and the repair shop re-installed Windows XP in French (I
live in France). Now when I run the export the values are separated by
semi-colons. I've used the Regional Settings in the Control Panel and
selected the English (UK) settings and this did make a difference to
how Excel saves a spreadsheet in CSV format: semi-colon with French
settings and comma with English, as you'd expect, but it hasn't made
any difference to how Access exports my data.

I'd welcome any ideas any of you might have. Many thanks in advance,

Nigel

 

[S.Clark]

Manually export the data and create a specification, then use that in your
TransferText call.

 

[Bonnie]

Hi Nigel,

I do this all the time and would be happy to help with my vba code if you
have any problem getting it worked out. I'll check your response.

Bonnie

 

[nigel]

Hello again,

Thanks for that advice - if I can't find any other solutions I'll try
that, but I was really hoping that there is a setting or registry
entry I can change on my machine which will fix the problem for me.
The reason: the database is installed at numerous client sites where
it works fine as it is - they're all 'English' computers - and I'd
prefer not to change the database just to solve a local problem on my
machine.

Thanks,

Nigel

 

[Bonnie]

Hi Nigel,

What Steve suggested does not require changing the database. There is a
feature built into Access that generates an export specification. You have
to actually do an export to generate the export specification. Then from
your VBA code you refer to the specification that outlines how you want your
data exported (i.e., delimited, sdf, etc.). If you have it on multiple
machines you would probably have to give them an update of your front end
with the specification in it which I'm supposing you would have to do with
the VBA changes anyway.

Do a manual export and you will see what we mean. Then look at the syntax
for the VBA command TRANSFERTEXT.

Bonnie

 

[nigel]

Hi Bonnie,

Thanks for coming back to me.

I tried what you suggested and sure enough the data is now exported
with comma separators as required.

BUT...as you mention this means a change to the VBA code AND that is
what I was trying to avoid.

Well now I've made the change on my version of the VBA I might as well
leave it in place and let it be rolled out in due course to my
clients.

What I don't understand is why changing the regional settings did not
fix the problem and I'd still like to know if there's a registry
setting that might!

Oh well, at least I learnt something - I've not used Export
Specifications before - so thank you for that!

Best wishes,

Nigel

 

[Bonnie]

Hi Nigel,

Glad it worked for you. There is not a regional setting because it's not a
regional issue. No matter where you live, etc., you may want to export in
any or all of the available data structures. I do all the time. Sorry but
the roll out option is the only way I know. :=)

Bonnie

 

[nigel]

Hi Bonnie,

I understand that you may want to export in a number of different
formats using different separators and that through the export
specifications Access provides that functionality. But the default
separator does seem to be a regional issue because on 'English'
computers the default is a comma and on my 'French' computer the
default is a semi-colon. I found other discussions regarding this
issue that pointed me to the regional settings and specifically the
setting for 'List Separator'. Unfortunately this didn't resolve my
problem. My regional settings are now English, the List Separator is
set to comma, but the default separator used by Access for exports is
still a semi-colon.

If the database was going to be installed in multiple sites some of
which would be non-English I'd accept that the Export Specification
was the correct solution and effectively it seems to be the only one!
But Access must be getting the default separator from somewhere.
Perhaps it depends on what your regional settings were at the time
that Access was installed...I've only just thought of that...oh well,
if I've ever some time I might try un-installing and re-installing
Access to see if that works. I wouldn't hold your breath though...

Thanks for the help,

Nigel

 

[Bonnie]

Oh, I see what you mean. Sorry I wasn't thinking of the default delimiter.