Data Change in CSV file after TransferText data export. (2 Viewers)

[LeoM]

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.

 

[arnelgp]

is 2203000040001 a Number? it seems very long number (since Long Integer can only accept upto 8-9 digits).

 

[LeoM]

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.

 

[arnelgp]

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?

 

[Gasman]

The number is still there, just formatted like that.

Just change the format to Number

Or try exporting as a string.

 

[LeoM]

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.

 

[Gasman]

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?

 

[LeoM]

Surround the number with ' before export.
Format the column as Number (as I have already said)

First option is likely the easisest.

Surround the number with ' you mean in the query like : "'" & [Field]
I tried and i got the same

 

[ebs17]

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.

 

[LeoM]

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?

I work as CMMS of an Oil&Gas company so in general with all discipline, including Piping yes.

 

[LeoM]

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.

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.

 

[Gasman]

What happens if you transfer direct to an Excel worksheet using TransferSpreadsheet

 

[arnelgp]

I usually create specification when I import (with wizard

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).

 

[Gasman]

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).

I cannot see where you can specify each column (in 2007 at least) ? whether as Excel or Text

 

[LeoM]

I cannot see where you can specify each column (in 2007 at least) ? whether as Excel or Text

Yes not possible to specify, i have only list of fields not possible to change any type.

 

[arnelgp]

I cannot see where you can specify each column (in 2007 at least) ? whether as Excel or Text

so its true.
anyway, you can create your own Export routine:

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):

Call ExportTableToText("yourTableOrQueryName", Environ$("userprofile") & "\documents\x.txt")

 

[ebs17]

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.

 

[LeoM]

so its true.
anyway, you can create your own Export routine:

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):

Call ExportTableToText("yourTableOrQueryName", Environ$("userprofile") & "\documents\x.txt")

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

 

[arnelgp]

i change it and it will work with CSV:

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

 

[LeoM]

i change it and it will work with CSV:

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

Yes, the code work fine but those values are always "wrong", i mean are written with "+".

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

Any other idea ?
Cheers