code: ExportDelimitedText, Number Format Codes
---
Hi Ngoni,
was waiting to see if someone offered a simpler way ... here is some
code you can use to get the file the way you want it...
this code creates a TAB delimited file unless otherwise specified ...
'~~~~~~~~~~~~~~~
Sub ExportDelimitedText( _
pRecordsetName As String, _
pFilename As String, _
Optional pBooIncludeFieldnames As Boolean, _
Optional pBooDelimitFields As Boolean, _
Optional pFieldDeli As String)
'written by Crystal
'strive4peace2006 at yahoo dot com
'NEEDS reference to Microsoft DAO Library
'PARAMETERS
'pRecordsetName --> name of query or table; or SQL statement
'pFilename -- name of file to create
'pBooIncludeFieldnames -- TRUE if you want fieldnames at top
' default is False
'pBooDelimitFields -- TRUE for delimiter, FALSE for none
'pFieldDeli -- string to use as delimiter
' TAB will be used if nothing specified
'BASIC USEAGE
' ExportDelimitedText "QueryName", "c:\path\filename.csv"
'set up error handler
On Error GoTo Proc_Err
Dim mPathAndFile As String, mFileNumber As Integer
Dim r As dao.Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
booDelimitFields = Nz(pBooDelimitFields, False)
booIncludeFieldnames = Nz(pBooIncludeFieldnames, False)
'make the delimiter a TAB character unless specified
If Nz(pFieldDeli, "") = "" Then
mFieldDeli = Chr(9)
Else
mFieldDeli = pFieldDeli
End If
'if there is no path specfied, put file in current directory
If InStr(pFilename, "\") = 0 Then
mPathAndFile = CurrentProject.Path
Else
mPathAndFile = ""
End If
mPathAndFile = mPathAndFile & "\" & pFilename
'if there is no extension specified, add TXT
If InStr(pFilename, ".") = 0 Then
mPathAndFile = mPathAndFile & ".txt"
End If
'get a handle
mFileNumber = FreeFile
'close file handle if it is open
'ignore any error from trying to close it if it is not
On Error Resume Next
Close #mFileNumber
On Error GoTo Proc_Err
'delete the output file if already exists
If Dir(mPathAndFile) <> "" Then
Kill mPathAndFile
DoEvents
End If
'open file for output
Open mPathAndFile For Output As #mFileNumber
'open the recordset
Set r = CurrentDb.OpenRecordset(pRecordsetName)
'write fieldnames if specified
If booIncludeFieldnames Then
mOutputString = ""
For mFieldNum = 0 To r.Fields.Count - 1
If booDelimitFields Then
mOutputString = mOutputString & """" _
& r.Fields(mFieldNum) & """" & mFieldDeli
Else
mOutputString = mOutputString _
& r.Fields(mFieldNum).Name & mFieldDeli
End If
Next mFieldNum
'remove last delimiter
if pBooDelimitFields then
mOutputString = Left( _
mOutputString _
, Len(mOutputString) - Len(mFieldDeli) _
)
end if
'write a line to the file
Print #mFileNumber, mOutputString
End If
'loop through all records
Do While Not r.EOF()
'tell OS (Operating System) to pay attention to things
DoEvents
mOutputString = ""
For mFieldNum = 0 To r.Fields.Count - 1
If booDelimitFields Then
Select Case r.Fields(mFieldNum).Type
'string
Case 10, 12
mOutputString = mOutputString & """" _
& r.Fields(mFieldNum) & """" & mFieldDeli
'date
Case 8
mOutputString = mOutputString & "#" _
& r.Fields(mFieldNum) & "#" & mFieldDeli
'number
Case Else
mOutputString = mOutputString _
& r.Fields(mFieldNum) & mFieldDeli
End Select
Else
mOutputString = mOutputString _
& r.Fields(mFieldNum) _
& mFieldDeli
End If
Next mFieldNum
'remove last TAB
if booDelimitFields then _
mOutputString = Left( _
mOutputString _
, Len(mOutputString) - Len(mFieldDeli)_
)
'write a line to the file
Print #mFileNumber, mOutputString
'move to next record
r.MoveNext
Loop
MsgBox "Done Creating " & mPathAndFile, , "Done"
Proc_Exit:
on error resume next
'close the file
Close #mFileNumber
'close the recordset
r.Close
'release object variables
Set r = Nothing
Exit Sub
'ERROR HANDLER
Proc_Err:
MsgBox Err.Description _
, , "ERROR " & Err.Number _
& " ExportDelimitedText"
'press F8 to step through code and correct problem
'comment next line after debugged
Stop : Resume
Resume Proc_Exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~
to get the number as you like, in the query, you can use
field --> ColumnName: Format([numberfield],"formatcode")
WHERE
formatcode:
"0" -->
to show 0 or whole number, no commas
"#,##0"
to show 0 or whole number, with commas
"#" -->
to show whole number or nothing if 0, no commas
"#,###"
to show whole number or nothing, with commas
~~~
on format code for numbers -- there are 4 parts
1. format for positive numbers
2. format for negative numbers
3. format for 0 (zero)
4. format for null
ie:
Format --> #,##0;[red]-#,##0;0;0
numbers will have no decimal places
negative numbers will be Red
zeros and null values will show up as a 0
Format --> #,###.##;[red]-#,###.##;"";""
numbers will show up to 2 decimal places
negative numbers will be Red
zeros and null values will not be displayed
for more help on Format, press the F1 key in the Format property on the
property sheet
****
FORMAT function
You can use the FORMAT function, which results in a string.
format([numberfield],"#,##0") -->
show commas between thousands, no decimal places, always show zero
format([numberfield],"#,###") -->
show commas between thousands, no decimal places, don't show anything if
the number is zero
format([numberfield],"#,##0.00") -->
show commas between thousands, 2 decimal places, always show zero
for more help on Format:
1. press CTRL-G to view the debug window
2. type FORMAT and then press the F1 key
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Hi Ngoni,
what is the data type for VendorID?
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Ngoni said:
This is a pretty cool query. I used it to export my data to CSV so I
can use it as an upload. Here's my query:
SELECT VENDOR.[VendorID], ENTITY.[Building Reference] INTO
[Text;HDR=Yes;DATABASE=C:\Temp].MyData4.csv
FROM VENDOR, ENTITY;
My query result is over 380,000 lines. I have a problem with decimals
and quotaion marks in the CSV file that I want to get rid of. My
VendorID field displays as a number with 2 decimals, e.g. 274458.00. I
need this to read 274458. My Building Reference field displays as
"RQS0057" (with the inverted commas). I would prefer it read as
RQS0057, (without the inverted commas). This is the format needed to
export this CVS file into another system. I could use the Find and
Replace when I open the file in Notepad but it takes forever (over
380,000 lines).
How can modify my SQL to get the correct format that I need?
Thanks.
'69 Camaro said:
You can create a query that exports directly to a CSV file that Excel
can open, complete with the correct number of decimals showing.
Here's an example:
SELECT ID, Stuff, Format(Nums, "##.###") AS Data INTO
[Text;HDR=Yes;DATABASE=C:\Test].MyData.csv
FROM tblMyTable;
In this example, the file will be created as C:\Test\MyData.csv and
the numbers will be formatted with three decimals showing.
