• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1529
  • Last Modified:

Access 2003 Export to .csv Switching dot for pound

Hello,

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

Thanks
0
larspanky
Asked:
larspanky
  • 18
  • 17
1 Solution
 
Kelvin SparksCommented:
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.
0
 
larspankyAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
do not use the { dot } as separator for your filename - use underscore or dash { - }
looks like the filename is being intepreted as hyperlink address
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
larspankyAuthor Commented:
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.  
0
 
larspankyAuthor Commented:
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.  
0
 
Kelvin SparksCommented:
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
0
 
larspankyAuthor Commented:
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.
0
 
Kelvin SparksCommented:
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.
0
 
larspankyAuthor Commented:
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?
0
 
Kelvin SparksCommented:
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
0
 
larspankyAuthor Commented:
It exports to excel without errors.  It still swaps # for . when you export to excel.  No wizard
0
 
larspankyAuthor Commented:
No doubt I am not even sure where to begin with those files.
0
 
Kelvin SparksCommented:
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
0
 
Kelvin SparksCommented:
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
0
 
larspankyAuthor Commented:
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?
0
 
larspankyAuthor Commented:
I now I cant save specifications anymore, Im reverting to backup.  
0
 
Kelvin SparksCommented:
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.
0
 
Kelvin SparksCommented:
OK

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.
0
 
larspankyAuthor Commented:
It accepted the spec, but the symbols are still reversed.  
0
 
Kelvin SparksCommented:
Hang on, I'll try and do it here with a temp table
0
 
larspankyAuthor Commented:
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?
0
 
Kelvin SparksCommented:
SELECT * FROM [qry_RateCode_calc] WHERE Instr(1,[Rate Code],"NM") =0
0
 
Kelvin SparksCommented:
OK

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
0
 
Kelvin SparksCommented:
SELECT * FROM [qry_RateCode_calc] WHERE Instr(1,[Rate Code],"NM") =0
0
 
larspankyAuthor Commented:
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.
0
 
Kelvin SparksCommented:
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"
    xlsheet.Select
    xlsheet.Activate
    
    .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
    Next
    ''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
    xlw.Save
    rs.Close
    Set rs = Nothing
    Set xlsheet = Nothing
End With
 
Set cmd = Nothing
Set xlw = Nothing
Set xlRange = Nothing
xl.Quit
Set xl = Nothing

Open in new window

0
 
larspankyAuthor Commented:
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.
0
 
Kelvin SparksCommented:
Is NM the exact field value?
0
 
larspankyAuthor Commented:
Yes
0
 
Kelvin SparksCommented:
So what does

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

give you?

Kelvin
0
 
larspankyAuthor Commented:
The Same Two errors
0
 
larspankyAuthor Commented:
Ok

NOT "NM"

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?

Thanks
0
 
Kelvin SparksCommented:
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
0
 
Kelvin SparksCommented:
Oh, you'll also need to set a reference to Excel.

In VBA Tools>References and select Microsoft Excel


Kelvin
0
 
larspankyAuthor Commented:
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.  




0
 
Kelvin SparksCommented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 18
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now