In Access column name is STKITEMNBR and data type is TEXT. 4/5 of data are numeric and 1/5 are alfa-numeric. One of data was 15E10 in Access, but was altered to 1.50E+11 when exporting out to Excel csv file. Because it was Stock Item Number it needed to stay the same as 15E10 in csv file. Could anyone give me a solution on the issue? :banghead:
Data Altered in Transaction from Access to Excel csv file
- Thread starter aj81
- Start date
Appreciated for your reply. It worked fine with actual excel file (.xlsx), but same issue surfaced again when save it to .csv file which is required. Sure make "" or ' or Text format in the cell could retain the form. But you need to work out each field manually. In the case tens of thousands of lines data transferred from Access or SQL into Excel .csv file, apart from manually pick them up, I am looking for better solution. I even tried SSIS which can pre-define data type in the package, still no joy. So long excel .csv sees 15E10 it converted to a scientific form 1.50E+11. Really frustrated. :banghead: Any more suggestions?
Import a csv file defining the field as text and save the Import Specification.
Use this sepecification as the second argument in the TransferText method when exporting.
Use this sepecification as the second argument in the TransferText method when exporting.
Galaxiom offcourse means export specification, but the global idea is the same.
You shouldnt need to ensure "" around different rows. just different columns, the number of columns usually is limited.
No I did mean Import Specification. Import and Export specs are really the same thing.
I just find it easy to create them by importing an example of the file that is in the exact format required then using that spec as the Export spec. It tends to show up anything that might cause trouble.
Thank you all for the help, but it still didn't work. That's what I did.
1. Open up Access, click Text File in Import & Link group of External Data tab (as csv file is text file).
2. Import csv file from hard drive and save the file as "AB_Stock Import Specification" and data type as Text for the column once Advanced... button was clicked.
3. Save a table "AB_Stock" in Access. Checked the data 15E10 in there.
4. Write a subroutine as
DoCmd.TransferText acExportDelim, "AB_Stock Import Specification", "AB_Stock", "G:\Test\AB_Stock.csv"
5. Open up csv file after running the subroutine. 15E10 has been turned to 1.50+11.
It seemed to me interanlly csv treated 15E10 as scientific format during data transaction from other data system. Unless you manually type in the number with text format.
Any thoughts are welcomed. It would be highly appreciated if you could do a small test and let me know your result.
1. Open up Access, click Text File in Import & Link group of External Data tab (as csv file is text file).
2. Import csv file from hard drive and save the file as "AB_Stock Import Specification" and data type as Text for the column once Advanced... button was clicked.
3. Save a table "AB_Stock" in Access. Checked the data 15E10 in there.
4. Write a subroutine as
DoCmd.TransferText acExportDelim, "AB_Stock Import Specification", "AB_Stock", "G:\Test\AB_Stock.csv"
5. Open up csv file after running the subroutine. 15E10 has been turned to 1.50+11.
It seemed to me interanlly csv treated 15E10 as scientific format during data transaction from other data system. Unless you manually type in the number with text format.
Any thoughts are welcomed. It would be highly appreciated if you could do a small test and let me know your result.
- Local time
- Yesterday, 18:46
- Joined
- Feb 28, 2001
- Messages
- 30,090
The problem, of course, is that Excel by default treats all output cells as "General Format" items, which is Microsoft-ese for "FREE FOR ALL FOOD FIGHT" or something like that.
You MIGHT try pre-defining a spreadsheet with each column pre-defined as to format and then exporting the data to the spreadsheet but not starting at row 1. I have to admit it has been a long time since I did that sort of thing and don't know if it is even still possible - but the last time I ran into this, it was solved by pre-formatting the spreadsheet.
You should also be able to use the FileSystemObject methods to COPY a blank spreadsheet of the right format and then export to the copy, leaving the master blank spreadsheet intact for the next iteration.
You MIGHT try pre-defining a spreadsheet with each column pre-defined as to format and then exporting the data to the spreadsheet but not starting at row 1. I have to admit it has been a long time since I did that sort of thing and don't know if it is even still possible - but the last time I ran into this, it was solved by pre-formatting the spreadsheet.
You should also be able to use the FileSystemObject methods to COPY a blank spreadsheet of the right format and then export to the copy, leaving the master blank spreadsheet intact for the next iteration.
What is actually in the csv if you open it in a text editor like Notepad? They are just text files.
I expect the problem with the change to a scientific notation number is when Excel opens the csv rather than a problem with the csv itself.
I expect the problem with the change to a scientific notation number is when Excel opens the csv rather than a problem with the csv itself.
Thanks for the help which makes me thinking.
Firstly, pre-defining csv file didn't work. You could change "General Format" to "Text Format" for a column in spreadsheet, but you cannot save the file as the image in the attachment.
It popped up an info box. Basicly it says:
"features are not compatable with csv
To leave out incompatable features click Yes
To perserve the features click No, then save a copy in latest Excel format"
End of story. Is csv an Excel format? Clcking the help button in the info box it tells you csv is text format, not excel format. ie. you can't save the incompatable features (in this case Text format in a column) into a pre-defined csv file.
Secondly, when opening csv file with Notepad "15E10" was perfectly preserved. But when you open it with Excel "1.50E+11" shows up.
Conclusion: It's impossible to preserve data like "15E10" in Excel.csv when data were transferred from another data system. Not only I tried Access -> Excel.csv I also tried SQL -> Excel.csv (using SSIS you can pre-define Excel data type). No joy in either transactions.
The only way I could get around the situation in real business is after csv file generated I open it up with Excel and search with the search string "*E+*" in the Stock Item Number and replace them with original values. Though there are thousands of lines of data there are only few falling into this category. Thanks God!
It's tough in business. You need to get them done one way or another "by hooks or by crooks".
Firstly, pre-defining csv file didn't work. You could change "General Format" to "Text Format" for a column in spreadsheet, but you cannot save the file as the image in the attachment.
It popped up an info box. Basicly it says:
"features are not compatable with csv
To leave out incompatable features click Yes
To perserve the features click No, then save a copy in latest Excel format"
End of story. Is csv an Excel format? Clcking the help button in the info box it tells you csv is text format, not excel format. ie. you can't save the incompatable features (in this case Text format in a column) into a pre-defined csv file.
Secondly, when opening csv file with Notepad "15E10" was perfectly preserved. But when you open it with Excel "1.50E+11" shows up.
Conclusion: It's impossible to preserve data like "15E10" in Excel.csv when data were transferred from another data system. Not only I tried Access -> Excel.csv I also tried SQL -> Excel.csv (using SSIS you can pre-define Excel data type). No joy in either transactions.
The only way I could get around the situation in real business is after csv file generated I open it up with Excel and search with the search string "*E+*" in the Stock Item Number and replace them with original values. Though there are thousands of lines of data there are only few falling into this category. Thanks God!
It's tough in business. You need to get them done one way or another "by hooks or by crooks".
Attachments
Like I said. The problem is with Excel, not the csv.
If the data is required in Excel it should be exported to an Excel file rather than a text file such as csv.
Since csv is the nominated format then there isn't a problem at all unless you open and save it in Excel where it may well save the newly interpreted numeric value in the csv.
If the data is required in Excel it should be exported to an Excel file rather than a text file such as csv.
Since csv is the nominated format then there isn't a problem at all unless you open and save it in Excel where it may well save the newly interpreted numeric value in the csv.
- Local time
- Yesterday, 19:46
- Joined
- Feb 19, 2002
- Messages
- 46,972
I think you are missing the point. The csv file is being created correctly and the application that you are sending it to will not have a problem. YOU are having a problem only because you are opening the file by double clicking on it. The associated program is Excel. If you open the file with Notepad, it will be fine.
You didn't say if you chose the option to delimit text fields with quotes. I always do this since it eliminates a lot of problems. If you do that, Excel should accept the column as text and not reformat it.
You didn't say if you chose the option to delimit text fields with quotes. I always do this since it eliminates a lot of problems. If you do that, Excel should accept the column as text and not reformat it.
Thanks Pat Hartman for your criticism. Things became clear at the end of discussion through the forum. I am learning everyday. At least those people tried to help. I appreciated for their genuine help.
Where were you at the beginning of the discussion? When things become clear and conclusions have been made we don't need you to emerge suddenly to pull your fingers out! Blah, Blah. What a hypocrisy!
Where were you at the beginning of the discussion? When things become clear and conclusions have been made we don't need you to emerge suddenly to pull your fingers out! Blah, Blah. What a hypocrisy!
- Local time
- Yesterday, 19:46
- Joined
- Feb 19, 2002
- Messages
- 46,972
No problem, you won't ever hear from me again.
- Local time
- Today, 00:46
- Joined
- Sep 12, 2006
- Messages
- 15,986
is 15E10 text, or a number?
