How to make the right CSV for database import?

If you are working in programming or database related industry, the bulk uploading from CSV format is always the best thing. Possible reasons? it is a simple comma separated data, easy to read even, Can be managed by any spreadsheet writer like Microsoft Excel or Open Office/LibreOffice Calc, can be made easily via spreadsheets data available.

Still, there are some things that need to be taken care of while making a CSV file. otherwise, you may get the undesirable results like garbage data uploaded or even sometimes less or no data being uploaded.

So, here are some things to be avoided and followed to get the right CSV file made:

1. Avoid Microsoft Excel: Seriously! I am not a Bill Gates hater, but I hate Microsoft products because the way it takes the user data so casually. If you create the CSV file from Excel, I bet, you will get a problem. Reason? Even I can't understand! Excel will change the date formats in it's own way. Will add apostrophes (') if you forcefully tried to change the dates. Also, if any of your data has just the value of hyphen (-) or plus sign (+), it will do the same for that too. Technically, Excel adds these values for the sake of it's own rendering. But, dear Excel, If the user needs the file to get opened in Excel itself, why in the world would he make CSV? You already have xls and xlsx formats for yourselves! Please respect the user's requirements and don't make the him dependent just on Microsoft Products.
2. Use Open Source: Well, now if we can't use Microsoft, then what to use? I would count on OpenOffice/LibreOffice Calc. Reasons: They are free! Secondly, they know what the user needs. There are several other tools available too. I even read in a blog somewhere that to convert the Excel file to CSV properly, first upload it to Google Drive, then download it as CSV. But, if you are so free, have so much of time to first save Excel, then upload, then download, then use; then please go ahead! I can't take so much pain and waste time. The interface of Calc is almost same as Office Excel 2003 versions. So, not so hard if you have used those old softwares.
3. Comma Separated/Tab Separated.: Keep in mind! This is a common mistake when the csv is made for the first time. CSV files are of two types in general. Use the type what is required by the system.
1. Comma Separated: the values are separated by using comma (,). The text values are enclosed in double quotes ("This is my text, to save the format"). Double quotes ensures the commas used in text values, if any, as a part of the value and not as a separator. This is the most commonly used type.
2. Tab Separated: The values are separated by using a tab space. In this type of file, the text values need not to be enclosed in double quotes.
4. Character Encoding: Again the important point. If you are using just normal english text then this doesn't matter much for what encoding are you using when saving a file. However, it is a good practice if you know the the character encoding your bulk upload system supports and the encoding you are giving in your file. If you are using some unicode or special characters, then, it is a MUST-TO-DO thing! Mostly the import supports UTF-8 encoding, however, it's better to consult the technology experts for that. The character encoding can be set while saving the file even via simple softwares like notepad.

Be safe while making your files, and always double check everything before actually importing the data. Because, it is not just a single query, It is a bunch! So, if any mishap occurs, you have got to clean a lot of junk!!