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

[arnelgp]

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.

 

[LeoM]

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

 

[arnelgp]

better are visualized wrongly

Excel has it's own way of doing things.

 

[Gasman]

Upload your data for anyone to try and export.

 

[Pat Hartman]

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.

 

[gemma-the-husky]

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.

 

[The_Doc_Man]

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.

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.

 

[KitaYama]

Excel will decide column data types from the first few rows of a file,

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.

 

[LeoM]

Upload your data for anyone to try and export.

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

 

[Galaxiom]

Excel is a bloody hopeless way to look at a csv.

 

[Gasman]

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

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?

 

[LeoM]

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 loc	Manufacturer	Model number	ManufSerialNo
OM.TIB.13-FCV-10003-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-001
OM.TIB.13-FCV-10005-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-002
OM.TIB.13-FCV-10007-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-003
OM.TIB.13-FCV-10009-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-004
OM.TIB.13-FCV-10011-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-005
OM.TIB.13-FCV-10013-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-006
OM.TIB.13-FCV-10015-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-007
OM.TIB.13-FCV-10017-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-008
OM.TIB.13-FCV-10019-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-009
OM.TIB.13-FCV-10021-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-010
OM.TIB.13-FCV-10023-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-011
OM.TIB.13-FCV-10025-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-012
OM.TIB.13-FCV-10027-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-013
OM.TIB.13-FCV-10029-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-014
OM.TIB.13-FCV-10031-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-015
OM.TIB.13-FCV-10033-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-016
OM.TIB.13-FCV-10035-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-017
OM.TIB.13-FCV-10037-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-018
OM.TIB.13-FCV-10039-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-019
OM.TIB.13-FCV-10041-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-020
OM.TIB.13-FCV-10043-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-002-021
OM.TIB.13-FCV-10045-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-001
OM.TIB.13-FCV-10047-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-002
OM.TIB.13-FCV-10049-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-003
OM.TIB.13-FCV-10051-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-004
OM.TIB.13-FCV-10053-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-005
OM.TIB.13-FCV-10055-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-006
OM.TIB.13-FCV-10057-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-007
OM.TIB.13-FCV-10059-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-008
OM.TIB.13-FCV-10061-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-009
OM.TIB.13-FCV-10063-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-010
OM.TIB.13-FCV-10065-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-011
OM.TIB.13-FCV-10067-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-012
OM.TIB.13-FCV-10069-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-013
OM.TIB.13-FCV-10071-CV	SEVERN GLOCON	5415_P2CN_DN80_CL1500	2110106-003-014
OM.TIB.17-FCV-10001-CV	FLOWSERVE	MARK ONE/2"CL300	847621.001
OM.TIB.17-FCV-10002-CV	FLOWSERVE	MARK ONE/2"CL300	847621.002
OM.TIB.17-KSV-10010-CV	LCM	T22_DN80_CL300	22 204 0001 0001
OM.TIB.17-KSV-10011-CV	LCM	T22_DN100_CL300	22 204 0002 0001
OM.TIB.17-KSV-10012-CV	LCM	T22_DN100_CL300	22 204 0003 0001
OM.TIB.17-PDCV-10003-CV	FLOWSERVE	MARK ONE/3"CL300	847621.003
OM.TIB.17-PDCV-10006-CV	FLOWSERVE	MARK ONE/1"CL300	847621.004
OM.TIB.17-UZV-10007-ESD	LCM	T22_DN150X100_CL300	2203000040001
OM.TIB.17-UZV-10008-ESD	LCM	T22_DN50X40_CL300	2203000050001
OM.TIB.17-UZV-10009-ESD	LCM	T22_DN150X100_CL300	2203000040002
OM.TIB.33-XZA-10047-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033230 63
OM.TIB.33-XZA-10048-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100031999 12
OM.TIB.33-XZA-10049-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100031999 64
OM.TIB.33-XZA-10050-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033230 67
OM.TIB.33-XZA-10051-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033230 81
OM.TIB.33-XZA-10052-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033230 85
OM.TIB.33-XZA-10053-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033230 84
OM.TIB.33-XZA-10054-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033230 83
OM.TIB.33-XZA-10055-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033230 72
OM.TIB.33-XZA-10056-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033230 75
OM.TIB.33-XZA-10057-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033230 62
OM.TIB.33-XZA-10058-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033231 33
OM.TIB.33-XZA-10059-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033231 31
OM.TIB.33-XZA-10060-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033231 28
OM.TIB.33-XZA-10063-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033231 20
OM.TIB.33-XZA-10064-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033231 25
OM.TIB.33-XZA-10065-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033231 18
OM.TIB.33-XZA-10066-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033231 19
OM.TIB.33-XZA-10067-VHA	R. STAHL SCHALTGERATE	FL60/C/D50/X/EU/T	100033231 23

Cheers.

 

[Gasman]

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 ?

 

[LeoM]

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

 

[ebs17]

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?

 

[LeoM]

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?

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

 

[ebs17]

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.

 

[Gasman]

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

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?

 

[Gasman]

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

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?

 

[The_Doc_Man]

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