export table to text file (1 Viewer)

[tristi]

I am trying to export a table to a text file from Access. The numeric fields have data with 5 decimal places; however, when I export I am only getting a text file with 2 decimal places. I checked properties of tables, and the numeric fields were set to "Double", Decimal places as "Auto". I changed decimal places to b "5" and tried to reexport, but am still getting the numbers cut off. I cannot find anything in the export criteria to allow me to choose number of decimal places.

How can I export table to text file which has more than 2 decimal places? I will appreciate your help.

 

[thouston]

I have come across this exact problem before! As far as I can see it is a "feature" of Access, and you can't solve it by setting table properties. However, I use 2 solutions, depending on the circumstances:

1. Create a query selecting all the records from your table. Format the troublesome column by using the format function (credit to Pat Hartman for showing me this one a year ago):

Select Format(Fieldname,"0000.00000") AS FormattedField

Save this query (export doesn't work on it otherwise) and export the query instead of the table.

One disadvantage of this approach is that your numeric field is then treated as text, so you then get quotes around the exported numbers, and if you use the option not to enclose text in quotes, then any actual text fields you export in the same query lose their quotes too...a problem for me at the time (but not necessarily for you), which brings me to method 2 -

2. The other (quicker, dirtier, bodge job) method that I use is to export first into Excel and from there to text. This leaves decimal places intact, but obviously it's not very elegant.

Hope this helps - glad to hear there's somebody else out there using "real" numbers with lots of decimals!

 

[RichMorrison]

You wrote
<<
I am trying to export a table to a text file from Access. The numeric fields have data with 5 decimal places;
>>

Then it isn't "text".

Create a new table for export. Make ALL fields text. Make append queries to populate the export table. In these queries you can use the Format$ function to get whatever look you want.

HTH,
RichM

 

[BobRoberts]

Additional table to text possibility

I had the same problem with the export and took RichMorrison's approach of creating a query based on the table to be exported and formatting each column as needed. This included qualifying the text columns with quotes.

The following function creates the desired query which can then be exported as a comma (or other delimiter) file without text qualifiers. The result leaves the numerics to be interpretted as numeric as the file is imported.

Bob

Public Function FormatForExport(tblOrQryName As String)
'Creates a query that may be exported to allow for real values with precision past two decimal places
'The query can be exported as a CSV with no text delimiters.
'The quotes have been added to the column of text defined in the query.
Dim db As Database
Dim tableIn As Recordset
Dim f As Field
Dim queryOut As QueryDef
Dim sqlCmd, betweenFields, qout As String

Set db = DBEngine(0)(0)
'Open the table or query to be exported
Set tblQryIn = db.OpenRecordset(tblOrQryName, dbOpenDynaset)

'Create an SQL string to create a single text column with the comma seperated values
'begin with SELECT
sqlCmd = "SELECT "
betweenFields = ""
With tblQryIn
For Each f In .Fields
'Add each field to the single column being selected
If f.Attributes = 34 Then 'Text field
sqlCmd = sqlCmd & betweenFields & " """""""" & [" & tblOrQryName & "].[" & f.Name & "] & """""""" "
Else 'Assume numeric and format
sqlCmd = sqlCmd & betweenFields & "Format([" & tblOrQryName & "].[" & f.Name & "])"
End If
sqlCmd = sqlCmd & " AS [" & f.Name & "] "
'add the seperator after the first field
betweenFields = ", "
Next
End With
'Add the column name and FROM clause
sqlCmd = sqlCmd & " FROM [" & tblOrQryName & "];"
'default the query name to the input name and " text"
qout = tblOrQryName & " text"
'create the query and it is ready to export
Set queryOut = db.CreateQueryDef(qout, sqlCmd)

End Function

 

[GB_Joe]

I know this is an old thread, but I've just been struggling with this myself and have remembered how I worked around it a few years back.

This sounds weird, but the decimal precision of the CSV export is determined by your Windows regional settings!

These instructions are based on Win 2000, so adjust for your version accordingly...

Go to Control Panel, Regional Options, Numbers tab. The default value for "No. of digits after decimal" is 2. I changed mine to 8, which is good enough for me - it'll let you choose anything up to 9.

Hope this helps someone!

 

[once]

GB Joe.... Brilliant!

This is so fundamentally important I'll just repeat what he said above.

Go to Control Panel->Date, Time, Lang & Regional Options->Change Format of Numbers

Then choose 'Customize' and set 'No. of digits after decimal'

Brilliant!

 

[GB_Joe]

Thanks!

 

[rexmorgan]

I am so glad I found this thread. I have been searching everywhere for an answer that made sense and was easy. Thanks a million.

 

[imorit]

GB_Joe,

I have written code to convert a qry to table and then export it to csv. I had to do two formatting - 1. For month and Year needed YMD and the second one for numbers with 4 decimal points. Everything worked great accept for the decimal points. I was going crazy until I saw this post - THANK YOU! everything is working perfect now.
Here is my code:
Private Sub cmdPrintSlit_Click()
On Error GoTo Err_cmdPrintSlit_Click




'Create an exported Slit text file (csv) and reformat it
Dim FilePath As String
Dim Ext As Variant
Dim DLString As String
Dim RsPath As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

DoCmd.SetWarnings False

db.Execute ("DELETE * FROM tblSlitReportFromQry;") 'Delete the data so I can append the data with right formatting

DoCmd.OpenQuery "qrySlitReport", acViewNormal, acEdit 'Run the query that creates the Slit Report. The query will fill the Slit status based on the order. query creates the tblSlitReportFromQry Take this out to run the qry directly to excel

DoCmd.SetWarnings True


'Get the file path from the default table
Set RsPath = db.OpenRecordset("SELECT * FROM tblTPGTEXDefaults WHERE ProgramVariable ='Slit_Confirmation'")
FilePath = RsPath!Path

DLString = "Slit_"

'Export the new table as a csv file
DoCmd.TransferText acExportDelim, "SpecTblSlitReportFromQry", "tblSlitReportFromQry", FilePath & "\" & DLString & Format(Date, "YYYYMMDD_") & Me.cmbWorkOrderId & ".csv", -1

'Eliminate the time stamp in date format
Dim lOpenFile As Long
Dim sFileText As String
Dim sFileName As String

sFileName = FilePath & "\" & DLString & Format(Date, "YYYYMMDD_") & Me.cmbWorkOrderId & ".csv"

'open the file and read it into a variable
lOpenFile = FreeFile
Open sFileName For Input As lOpenFile
sFileText = Input(LOF(lOpenFile), lOpenFile)
Close lOpenFile

'Replace Time stamp with nothing
sFileText = Replace(sFileText, " 00000", "")

'write it back to the file
lOpenFile = FreeFile
Open sFileName For Output As lOpenFile
Print #lOpenFile, sFileText
Close lOpenFile

MsgBox "The report *** " & sFileName & " *** was saved." & vbOKOnly

Exit_cmdPrintSlit_Click:
Exit Sub

Err_cmdPrintSlit_Click:
MsgBox err.Description
Resume Exit_cmdPrintSlit_Click
End Sub

 

[Chintsapete]

I know it's an old tread but came across it looking for something else. There is another way doing it as well.
Format([Field],"0.00000"), works with csv in spite of being an number. I did that in a append query to the export table.
https://support.microsoft.com/en-us/kb/153364

 

[realmikehardy]

Old thread, but a goodie. Saved me in Access 2016 on Windows 10. Tweaking the Region settings is the trick. You need:

Control Panel > Clock and Region > Region > Additional Settings
or
Settings > Time & Language > Region > Additional date, time, & regional settings > Region (not a typo)> Additional Settings

Thanks all!

 

[petarjovanov]

I just registered to leave thanks for all of you guys and especially to GB_Joe
still the same problem in Windows 11 and Access 365
Cheers!

Edit: And the same solution