Avoid last 3 digits converting to 0 in Excel

Programming
,
1

I have a shell script that connects to a database and extracts data into a .CSV file. When I open the file in Excel, it appends zeros to the last three digits of a column, because the column has 18 digits, but Excel can support only up to 15 for numbers. Is there a way I can modify my logic in the shell script so that it doesn’t show the last three digits as zeros when opened in Excel?

# Shell Script
# Connect to db and extract data into .CSV file

# ...

Thanks in advance.

2

Yes, you can modify your shell script to format the column as text so that Excel will not append zeros to the last three digits. Here’s an example of how you can modify your script:

# Shell Script
# Connect to db and extract data into .CSV file

# Set the delimiter to ',' to create a CSV file
DELIMITER=','

# Query the database and format the output as CSV
QUERY="SELECT column1, column2, column3 FROM table1"

# Use the sed command to format the output as text
RESULT=$(echo "$QUERY" | mysql --user=username --password=password --database=database | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g')

# Save the result to a CSV file
echo "$RESULT" > output.csv

In the above script, we are using the sed command to format the output of the query as text. This means that when you open the output file in Excel, it will not append zeros to the last three digits.