By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,422 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,422 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Sep 14 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Just to clarify:

I'm really hoping that there is a setting or registry entry I can
change on my machine which will fix the problem for me, rathaer than
making any modification to the database code itself. 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

On Sep 14, 9:34 am, ni...@bouteyres.com wrote:
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

Sep 14 '07 #2

P: n/a
<ni***@bouteyres.comwrote in message
news:11*********************@y42g2000hsy.googlegro ups.com...
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
No idea how to fix the problem, but if noone can help, you could
post-process the file with VBA (I'm aware you don't want to do this, but if
it turns out to be your only option...). Paste the following code
immediately after
your TransferText line:

''' BEGIN CODE '''
Dim buffer As String

Open filepath for Binary Access Read As 1
buffer = Space(LOF(1))
Get #1,, buffer
Close 1
buffer = Replace(buffer, ";", ",")
Kill filepath
Open filepath for Binary Access Write As 1
Put #1,, buffer
Close 1
''' END CODE '''

What that code does is to grab the whole file from disk into a string
variable (buffer), then replace all instances of semicolon with comma. The
original export file is then deleted and the file is re-created by writing
out the buffer.

Sep 14 '07 #3

P: n/a
Try Start>Control Panel>Regional and Language Options and look at Customize
settings. There may be something there,
<ni***@bouteyres.comwrote in message
news:11*********************@50g2000hsm.googlegrou ps.com...
Just to clarify:

I'm really hoping that there is a setting or registry entry I can
change on my machine which will fix the problem for me, rathaer than
making any modification to the database code itself. 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

On Sep 14, 9:34 am, ni...@bouteyres.com wrote:
>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


Sep 14 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.