Data Change in CSV file after TransferText data export.

well, i tested the Ordinary Docmd.TransferText, see gasman's post #5.
try to re-import the csv and check if there are digit loss.
 
I did this test, when i import the CSV file previously created, the values are showed ok. Only when i export in CSV are wrong, or better are visualized wrongly :unsure:
 
LeoM said:
better are visualized wrongly :unsure:
Click to expand...
Excel has it's own way of doing things.
 
Upload your data for anyone to try and export.
 
The problem is not with the export routine. The problem is with Excel. By default, Excel tells Windows that .csv files should be opened with Excel so when you double-click on a .csv file, Excel opens rather than NotePad. I have changed this on my system but my users get in trouble all the time. The .csv format is plain text as you can see if you open it with NotePad.

You can avoid the problem in two ways.
1. reset the .csv extention so it is opened by NotePad.
2. NEVER double-click on the .csv file. Open Excel. Go to the Data tab, open the .csv file from the Data tab.
 
The problem may be caused by opening a CSV in excel. The best way to look at a csv is in notepad. At least you then see the actual characters in the file.

Excel can be quite dangerous in some ways, as it can change the csv file without you being aware.

If I want to be super careful, I tend to read lines from a CSV, and process them in code. Excel will decide column data types from the first few rows of a file, and can often get some wrong.

Eg, import a column that's maybe numeric , but with a few alphanumerics. Often the alphanumerics get rejected as bad data.
 
Last edited:
gemma-the-husky said:
The problem may be caused by opening a CSV in excel. The best way to look at a csv is in notepad. At least you then see the actual characters in the file.

Excel can be quite dangerous in some ways, as it can change the csv file without you being aware.

If I want to be super careful, I tend to read lines from a CSV, and process them in code. Excel will decide column data types from the first few rows of a file, and can often get some wrong.

Eg, import a column that's maybe numeric , but with a few alphanumerics. Often the alphanumerics get rejected as bad data.
Click to expand...

Good point, Dave, and it works both ways. If you are IMPORTING a spreadsheet where there are fields that look like numbers, Access (when left to its own internal directives) will interpret the column as a number even though it wasn't intended that way. So I'm not surprised that an unassisted export from Access would leave the destination ambiguous. And technically, the number change that is occurring is a format change, not really a number change. The issue is that a 12-digit number is too long for INTEGER or LONG data types (and SINGLE, for that matter), so it gets treated as the next largest size, one that can handle up to 15 digits - and that is DOUBLE. Seeing a DOUBLE in E-notation is therefore not actually a surprise.
 
gemma-the-husky said:
Excel will decide column data types from the first few rows of a file,
Click to expand...
I don't think so.
If I add a lot of records as text and then a long number saved as text, when I export the table as csv(text file), and open it in excel. the number is shown as E-notation.


2024-03-28_11-29-24.jpg
 
Last edited:
Gasman said:
Upload your data for anyone to try and export.
Click to expand...

Good morning everyone and thanks for all answer. Attached is part of my data who I usually export from MS ACCESS (trough query) in CSV. The problem is in the column "Q" (ManufSerialNo) for those 3 very long numbers. Cheers
 

Attachments

Excel is a bloody hopeless way to look at a csv.
 
LeoM said:
Good morning everyone and thanks for all answer. Attached is part of my data who I usually export from MS ACCESS (trough query) in CSV. The problem is in the column "Q" (ManufSerialNo) for those 3 very long numbers. Cheers
Click to expand...
That is the excel workbook? :(
We need the DB, or enough of it to be able to export the data and instructions on how to export which data.
I would just change the format in that workbook as I have mentioned previously, several time I believe?

To save anyone else having to downlod the zip file, this is what the worksheet looks like? so no issue there?

1711606880155.png
 
If it can help, following is my query result which I use to export in CSV with the following instruction:

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

The records which are wrongly transformed are in the column "ManufSerialNo" in red color, all the others are well exported. As you can see are different from what i received in CSV file and this create me problem of duplication.


_008_EQUI_Tempa _008_EQUI_Tempa

Functional locManufacturerModel numberManufSerialNo
OM.TIB.13-FCV-10003-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-001
OM.TIB.13-FCV-10005-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-002
OM.TIB.13-FCV-10007-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-003
OM.TIB.13-FCV-10009-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-004
OM.TIB.13-FCV-10011-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-005
OM.TIB.13-FCV-10013-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-006
OM.TIB.13-FCV-10015-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-007
OM.TIB.13-FCV-10017-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-008
OM.TIB.13-FCV-10019-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-009
OM.TIB.13-FCV-10021-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-010
OM.TIB.13-FCV-10023-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-011
OM.TIB.13-FCV-10025-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-012
OM.TIB.13-FCV-10027-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-013
OM.TIB.13-FCV-10029-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-014
OM.TIB.13-FCV-10031-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-015
OM.TIB.13-FCV-10033-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-016
OM.TIB.13-FCV-10035-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-017
OM.TIB.13-FCV-10037-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-018
OM.TIB.13-FCV-10039-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-019
OM.TIB.13-FCV-10041-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-020
OM.TIB.13-FCV-10043-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-002-021
OM.TIB.13-FCV-10045-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-001
OM.TIB.13-FCV-10047-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-002
OM.TIB.13-FCV-10049-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-003
OM.TIB.13-FCV-10051-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-004
OM.TIB.13-FCV-10053-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-005
OM.TIB.13-FCV-10055-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-006
OM.TIB.13-FCV-10057-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-007
OM.TIB.13-FCV-10059-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-008
OM.TIB.13-FCV-10061-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-009
OM.TIB.13-FCV-10063-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-010
OM.TIB.13-FCV-10065-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-011
OM.TIB.13-FCV-10067-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-012
OM.TIB.13-FCV-10069-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-013
OM.TIB.13-FCV-10071-CVSEVERN GLOCON5415_P2CN_DN80_CL15002110106-003-014
OM.TIB.17-FCV-10001-CVFLOWSERVEMARK ONE/2"CL300847621.001
OM.TIB.17-FCV-10002-CVFLOWSERVEMARK ONE/2"CL300847621.002
OM.TIB.17-KSV-10010-CVLCMT22_DN80_CL30022 204 0001 0001
OM.TIB.17-KSV-10011-CVLCMT22_DN100_CL30022 204 0002 0001
OM.TIB.17-KSV-10012-CVLCMT22_DN100_CL30022 204 0003 0001
OM.TIB.17-PDCV-10003-CVFLOWSERVEMARK ONE/3"CL300847621.003
OM.TIB.17-PDCV-10006-CVFLOWSERVEMARK ONE/1"CL300847621.004
OM.TIB.17-UZV-10007-ESDLCMT22_DN150X100_CL3002203000040001
OM.TIB.17-UZV-10008-ESDLCMT22_DN50X40_CL3002203000050001
OM.TIB.17-UZV-10009-ESDLCMT22_DN150X100_CL3002203000040002
OM.TIB.33-XZA-10047-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033230 63
OM.TIB.33-XZA-10048-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100031999 12
OM.TIB.33-XZA-10049-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100031999 64
OM.TIB.33-XZA-10050-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033230 67
OM.TIB.33-XZA-10051-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033230 81
OM.TIB.33-XZA-10052-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033230 85
OM.TIB.33-XZA-10053-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033230 84
OM.TIB.33-XZA-10054-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033230 83
OM.TIB.33-XZA-10055-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033230 72
OM.TIB.33-XZA-10056-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033230 75
OM.TIB.33-XZA-10057-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033230 62
OM.TIB.33-XZA-10058-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033231 33
OM.TIB.33-XZA-10059-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033231 31
OM.TIB.33-XZA-10060-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033231 28
OM.TIB.33-XZA-10063-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033231 20
OM.TIB.33-XZA-10064-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033231 25
OM.TIB.33-XZA-10065-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033231 18
OM.TIB.33-XZA-10066-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033231 19
OM.TIB.33-XZA-10067-VHAR. STAHL SCHALTGERATEFL60/C/D50/X/EU/T100033231 23
Cheers.
 
Why is there not a consistent format for that field?
I only looked at the first few rows and they all had hyphens in them, so I thought you had solved it that way?

Just downloaded to my phone and opened it on that and I get ?

Screenshot_20240328-094018_Excel.jpg
 
Last edited:
Sorry, but i believe i don't understand you. This is the original query from the database, data are stored as it is showed. What do you mean when you say that are not consistent, format is the same, we speak about technical data which is normal are like that.
Honestly i don't understand where is the hyphens you also mentioned, my problem is when i transfer this query (with the instruction i mentioned) to CSV, the result for those specific records is:

LCM IT T22_DN150X100_CL300 2.203E+12
LCM IT T22_DN50X40_CL300 2.203E+12
LCM IT T22_DN150X100_CL300 2.203E+12
 
A CSV is a text file, i.e. a simple file that only contains plain text, but never own code.
So if you open the text file with a simple text editor as written above, you will see the content exactly as it was written. There is nothing that can independently change anything.

If you open the file with Excel, it's different because Excel is already doing a lot of its own things in the background.

A CSV is a text file, not an Excel file, even though MS developers once decided to set Excel as the default program for opening CSVs.

So: What do you use to view the file after you create it?
 
ebs17 said:
A CSV is a text file, i.e. a simple file that only contains plain text, but never own code.
So if you open the text file with a simple text editor as written above, you will see the content exactly as it was written. There is nothing that can independently change anything.

If you open the file with Excel, it's different because Excel is already doing a lot of its own things in the background.

A CSV is a text file, not an Excel file, even though MS developers once decided to set Excel as the default program for opening CSVs.

So: What do you use to view the file after you create it?
Click to expand...
Just to clarify the scope of all of this, maybe there is another solution: actually I'm forced to prepare a CSV template file because it is required by the SAP functional team who upload data into the system trough a program who read the CSV File. So, as i explained, i export my data from my ms access database in to a CSV file. The team who receive my template (CSV file) , simple take the CSV file and import in their application but when they try to import it they get duplication error due to the "wrong" transformation of those long numbers. My question is: there is a way to create a CSV avoiding this transformation, regardless how it is open, i even don't need to open it (this is my answer).
 
A CSV is plain text. This file or its contents will never change independently.
As written, you can check this by simply checking. Changes only occur through access from third parties or simply through a clumsy import because no suitable import specification is then used.

The CSV is innocent. Those involved should give up their belief in their own infallibility and open themselves up to self-criticism.
 
LeoM said:
Sorry, but i believe i don't understand you. This is the original query from the database, data are stored as it is showed. What do you mean when you say that are not consistent, format is the same, we speak about technical data which is normal are like that.
Honestly i don't understand where is the hyphens you also mentioned, my problem is when i transfer this query (with the instruction i mentioned) to CSV, the result for those specific records is:

LCM IT T22_DN150X100_CL300 2.203E+12
LCM IT T22_DN50X40_CL300 2.203E+12
LCM IT T22_DN150X100_CL300 2.203E+12
Click to expand...
That is how it is when I open it in Excel for android. The first set of rows have hyphens, then decimal points, then spaces? :(
Note rows 44 to 46 have none of these, yet display as you want them?
 
Last edited:
LeoM said:
Just to clarify the scope of all of this, maybe there is another solution: actually I'm forced to prepare a CSV template file because it is required by the SAP functional team who upload data into the system trough a program who read the CSV File. So, as i explained, i export my data from my ms access database in to a CSV file. The team who receive my template (CSV file) , simple take the CSV file and import in their application but when they try to import it they get duplication error due to the "wrong" transformation of those long numbers. My question is: there is a way to create a CSV avoiding this transformation, regardless how it is open, i even don't need to open it (this is my answer).
Click to expand...
Look at the csv file on text editor. Are the numbers changed there?, I doubt it.
Your SAP people appear to be opening the file in Excel as opposed importing the csv directly?
 
Do you have control over the original query? If so, change the format of the output to FORMAT( x, "@@@@@@@@@@@@@@@" ) (or however many characters you need for the content of that field.)
 
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
277
The_Doc_Man
The_Doc_Man
isladogs
Access Europe meeting on Wed 5 June - Extended File Properties / Manage Import/Export Data Tasks
Replies
2
Views
375
isladogs
isladogs
RickHunter84
Item not found in collection - Error 3265
Replies
12
Views
1,481
Pat Hartman
P
T
CSV semicolon delimited without header import and all data in first table column
Replies
12
Views
1,885
CJ_London
C
C
How do i remove quotations in text file export.
Replies
8
Views
827
Isaac
Isaac

Users who are viewing this thread

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