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

Issue Exporting Access 2003 table to CSV format using VBA

Hi,  

I've got a file I need to get to the government ASAP and need some help on exporting through VBA.  I have the code to export using DoCmd.TransferText acExportDelim, etc. but the export is ending up like:
        7262            7262      8.18201E+13
       7262            7262      8.18201E+13
        7262            7262      8.18201E+13

but is supposed to be like:                  
      0007262            0007262      81820129012012

I have tried making an export Schema and setting the text qualifyer to {none} as was recommended by some posts online, but it still doesn't work.    All of these fields are Text fields in Access but are coming over as numbers.  Any pointers on how to fix the schema on this or some other way to remedy?   The number starting with the 8 do need to be a number, just not formatted as scientific..

Thanks,
Joan
0
jrogersok
Asked:
jrogersok
  • 8
  • 4
  • 3
  • +1
2 Solutions
 
als315Commented:
Seems you have numeric fields in your table (query). Try to convert them to text before export and export this query:
SELECT Format([Field1],"0000000") AS Expr1, Format([Field2],"0000000") AS Expr2, Format([Field3],"0000000000000") AS Expr3
FROM Table1;

Open in new window

0
 
jrogersokAuthor Commented:
Thanks for the post.

I tried formatting Field1 and Field2 to no avail.  It's in the exporting process where it doesn't work.  The data looks fine in the Access Tables (set as a text field), just doesn't export correctly.  

I will have to use another method for Field3 as the number of characters will vary depending on the month.  This field joins two dates together where I have removed the /'s and some dates will start with 1 character and some with 2 (the entity that needs the data does not want leading zeros. Again, this is a text field that is being converted to a number somehow when exporting.

jr
0
 
jerryb30Commented:
Are you exporting from a query?
For field3, try 'FIELD4: Format([DATE1],"mdyyyy") & Format([DATE2],"mdyyyy")'
I cannot recreate issue in my version. If you are using a query, try to use cstr(fieldName), and see what happens.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
als315Commented:
Try this sample (Export code is in Button on Form1)
DBExportcsv.accdb
0
 
jrogersokAuthor Commented:
I'm exporting from a table.  Remember, too, I'm using Access 2003.  Perhaps the newer versions are better at this.

I'll try creating a query, though, and see if it's any different.
0
 
jerryb30Commented:
Try this.
db2.mdb
0
 
jrogersokAuthor Commented:
Doesn't work -- the query looks fine, but the export once again posts the information the same.  It's got to be something with the schema file which is the translator to CSV.

Thanks for keeping trying; I'll do the same.

I have a few experts here at work that aren't here today.  I'll check with them as well as I'm new to this job and they have been doing this type of thing for many years.  Perhaps they have a tip.
0
 
jerryb30Commented:
query3a. You'll have to set up source and destination folders, export spec.
0
 
als315Commented:
You can always write csv from VBA
DBExportcsv.mdb
0
 
aikimarkCommented:
open the file in Notepad.  Do you see the scientific notation?

In many cases, Excel does the numeric value display transformation automatically and the data is in the correct format in the CSV file.
0
 
jrogersokAuthor Commented:
@ aikimark - Yes in Notepad I see the scientific notation-bummer

Anyone - tips on what to do in the Export spec?  I see that we have a lot of processes that use them but I can seem to get it to work.  How would I change the spec on those particular fields?

Thanks,
Joan
0
 
aikimarkCommented:
Had you opened the exported file in Excel before you opened it in Notepad?
0
 
aikimarkCommented:
What "schema file"?  CSV handling doesn't use schema files.
0
 
jrogersokAuthor Commented:
OK.  I think I figured it out.  I regenerated the export using a query instead of a table and then opened it in Notepad like @aikimark said.  It looks fine but when exporting from a table it does not -- wierd but I got it working.

A few more tests and I'll award points.
0
 
jrogersokAuthor Commented:
Looks like outputting to a query first worked.  I'm waiting to see if the file was accepted by the establishment that needed it to see if there's further work.

@als315 - Testing your method

Will be back...
0
 
jrogersokAuthor Commented:
@als315 -- Thanks for the code.  Tweeked it a bit to put quotes and commas around the fields, however, the Payperiod date is repeating on the last row. (I added more rows to the table as we'll have several in the output). What would be causing that?

"0007262","0007262","81820129012012"
"0007622","0007622","81820129012012"
820129012012
0
 
als315Commented:
Strange problem. I've also got this result, but only once.
For comma delimited csv you can try this sample. It is not so complicate as fixed width output.
DBExportcsv.mdb
0
 
jrogersokAuthor Commented:
Splitting points as both solutions worked.  Thanks experts!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now