Access 2003 Export to .csv Switching dot for pound

larspanky used Ask the Experts™

I am using this command

Private Sub Export_Click()
DoCmd.TransferText acExportDelim, , "ZZqry_BatchExport", "C:\ADP\PCPW\ADPDATA\EPI.LANDMARK.AA.csv", True
End Sub

And it is exporting my query to a CSV file with no problem save one.  The process is trading the places of . and # dot and pound.  In both one of the fields of the sheet and the filename.  the field comes out "File . " and the filename is EPI#LANDMARK#AA.csv  

I would love to fix this to keep users from manually changing the . and # back

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Have you identified a text qualifier in the export specification. Normally I leave set to none, but when strange characters are used in data I tend to add them in to try and prevent these "strange" happenings.


I am having a hard time saving an export specification.  I select text, no wizard just a choice of encoding.  I do not have the option to create a deliminated file from exporting manually.  I am not sure where to look next.
Top Expert 2016

do not use the { dot } as separator for your filename - use underscore or dash { - }
looks like the filename is being intepreted as hyperlink address
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


Underscores export correctly but unfortunately that does not help me.  One of the Fields is [File #] and it is coming through [File . ] Also I need the dots in the file name.  The naming convention was determined by ADP and they are required to import.  


I read a brief message about this might be caused by a mismatch between the field names of the data tables and the header of the export file.  But I have not seen much on it.  I don't know if that helps.  
In regard to export specs, I take it from the posting that you are using a Microsoft project rather than a database (adp instead of mdb). Not that this affects things too much.

You can still (if not eliminated by custom menus) goto File>Export while the table or view is selected (but not open). Scroll the file type at the bottom of the dialog box to find text, enter the xport name with .csv after it and click next. The Advanced button will lead you to the export specification dialog - in the saved specs select the export spec you are using and play with the field qualifier etc... Save the spac as its origibnal name and overwite and try that


An odd thing is there is no advanced button.  I have seen screen shots of what your talking about and it is not an option.  I am using a database, .mdb

I also get an error "Too few parameters.  Expected 1." when I select export with the query highlighted.  

But no advanced dialog, I only get a dialog giving 4 options for encoding.  None of them are deliminated.
Your query obviously is driven off a parameterized form. The export command attempts to open this query. Just have the form open in the ackground with the fields providing the parameters filled to avoid this.

I suspect your install of Access was the "short" version. If you have the CD, go rerun it and ensure you use Full Install or Custom install with Run All from my PC selected for Access and towards the bottom of the list the is advanced or additional features (I can't quite remember what). Some wizards are only installed from that additional list.


I ran the entire install with all the settings applied and no change.  Still no wizard, I tried exporting from another NEW Db and it did, but no wizard there either.  Is it possible to make a specifications file by hand?
Technically, yes. They use two system tables: MSysIMEXSpecs is the spec itself

MSysIMEXColumns holds the data for the columns of data being exported.

You may need some guidance as to the values to stick in here if not obvious


It exports to excel without errors.  It still swaps # for . when you export to excel.  No wizard


No doubt I am not even sure where to begin with those files.
As a thought, if you already have an export spec, it'll be in the table MSysIMEXSpecs. You should be able to see it by reading the specname column. Find the textdelim column and add one double quote into the row for your spec. That should amend it.

Then retry
Looking at your export code there isn't one. Table may not exist until the first one is created!! If it does, let me know and I'll give you the column settings


The wizard comes up when exporting a table.  I found the table you mentioned, and I pointed the export button command to the spec.  the DoCmd export gets hung up on '|'.  

"Run-time error'2465'"

" Microsoft Office Access can't find the field '|' referred to in your expression."

I am guessing there is something in the spec sheet that is throwing off the export.  Probably the Encoding.   How do you edit that table?  Is there any reference to the meaning of all the fields?


I now I cant save specifications anymore, Im reverting to backup.  
The field relate to the advance button you'll get on the wizard.

Once you can call the wizard, select the spec you want and edit it through the interface (you'll see a one to one match of fields there with the form).

If I were you, I'd use the query to make a table of the table (just for now) then use the wizard on that for export to create the spec (if you are exporting memo fields, you need to put data into a table & export from the table or memo data is truncated at 255 characters anyway - so I always use the query as a make table query (set warnings off first) then run the query & export the resultant table).

But back the export, once you have atable with the same fields as the query, use the wizard to make a export spec, save it and call that in the transfer text line. Try with and without the Field qualifier and see if it makes a difference.

I didn't know that the wizard didn't appear for queries. You learnb something every day in this business.

With your new"resored copy follw these instructions

Set the form parameters for the query

Open the query in desin mode and temorarily chage to make table & give a temp name, then run query.

Change query back to select.

On tables tab select (but don't open) table

Click File>Export, choose text and give a temp file name toa handy location

Click Next

Wizard will have started, if you want column names in csv, click next

Click Advanced

Try setting field Qualifier to "

Check date formats (if applicable)

You field should be listed at the bottom, check each of these and in particular the one for the problem field - make sure it is text

Save spec with new name and note the name

Clcik OK and follow wizard to export. Check the file you created - OK?

If not OK, repeat the export process and call back the spec at the advanced stage (Saved spec) and edit & resave replacing the previous version.

Once it worked manually, then include it in your code (spec name enclosed in quotes")

Your | error would be the spec you made was based on a table and the field names would not have matched the query.


It accepted the spec, but the symbols are still reversed.  
Hang on, I'll try and do it here with a temp table


I really hate to cross threads, but I have one last stumbling block,

I want to pull a few records out of the query that contain data "NM"

I tried this in the criteria

SELECT * FROM [qry_RateCode_calc] WHERE [Rate Code] IS NOT "NM"

Can you help please?
SELECT * FROM [qry_RateCode_calc] WHERE Instr(1,[Rate Code],"NM") =0

I have played around with the export specs & cannot force the changes you want & suspect that windows/something is getting in the way.

Alternative is to run an instance of excel & populate via VBA. I can try this and send code if you want to look at this option. Until I try I don't know if it wil solve it
SELECT * FROM [qry_RateCode_calc] WHERE Instr(1,[Rate Code],"NM") =0


SELECT * FROM [qry_RateCode_calc] WHERE Instr(1,[Rate Code],"NM"=0)

I get this error.
The syntax of the subquery in this expression is incorrect.

Check the subquery's syntax and enclose the subquery in parentheses

Im not sure what the problem is.
OK The code snippet attached has done the job.

You'll need to edit it for your paths etc, but it does keep the "." in file & sheet names and the # in the column names (also boilds the columns names in the worksheet
Function ExportData()
Dim xl As New Excel.Application
Dim xlw As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim strwb As String
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim iCols As Integer
Dim i As Integer
strwb = "C:\EPI.LANDMARK.AA.csv"
Set xl = New Excel.Application
xl.DisplayAlerts = False
Set xlw = xl.Workbooks.Add
xlw.SaveAs strwb
Set cmd = New Command
With cmd
    Set xlsheet = xlw.Worksheets("Sheet1")
    xlsheet.Name = "EPI.LANDMARK.AA"
    .ActiveConnection = Application.CurrentProject.Connection
    .CommandText = "SELECT * FROM tewmpExport"
    .Prepared = True
    Set rs = .Execute()
    ''Insert Column headings
    For iCols = 0 To rs.Fields.Count - 1
            xlsheet.Cells(2, iCols + 2).Value = rs.Fields(iCols).Name
    ''Bold the column headings
    xlsheet.Range(xlsheet.Cells(2, 2), xlsheet.Cells(2, rs.Fields.Count + 1)).Font.Bold = True
    ''Now the data
    If Not rs.EOF Then
        xlsheet.Cells(3, 2).CopyFromRecordset rs
    End If
    Set xlRange = xlsheet.Cells(2, 2).CurrentRegion
    Set rs = Nothing
    Set xlsheet = Nothing
End With
Set cmd = Nothing
Set xlw = Nothing
Set xlRange = Nothing
Set xl = Nothing

Open in new window


I enclosed it ((SELECT * FROM [qry_RateCode_calc] WHERE Instr(1,[Rate Code],"NM")=0) ) and got this

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause.  Revise the SELECT statement of the subquery to request only one field.
Is NM the exact field value?


So what does

SELECT * FROM [qry_RateCode_calc] WHERE [Rate Code] <> "NM"

give you?



The Same Two errors




Go Figure.  

I am not entirely sure what to do with that code.   Can I use it as an On Click Event?  What query does it pull from in your code?

Replace you transfertext line in your sub with my code

Change the export path with your (strwb)

In the Select * bit, use you query name

Rest should be OK

Call the export as you normally did
Oh, you'll also need to set a reference to Excel.

In VBA Tools>References and select Microsoft Excel



Ok I set the query, changed the path and enabled "Microsoft Excel 11.0 Object Library" in VB

This script creates a file of the correct name and location.  

I receive an error "'Run-time error '-2147217904 (80040e10)'"

"No Value given for one or more required parameters"

The Debug highlights this line.

Set rs = .Execute()  

I imagine that the parameter that is not given is

ZZqry_BatchExport.[Batch ID]  Criteria = [Forms]![Exportform]![Combo1]

Which is activated on the same form as the button I am using to execute your code.  

Excel remains active in the background locking the file.  I would guess that is because the script is not able to finish.  

Did you provide the parameter? Try hard coding the parameter into the query first as a test.

You should also add an error handler to quit exel if there is an error, or do it manually each time you run it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial