Solved

Access 2003 Export to .csv Switching dot for pound

Posted on 2008-06-19
39
1,401 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 119

Expert Comment

by:Rey Obrero
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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now