Solved

Access 2003 Export to .csv Switching dot for pound

Posted on 2008-06-19
39
1,445 Views
Last Modified: 2013-11-27
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
Comment
Question by:larspanky
  • 18
  • 17
39 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21826966
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
 

Author Comment

by:larspanky
ID: 21827050
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21827064
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:larspanky
ID: 21827107
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
 

Author Comment

by:larspanky
ID: 21827126
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21827208
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
 

Author Comment

by:larspanky
ID: 21827248
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21827286
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
 

Author Comment

by:larspanky
ID: 21827748
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21827783
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
 

Author Comment

by:larspanky
ID: 21827790
It exports to excel without errors.  It still swaps # for . when you export to excel.  No wizard
0
 

Author Comment

by:larspanky
ID: 21827798
No doubt I am not even sure where to begin with those files.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21827811
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21827828
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
 

Author Comment

by:larspanky
ID: 21828012
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
 

Author Comment

by:larspanky
ID: 21828043
I now I cant save specifications anymore, Im reverting to backup.  
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828049
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828102
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
 

Author Comment

by:larspanky
ID: 21828118
It accepted the spec, but the symbols are still reversed.  
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828126
Hang on, I'll try and do it here with a temp table
0
 

Author Comment

by:larspanky
ID: 21828160
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828171
SELECT * FROM [qry_RateCode_calc] WHERE Instr(1,[Rate Code],"NM") =0
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828193
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828203
SELECT * FROM [qry_RateCode_calc] WHERE Instr(1,[Rate Code],"NM") =0
0
 

Author Comment

by:larspanky
ID: 21828251
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828259
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
 

Author Comment

by:larspanky
ID: 21828261
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
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 250 total points
ID: 21828268
Is NM the exact field value?
0
 

Author Comment

by:larspanky
ID: 21828298
Yes
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828302
So what does

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

give you?

Kelvin
0
 

Author Comment

by:larspanky
ID: 21828322
The Same Two errors
0
 

Author Comment

by:larspanky
ID: 21828363
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828377
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21828447
Oh, you'll also need to set a reference to Excel.

In VBA Tools>References and select Microsoft Excel


Kelvin
0
 

Author Comment

by:larspanky
ID: 21836158
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21839152
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question