Data Change in CSV file after TransferText data export. (1 Viewer)

L

LeoM

Member
Local time
Today, 14:27
Joined
Jun 22, 2023
Messages
56
Good morning everyone.
I'm actually use a simple function to export some data trough a selection query to a CSV File. The problem is that when i export the data from my table (trough query) in the CSV output file i have some values (long numbers) who change. See please below :

This is part of my function I use to export the data in a CSV file :

Docmd.TransferText acExportDelim, , "008_EQUI_Temp", myfilename, True

These are part of my original data in the query :

LCM IT T22_DN150X100_CL300 2203000040001
LCM IT T22_DN50X40_CL300 2203000050001
LCM IT T22_DN150X100_CL300 2203000040002

And this is what I get in the CSV file (please focus on last column):

LCM IT T22_DN150X100_CL300 2.203E+12
LCM IT T22_DN50X40_CL300 2.203E+12
LCM IT T22_DN150X100_CL300 2.203E+12

This create me a problem of duplication other then of wrong data and not sure how to solve it, i tried to check in the net but didn't find appropriate solution.
Anyone face with the same problem and find a way to solve it?
Thank you for usual and kindly support.
Cheers.
 
is 2203000040001 a Number? it seems very long number (since Long Integer can only accept upto 8-9 digits).
 
In my table the field (Model Number) is a Short Text type not a number but it seams in the CSV is transformed in a number.
 
on your Query on that column add "":

Expr: [field1] & ""

or

Expr: CStr([Field1])

BTW DN150 is 6", are you in construction (piping)?, probably an Olet?
 
The number is still there, just formatted like that.

1711525186720.png


Just change the format to Number
1711525255333.png

Or try exporting as a string.
 
Last edited:
Yes the number is there i can see it, however there is a team that take my CSV template and check the data in the file and they find duplications in this way so i need to also show as it is. I tried the 2 way suggested by arnelgp but in both cases i got the same. How to export as string ? By i suppose if i export as string i will visualize the same, i believe only if i export as number i will get it. I may be wrong.
 
Surround the number with ' before export.
Format the column as Number (as I have already said)

First option is likely the easisest.

To set as text in Excel you precede the number with a '

You could also try that?
 
Gasman said:
Surround the number with ' before export.
Format the column as Number (as I have already said)

First option is likely the easisest.
Click to expand...
Surround the number with ' you mean in the query like : "'" & [Field]
I tried and i got the same
 
Docmd.TransferText acExportDelim, ___ , "008_EQUI_Temp", myfilename, True

Text should remain text and should not be reformatted.
You could create and use an export specification where this column is defined as text.
 
arnelgp said:
on your Query on that column add "":

Expr: [field1] & ""

or

Expr: CStr([Field1])

BTW DN150 is 6", are you in construction (piping)?, probably an Olet?
Click to expand...
I work as CMMS of an Oil&Gas company so in general with all discipline, including Piping yes.
 
ebs17 said:
Docmd.TransferText acExportDelim, ___ , "008_EQUI_Temp", myfilename, True

Text should remain text and should not be reformatted.
You could create and use an export specification where this column is defined as text.
Click to expand...
I usually create specification when I import (with wizard) but how i can do when i export , i don't have even the possibility to save in CSV, only XLSX or similar and don't give me the possibility to choose the specification. Sorry don't know how to do it.
 
What happens if you transfer direct to an Excel worksheet using TransferSpreadsheet
 
LeoM said:
I usually create specification when I import (with wizard
Click to expand...
you can create the same for export.
first create an export specification by manually exporting the query and saving your Export spec to a Name.
later, use the Export Spec name on your DoCmd.TransferText (google the syntax).
 
arnelgp said:
you can create the same for export.
first create an export specification by manually exporting the query and saving your Export spec to a Name.
later, use the Export Spec name on your DoCmd.TransferText (google the syntax).
Click to expand...
I cannot see where you can specify each column (in 2007 at least) ? whether as Excel or Text
 
Gasman said:
I cannot see where you can specify each column (in 2007 at least) ? whether as Excel or Text
Click to expand...
Yes not possible to specify, i have only list of fields not possible to change any type.
 
Gasman said:
I cannot see where you can specify each column (in 2007 at least) ? whether as Excel or Text
Click to expand...
so its true.
anyway, you can create your own Export routine:
Code: 
Sub ExportTableToText(ByVal TableName As String, ByVal textFilePath As String, Optional ByVal fieldSeparator As String = ",")

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim textFileNum As Integer
    Dim lineText As String
    Dim value As Variant
    Dim i As Integer
    ' Set the database object
    Set db = CurrentDb
   
    ' Set the table recordset
    Set rs = db.OpenRecordset(TableName)
   
    ' Open a text file for writing
    textFileNum = FreeFile
    Open textFilePath For Output As #textFileNum
   
    ' Loop through the recordset and write each record to the text file
    With rs
        Do While .EOF
            lineText = ""
            For i = 0 To rs.Fields.Count - 1
                value = .Fields(i).value
                Select Case .Fields(i).Type
                Case 10, 12
                    value = value & ""
                End Select
                lineText = lineText & value & fieldSeparator
            Next i
            ' Remove the trailing separator
            lineText = Left(lineText, Len(lineText) - Len(fieldSeparator))
            ' Write the line to the text file
            Print #textFileNum, lineText
            ' Move to the next record
            .MoveNext
        Loop
        .Close
    End With
   
    ' Close the text file
    Close #textFileNum
   
    ' Release the objects
    Set rs = Nothing
    Set db = Nothing

    MsgBox "Table exported successfully to " & textFilePath, vbInformation

End Sub

To use (sample):
Code: 
Call ExportTableToText("yourTableOrQueryName", Environ$("userprofile") & "\documents\x.txt")
 
The specification for export and import with the same structure of the text file is the same. So you can also import a text file, create the specification and remember the name of this specification so that you can then use it when exporting.
 
arnelgp said:
so its true.
anyway, you can create your own Export routine:
Code: 
Sub ExportTableToText(ByVal TableName As String, ByVal textFilePath As String, Optional ByVal fieldSeparator As String = ",")

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim textFileNum As Integer
    Dim lineText As String
    Dim value As Variant
    Dim i As Integer
    ' Set the database object
    Set db = CurrentDb
  
    ' Set the table recordset
    Set rs = db.OpenRecordset(TableName)
  
    ' Open a text file for writing
    textFileNum = FreeFile
    Open textFilePath For Output As #textFileNum
  
    ' Loop through the recordset and write each record to the text file
    With rs
        Do While .EOF
            lineText = ""
            For i = 0 To rs.Fields.Count - 1
                value = .Fields(i).value
                Select Case .Fields(i).Type
                Case 10, 12
                    value = value & ""
                End Select
                lineText = lineText & value & fieldSeparator
            Next i
            ' Remove the trailing separator
            lineText = Left(lineText, Len(lineText) - Len(fieldSeparator))
            ' Write the line to the text file
            Print #textFileNum, lineText
            ' Move to the next record
            .MoveNext
        Loop
        .Close
    End With
  
    ' Close the text file
    Close #textFileNum
  
    ' Release the objects
    Set rs = Nothing
    Set db = Nothing

    MsgBox "Table exported successfully to " & textFilePath, vbInformation

End Sub

To use (sample):
Code: 
Call ExportTableToText("yourTableOrQueryName", Environ$("userprofile") & "\documents\x.txt")
Click to expand...
Thank you.
Does it work with CSV ? because i tried to add this code but to create CSV (instead of txt) but when it goes trough the loop is close immediatelly, probably because the file is empty. Any idea ?
Cheers
 
i change it and it will work with CSV:
Code: 
Sub ExportTableToText(ByVal TableName As String, ByVal textFilePath As String, Optional ByVal fieldSeparator As String = ",")

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim textFileNum As Integer
    Dim lineText As String
    Dim value As Variant
    Dim i As Integer
    ' Set the database object
    Set db = CurrentDb
    
    ' Set the table recordset
    Set rs = db.OpenRecordset(TableName, dbOpenSnapshot, dbReadOnly)
    
    ' Open a text file for writing
    textFileNum = FreeFile
    Open textFilePath For Output As #textFileNum
    
    ' Loop through the recordset and write each record to the text file
    With rs
        ' print the header
        For i = 0 To .Fields.Count - 1
            lineText = lineText & .Fields(i).Name & fieldSeparator
        Next
        lineText = Left(lineText, Len(lineText) - Len(fieldSeparator))
        Print #textFileNum, lineText
        ' print the records
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do While Not .EOF
            lineText = ""
            For i = 0 To .Fields.Count - 1
                value = .Fields(i).value
                Select Case .Fields(i).Type
                Case 10, 12
                    value = value & ""
                End Select
                lineText = lineText & value & fieldSeparator
            Next i
            ' Remove the trailing separator
            lineText = Left(lineText, Len(lineText) - Len(fieldSeparator))
            ' Write the line to the text file
            Print #textFileNum, lineText
            ' Move to the next record
            .MoveNext
        Loop
        .Close
    End With
    
    ' Close the text file
    Close #textFileNum
    
    ' Release the objects
    Set rs = Nothing
    Set db = Nothing

    MsgBox "Table exported successfully to " & textFilePath, vbInformation

End Sub
 
arnelgp said:
i change it and it will work with CSV:
Code: 
Sub ExportTableToText(ByVal TableName As String, ByVal textFilePath As String, Optional ByVal fieldSeparator As String = ",")

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim textFileNum As Integer
    Dim lineText As String
    Dim value As Variant
    Dim i As Integer
    ' Set the database object
    Set db = CurrentDb
   
    ' Set the table recordset
    Set rs = db.OpenRecordset(TableName, dbOpenSnapshot, dbReadOnly)
   
    ' Open a text file for writing
    textFileNum = FreeFile
    Open textFilePath For Output As #textFileNum
   
    ' Loop through the recordset and write each record to the text file
    With rs
        ' print the header
        For i = 0 To .Fields.Count - 1
            lineText = lineText & .Fields(i).Name & fieldSeparator
        Next
        lineText = Left(lineText, Len(lineText) - Len(fieldSeparator))
        Print #textFileNum, lineText
        ' print the records
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do While Not .EOF
            lineText = ""
            For i = 0 To .Fields.Count - 1
                value = .Fields(i).value
                Select Case .Fields(i).Type
                Case 10, 12
                    value = value & ""
                End Select
                lineText = lineText & value & fieldSeparator
            Next i
            ' Remove the trailing separator
            lineText = Left(lineText, Len(lineText) - Len(fieldSeparator))
            ' Write the line to the text file
            Print #textFileNum, lineText
            ' Move to the next record
            .MoveNext
        Loop
        .Close
    End With
   
    ' Close the text file
    Close #textFileNum
   
    ' Release the objects
    Set rs = Nothing
    Set db = Nothing

    MsgBox "Table exported successfully to " & textFilePath, vbInformation

End Sub
Click to expand...
Yes, the code work fine but those values are always "wrong", i mean are written with "+".

LCMITT22_DN150X100_CL300
2.203E+12​
LCMITT22_DN50X40_CL300
2.203E+12​
LCMITT22_DN150X100_CL300
2.203E+12​

Any other idea ?
Cheers
 
You must log in or register to reply here.

Similar threads

S
The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625)
Replies
12
Views
300
The_Doc_Man
The_Doc_Man
J
Use VBA to export form data to Excel, but keep getting "We Found a Problem..." when opening the file in Excel
Replies
5
Views
79
June7
June7
isladogs
Access Europe meeting on Wed 5 June - Extended File Properties / Manage Import/Export Data Tasks
Replies
2
Views
381
isladogs
isladogs
RickHunter84
Item not found in collection - Error 3265
Replies
12
Views
1,508
Pat Hartman
P
T
CSV semicolon delimited without header import and all data in first table column
Replies
12
Views
1,913
CJ_London
C

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom