Multiple files generated by script, only last one readable

Question

I had a rather strange problem when generating text files by linux shell scripts. The situation is this:

On my Synology Disk Station I am executing an sh-script. It accesses the local mySQL database using a read-only SQL-user. There are multiple calls (one line per call) and each one writes the received output to a .csv file in different locations.

The .sh script looks like this:

/some/path/create_lists.sh

mysql --arguments < /path/to/script1.sql > /path/to/outfile1.csv
mysql --arguments < /path/to/script2.sql > /path/to/outfile2.csv
mysql --arguments < /path/to/script3.sql > /path/to/outfile3.csv
mysql --arguments < /path/to/script4.sql > /path/to/outfile4.csv

Using my Windows PC I want to access these files.

In principle, this is already working fine, but somehow only the last created .csv file is directly readable by MS Excel. In the example above, outfile4.csv would be the only readable file. When swapping line 3 and 4, outfile3.csv would be readable. For outfile4.csv a new file with the exact same name would be created, that cannot be opened by MS Excel. Notepad++ is able to open it, though.

It would look like this in Windows Explorer:

\\myNAS\path\to

outfile4.csv    (working)
outfile4.csv    (not working)

How can there be two files with the same name? And one that is working while the other one doesn't?

Answer 1

While writing this question, I solved it by myself, but I still want to post it for others to see.

Here is the solution: I wrote the scripts on my Windows PC using Notepad++. I saved them in a dedicated scripts folder on my NAS. Running them by the NAS seemingly worked, but there was one little detail that caused the problem. Carriage returns.

While Windows uses a carriage return (\r) and a linefeed (\n) resulting in \r\n for a new line, unix based system only use a linefeed (\n)

By writing the script on a windows machine, I basically wrote:

mysql --arguments < /path/to/script1.sql > /path/to/outfile1.csv\r\n
mysql --arguments < /path/to/script2.sql > /path/to/outfile2.csv\r\n
mysql --arguments < /path/to/script3.sql > /path/to/outfile3.csv\r\n
mysql --arguments < /path/to/script4.sql > /path/to/outfile4.csv

Reading the file on linux resulted in:

mysql --arguments < /path/to/script1.sql > /path/to/outfile1.csv?\n
mysql --arguments < /path/to/script2.sql > /path/to/outfile2.csv?\n
mysql --arguments < /path/to/script3.sql > /path/to/outfile3.csv?\n
mysql --arguments < /path/to/script4.sql > /path/to/outfile4.csv

I found this out by listing the contents of the containing folder via SSH which gave me:

ls -alh /path/to

... outfile4.csv
... outfile4.csv?

And there we have it! This is the reason why the last file is working and the others are not. This also the reason why there can be two files with the "same" name. Windows simply doesn't display the questionmark which causes a lot of confusion.

So if anyone ever has this problem, he might stumble upon this thread and save some of his time. I guess this is common knowledge for most people but I'm still a beginner with Linux so I had to learn it the hard way :)