Notes tests, text files, manipulation of text files in Excel and with Excel VBA CSV stuff

**DocAElstein** · 06-16-2020, 11:14 AM

One important note at the outset of these notes is that my Excel is default German. I expect therefore issues likely to arise in anything to do with use of a comma , because often the comma , in English for certain things in Excel is replaced with the semi colon ; in German Excel.

Wot’s in a .csv file
In many Excel versions, you have the choice of three different Save As options related to .csv extension files https://excel.tips.net/T002519_Comma...C_and_Mac.html

So lets have a quick look at what the differences are…
In each of the 3 cases I will
make a virgin default template, save it
make a virgin default template, put a value in first cell, save it
make a virgin default template, put a value in first 2 cells, save it
make a virgin default template, put a value in first 2 cells, and in the cell A2, save it
( Initially, when I put values in, I wont hit the Enter after: Initially I want to avoid purposely doing something that may introduce a carriage return or line feed. I will then look further at that issue later )
After this I will investigate the made files , ( using , for example, my Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String) ) ' https://excelfox.com/forum/showthrea...ts-of-a-string ' https://excelfox.com/forum/showthrea...ll=1#post11015
https://excelfox.com/forum/showthrea...ll=1#post13699

Here are the results ( It is a summary of what my function tells me is in those made files ) :

CSV (Comma delimited)Empty.csv		vbCr & vbLf
CSV (Macintosh)Empty.csv		vbCr & vbLf
CSV (MS-DOS)Empty.csv		vbCr & vbLf
CSV (Comma delimited)A1.csv	cellA1	"cellA1" & vbCr & vbLf
CSV (Macintosh)A1.csv	cellA1	"cellA1" & vbCr & vbLf
CSV (MS-DOS)A1.csv	cellA1	"cellA1" & vbCr & vbLf
CSV (Comma delimited)A1B1.csv	cellA1;cellB1	"cellA1" & ";" & "cellB1" & vbCr & vbLf
CSV (Macintosh)A1B1.csv	cellA1;cellB1	"cellA1" & ";" & "cellB1" & vbCr & vbLf
CSV (MS-DOS)A1B1.csv	cellA1;cellB1	"cellA1" & ";" & "cellB1" & vbCr & vbLf
CSV (Comma delimited)A1B1A2.csv	cellA1;cellB1 callA2;	"cellA1" & ";" & "cellB1" & vbCr & vbLf & "callA2" & ";" & vbCr & vbLf
CSV (Macintosh)A1B1A2.csv	cellA1;cellB1 callA2;	"cellA1" & ";" & "cellB1" & vbCr & "callA2" & ";" & vbCr & vbLf
CSV (MS-DOS)A1B1A2.csv	cellA1;cellB1 callA2;	"cellA1" & ";" & "cellB1" & vbCr & vbLf & "callA2" & ";" & vbCr & vbLf

Important Conclusions are
_ the Macintosh distinguishes itself with a carriage return character, vbCr , as the line separator for introduced lines
_ There is always a last vbCr & vbLf – Note this means that for a single line, or empty file, we could not tell if we had a Macintosh

When closing the file, I was prompted to answer if I wanted to save changes or not. ( I chose yes in the last experiment ). This is strange since I had previously saved the files before closing
DoYouWantToSaveChangesOnCloseDespiteAlreadySavedCS V.JPG : https://imgur.com/nfnVwSF
But it does not seem to have any effect if I chose Yes or No
Some other observations.
If I use a simple macro, as below, to save and close the file ( and except the changes, which I am still strangely asked for , with Yes), then I get commas instead for the separator/delimiter

Code:

Sub SaveCSVviaVBA()
 ActiveWorkbook.Save
 ActiveWorkbook.Close
End Sub

I get the same results for answering No

These macros gives me the same results

Code:

Sub SaveAsCSVviaVBA()
 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "csv Text file Chaos\" & "CSV (Comma delimited)A1B1A2" & ".csv"
 ActiveWorkbook.Close
End Sub

Code:

Sub SaveAsCSVviaVBAxlcsv()
 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "csv Text file Chaos\" & "CSV (Comma delimited)A1B1A2" & ".csv", FileFormat:=xlCSV
 ActiveWorkbook.Close
End Sub

Ref
https://excelfox.com/forum/showthrea...mediate-Window
https://excelfox.com/forum/showthrea...ed-Text-String
https://excel.tips.net/T002519_Comma...C_and_Mac.html
https://excelribbon.tips.net/T010280...for_PC_and_Mac
https://sites.google.com/a/madrocket...svs?authuser=0
https://excelribbon.tips.net/T009508...ariations.html

Eileen’sLoungeTextFiles
http://www.eileenslounge.com/viewtop...274367#p274367

https://excel.tips.net/T003232_Speci...n_a_Macro.html -- printer line save ……. This works this way by design in VBA. The Excel implementation of the export routines for VBA always use whatever the Windows regional settings are to determine how items in a CSV should be separated. Specifically, the routine looks at the List Separator field for the delimiter. This means that you can, if desired, change the delimiter to a semicolon by changing the List Separator setting in your regional settings configuration.
If you don't want to change the regional settings, then you can instead write your own macro that will output the file in any way you desire. Consider, for a moment, the following macro, which will output the file:

https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg.9k3ShckGnhv9k89Lsaig oO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg.9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg

**DocAElstein** · 06-16-2020, 11:14 AM

Strange saving of “csv” files in Excel
Lets clarify and look in more detail at the strange saving of the files. In addition when we do it manually, we will run a macro recording
We will concentrate on the CSV (______)A1B1A2.csv

Comma delimited
Initially, if you are starting from Excel with more than one worksheet showing, then an initial pop up warns you of this:
PopUpWarningOfMultipleSheets.JPG : https://imgur.com/QLscU1a

This initial pop up will not be shown if you have just one sheet showing at the Save stage

Assuming we have said OK at the above pop up, or have not had this pop up, we examine now the set of pop ups that occur always..
In both cases answering with Yes
On the SaveAs ( or Save ) I get this prompt
SaveAs or Save CSV (Comma delimited).JPG : : https://imgur.com/SQPHjWI
WarningOnManualSaveOrSaveAScsv(Comma delimited).JPG : https://imgur.com/QiH8phI

and these 2 prompts on the Close, ( the second of which is identical to that from the Save or SaveAs )
this first being strange since I already just Saved the file
AskToSaveTheChangesAfterSave CSV (Comma delimited).JPG : : https://imgur.com/8ih47Ty
FirstWarningOnManualClose CSV (Comma delimited).jpg : https://imgur.com/dAxojzW

The second is identical to that on the save : https://imgur.com/SQPHjWI
SecondWarningOnManualClose CSV (Comma delimited).JPG : https://imgur.com/TY9eBqq

The files as seen in test editor, or from my function, after is like
CellA1;CellA2
CellA3;
The given macro coding from the macro recorder.

Code:

Sub CSVTests()
ActiveWorkbook.SaveAs Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\csv Text file Chaos\CSV (Comma delimited)A1B1A2.csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save: ActiveWindow.Close
End Sub

If I answer No for the attempt at Save or SaveAs, then I will get the option to save as an Excel file
Answer No on SaveAs or Save CSV (Comma delimited).JPG : https://imgur.com/uXWo8vz

If I answer Yes for the attempt at Save or SaveAs, but then on Close , answer No to the first prompt, then I get that first prompt only: I don’t get the second prompt. But,
_there is a no difference in the macro from the macro recorder
and
_there is no difference in the file seen in a text editor after.

Should I now open manually the file, I find that the file has the values put, one in each cell, to reproduce the file such that it looks exactly as I created it originally
If I run the macro obtained from the macro recorder, then on the SaveAs or Save , I don’t get any prompt
On Close I get the first prompt only
DoYouWantToSaveChangesOnCloseDespiteAlreadySavedCS V with maco.JPG : https://imgur.com/BihBGOH
If I answer Yes or No , the file is saved without the second prompt as was the manual case.
Strangely, my file as seen in a text editor is different to that obtained whilst manually doing the recording: It has now commas as the separator/delimiter
cellA1,cellB1
cellA2,
Should I open this now manually, I will find that my row data is spit as before, but , I loose the columns, - that is to say the entire row information, including the , separator appear in the first column. So what is happening is that the , separator is not being recognised as a separator, and instead is being taken as pure text: My file is being seen as a text file having just rows of text, with no separator: In other words it could be regarded as a text file intended to hold the text values to be inserted into column A of an Excel File-

_Row\^Col	A	B
1	CellA1,CellA2
2	CellA3,

**DocAElstein** · 06-27-2020, 02:57 PM

This post repeats the processes and discussions of last post for saving as CSV Macintosh and CSV MS-DOS. ( We will assume we only have one worksheet showing at the start , so will not have the initial pop up warning ( https://imgur.com/QLscU1a )

Macintosh
We record a macro whilst doing Save, SaveAs , Close.
We find that we get the following warnings, answering always Yes

Save or SaveAs
WarnugOnSaveOrSaveAsMacintoshCSV.JPG : https://imgur.com/jCJ5XjK

Close
WarningOnCloseForSavingChangesDespiteAlreadySavedM acintoshCSV.jpg : https://imgur.com/sShcoNq

SecondWarningOnClose SameAsWarningOnSaveOrSaveAsMacintoshCSV.JPG : https://imgur.com/Q20iknI

Here is the macro obtained from the recorder.

Code:

Sub SaveSaveAsCloseMacintoshCSV() '  https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=13741&viewfull=1#post13741
' Save manually
 ActiveWorkbook.Save
 ' SaveAs manually
 ActiveWorkbook.SaveAs Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\csv Text file Chaos\CSV (Macintosh)A1B1A2.csv", FileFormat:=xlCSVMac, CreateBackup:=False
' Warnings same for both: https://imgur.com/jCJ5XjK

' Close manually
 ActiveWorkbook.Save: ActiveWorkbook.Close
' two warnings obtained : https://imgur.com/sShcoNq , https://imgur.com/Q20iknI
End Sub

This is what it the produced text file, like in a text editor
answering always Yes
cellA1;cellB1cellA2;

Note: if I copy and paste that into Word, then I get
cellA1;cellB1
callA2;

Analysing what is there I see the typical results expected for a Macintosh csv file
"cellA1" & ";" & "cellB1" & vbCr & "callA2" & ";" & vbCr & vbLf

If I say No on the Close, then I get only that warning: The file closes without the second warning. But the results are the same

If I run the recorded macro, ( on a newly made file ) , then there is no warning pop up on Save or SaveAs. I just get the single warning asking me if I want to save the changes on Close ( despite already with the preceding coding having Saved and SaveAsd and Saved before the Close
MacroRunWarningOnCloseForSavingChangesDespiteAlrea dySavedMacintoshCSV.JPG : https://imgur.com/KYp1AYw
The resulting text file has commas , for the separator
CellA1,CellB1CellA2,
I get the same results if I answer Yes or No to the warning

CSV (MS-DOS)
We record a macro on Save, SaveAs , Close. We get the following warnings, answering always Yes

Save or SaveAs
Manual SaveAs or Save CSV (MS-DOS) warning.JPG : https://imgur.com/vkikEUS

Close
Manual Close CSV (MS-DOS) first warning.JPG : https://imgur.com/uvh64E6

Manual Close CSV (MS-DOS) second warning.JPG : https://imgur.com/sCPSmFX

This macro is obtained

Code:

Sub ManualSaveSaveAsCloseCSV_MS_DOS_()
 ' Manual Save
 ActiveWorkbook.Save
 ' Manual SaveAs
 ActiveWorkbook.SaveAs Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\csv Text file Chaos\CSV (MS-DOS)A1B1A2.csv", FileFormat:=xlCSVMSDOS, CreateBackup:=False
 ' warning same for both https://imgur.com/vkikEUS
 
 ' Close manually
 ActiveWorkbook.Save: ActiveWindow.Close
 ' two warnings obtained https://imgur.com/uvh64E6 , https://imgur.com/sCPSmFX
End Sub

If I say No on the Close, then I get only that warning: The file closes without the second warning. But the results are the same.

The resulting text file looks like this
cellA1;cellB1
callA2;

If I run the macro produced from the macro recording, then I only get the first warning on at the Close
MacroRunWarningOnCloseForSavingChangesDespiteAlrea dySavedCSVMSDOS.JPG : https://imgur.com/aCzwGqa

**DocAElstein** · 07-17-2020, 01:23 AM

test

PK ! U6»+w ( Ø[Content_Types].xml ¢Ô(* ÌTËNÃ0¼#ñ‘¯(q[B¨i<ŽP ø co«ŽmyÝÒþ=›¤* Ñ¨¥=pI%;3žÌp¼¬L²€€ÚÙœõ³KÀJ§´ æìíõ1½a Fa•0ÎBÎV€l<:?¾®<`BÓsVÆèo9GYB%0s,½)\¨D¤Ç0å^È™˜ôz×\:Á Æ4Öl4|&A+H&"Ä'Q_
Úk?#<–Üµƒ5wÎ„÷FKI9_Xõƒ5uE¡%('çqe
ØEÂwb\À£©Ð
K€X™¬Ý0ßC!æ&&Kr*5=€ÁÃŽ¶63£ÉæøXjÝÞu{òáÂìÝ¹Ù ©]©ÝÉ*¡íF÷ŽÔü•È›ÛÕ‰…lñ»tPŠ&Áyä”¹£ù¡^½•z‚„5lw·§ƒ£5|è^Ð� �ýn½‹Ë¢ãøvø›XŠ ê%jË“×ÆWì½²)]€Ã²é’zú—Dò¦çGŸ ÿÿ PK ! µU0#õ L Î_rels/.rels ¢Ê(*

Code:

 PK          ! U6»+w   (     Ø [Content_Types].xml ¢Ô (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   ÌTËNÃ0 ¼#ñ ‘¯(q[ B¨i <ŽP	ø co «ŽmyÝÒþ=›¤* Ñ¨¥=pI %;3žÌp¼¬L²€€ÚÙœõ³ KÀJ§´æìíõ1½a	Fa•0ÎBÎV€l<:? ¾®<`BÓ sVÆèo9GYB%0s ,½)\¨D¤Ç0å^È™˜ -ôz×\: ÁÆ4Ö l4|& A+H&"Ä'Q  _  	 Úk?#<–Üµƒ5wÎ„÷FK I9_Xõƒ5uE¡%('ç qe ØEÂw b\ À£©Ð 
K€X™¬ Ý0ßC!æ&& Kr 5=€ÁÃŽ¶63£ÉæøXj  
ÝÞu{òáÂìÝ¹Ù©]©ÝÉ*¡íF÷Ž Ôü•È›ÛÕ‰…lñ»tPŠ&Áyä”¹£ù¡^½ •z‚„ 5lw•§ƒ£5|è^ Ð ¹ýn½‹Ë¢ãøvø›¬XŠ ê% jË“×ÆWì½²)]€Ã ²é’zú—Dò¦çGŸ   ÿÿ  PK          ! µU0#õ   L   
 Î _rels/.rels ¢Ê (

**DocAElstein** · 07-20-2020, 03:19 PM

Post for later use

**DocAElstein** · 11-24-2020, 01:45 PM

Post for later use to keep order, and to get URL now for an index page in Blog post

**DocAElstein** · 11-24-2020, 01:45 PM

Post for later use to keep order, and to get URL now for an index page in Blog post -

**DocAElstein** · 11-24-2020, 01:46 PM

Post for later use to keep order, and to get URL now for an index page in Blog post --

**DocAElstein** · 11-24-2020, 01:46 PM

Post for later use to keep order, and to get URL now for an index page in Blog post ...

**DocAElstein** · 11-24-2020, 01:46 PM

Post for later use to keep order, and to get URL now for an index page in Blog post ....

Thread: Notes tests, text files, manipulation of text files in Excel and with Excel VBA CSV stuff

Thread Tools

Search Thread

Display

Notes tests, text files, manipulation of text files in Excel and with Excel VBA CSV stuff

Strange saving of "csv” files

Similar Threads

Notes tests, string, manipulation of text files and string manipulations

Vba - Check String/Text From Multiple Workbook Without Opening All Files

Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos

Notes tests. Excel VBA Folder File Search

Collate Data from csv files to excel sheet

Bookmarks

Bookmarks

Posting Permissions